Thread: 002_types.pl fails on some timezones on windows
Hi,
CI showed me a failure in 002_types.pl on windows. I only just now noticed
that because the subscription tests aren't run by any of the vcregress.pl
steps :(
It turns out to be dependant on the current timezone. I have just about zero
understanding how timezones work on windows, so I can't really interpret why
that causes a problem on windows, but apparently not on linux.
The CI instance not unreasonably runs with the timezone set to GMT. With that
the tests fail. If I set it to PST, they work. For the detailed (way too long)
output see [1]. The relevant excerpt:
tzutil /s "Pacific Standard Time"
...
timeout -k60s 30m perl src/tools/msvc/vcregress.pl taptest .\src\test\subscription\ || true
t/002_types.pl ..................... ok
..
tzutil /s "Greenwich Standard Time"
timeout -k60s 30m perl src/tools/msvc/vcregress.pl taptest .\src\test\subscription\ || true
..
# Failed test 'check replicated inserts on subscriber'
# at t/002_types.pl line 278.
# got: '1|{1,2,3}
...
# 5|[5,51)
# 1|["2014-08-04 00:00:00+02",infinity)|{"[1,3)","[10,21)"}
# 2|["2014-08-02 01:00:00+02","2014-08-04 00:00:00+02")|{"[2,4)","[20,31)"}
# 3|["2014-08-01 01:00:00+02","2014-08-04 00:00:00+02")|{"[3,5)"}
# 4|["2014-07-31 01:00:00+02","2014-08-04 00:00:00+02")|{"[4,6)",NULL,"[40,51)"}
...
# expected: '1|{1,2,3}
...
# 1|["2014-08-04 00:00:00+02",infinity)|{"[1,3)","[10,21)"}
# 2|["2014-08-02 00:00:00+02","2014-08-04 00:00:00+02")|{"[2,4)","[20,31)"}
# 3|["2014-08-01 00:00:00+02","2014-08-04 00:00:00+02")|{"[3,5)"}
# 4|["2014-07-31 00:00:00+02","2014-08-04 00:00:00+02")|{"[4,6)",NULL,"[40,51)"}
...
Greetings,
Andres Freund
[1] https://api.cirrus-ci.com/v1/task/5800120848482304/logs/check_tz_sub.log
Andres Freund <andres@anarazel.de> writes:
> It turns out to be dependant on the current timezone. I have just about zero
> understanding how timezones work on windows, so I can't really interpret why
> that causes a problem on windows, but apparently not on linux.
Weird. Unless you're using --with-system-tzdata, I wouldn't expect that
code to work any differently on Windows.
regards, tom lane
On 9/30/21 2:36 PM, Andres Freund wrote: > Hi, > > CI showed me a failure in 002_types.pl on windows. I only just now noticed > that because the subscription tests aren't run by any of the vcregress.pl > steps :( We have windows buildfarm animals running the subscription tests, e.g. <https://buildfarm.postgresql.org/cgi-bin/show_stage_log.pl?nm=drongo&dt=2021-09-29%2019%3A08%3A23&stg=subscription-check> and they do it by calling vcregress.pl. cheers andrew -- Andrew Dunstan EDB: https://www.enterprisedb.com
Andrew Dunstan <andrew@dunslane.net> writes: > On 9/30/21 2:36 PM, Andres Freund wrote: >> CI showed me a failure in 002_types.pl on windows. I only just now noticed >> that because the subscription tests aren't run by any of the vcregress.pl >> steps :( > We have windows buildfarm animals running the subscription tests, e.g. > <https://buildfarm.postgresql.org/cgi-bin/show_stage_log.pl?nm=drongo&dt=2021-09-29%2019%3A08%3A23&stg=subscription-check> > and they do it by calling vcregress.pl. But are they running with the prevailing zone set to "Greenwich Standard Time"? I dug around to see exactly how we handle that, and was somewhat gobsmacked to find this mapping in findtimezone.c: /* (UTC+00:00) Monrovia, Reykjavik */ "Greenwich Standard Time", "Greenwich Daylight Time", "Africa/Casablanca" According to current tzdb, # Zone NAME STDOFF RULES FORMAT [UNTIL] Zone Africa/Casablanca -0:30:20 - LMT 1913 Oct 26 0:00 Morocco +00/+01 1984 Mar 16 1:00 - +01 1986 0:00 Morocco +00/+01 2018 Oct 28 3:00 1:00 Morocco +01/+00 Morocco has had weird changes-every-year DST rules since 2008, which'd go a long way towards explaining funny behavior with this zone, even without the "reverse DST" since 2018. And sure enough, 002_types.pl falls over with TZ=Africa/Casablanca on my Linux machine, too. I'm inclined to think we ought to be translating that zone name to Europe/London instead. Or maybe we should translate to straight-up UTC? But the option of "Greenwich Daylight Time" suggests that Windows thinks this means UK civil time, not UTC. I wonder if findtimezone.c has any other surprising Windows mappings. I've never dug through that list particularly. regards, tom lane
I wrote:
> ... sure enough, 002_types.pl
> falls over with TZ=Africa/Casablanca on my Linux machine, too.
Independently of whether Africa/Casablanca is a sane translation of
that Windows zone name, it'd be nice if 002_types.pl weren't so
sensitive to the prevailing zone. I looked into exactly why it's
falling over, and the answer seems to be this bit:
(2, tstzrange('Mon Aug 04 00:00:00 2014 CEST'::timestamptz - interval '2 days', 'Mon Aug 04 00:00:00 2014
CEST'::timestamptz),'{"[2,3]", "[20,30]"}'),
(3, tstzrange('Mon Aug 04 00:00:00 2014 CEST'::timestamptz - interval '3 days', 'Mon Aug 04 00:00:00 2014
CEST'::timestamptz),'{"[3,4]"}'),
(4, tstzrange('Mon Aug 04 00:00:00 2014 CEST'::timestamptz - interval '4 days', 'Mon Aug 04 00:00:00 2014
CEST'::timestamptz),'{"[4,5]", NULL, "[40,50]"}'),
The problem with this is the blithe assumption that "minus N days"
is an immutable computation. It ain't. As bad luck would have it,
these intervals all manage to cross a Moroccan DST boundary
(Ramadan, I assume):
Rule Morocco 2014 only - Jun 28 3:00 0 -
Rule Morocco 2014 only - Aug 2 2:00 1:00 -
Thus, in GMT or most other zones, we get 24-hour-spaced times of day for
these calculations:
regression=# set timezone to 'GMT';
SET
regression=# select n, 'Mon Aug 04 00:00:00 2014 CEST'::timestamptz - n * interval '1 day' from generate_series(0,4) n;
n | ?column?
---+------------------------
0 | 2014-08-03 22:00:00+00
1 | 2014-08-02 22:00:00+00
2 | 2014-08-01 22:00:00+00
3 | 2014-07-31 22:00:00+00
4 | 2014-07-30 22:00:00+00
(5 rows)
but not so much in Morocco:
regression=# set timezone to 'Africa/Casablanca';
SET
regression=# select n, 'Mon Aug 04 00:00:00 2014 CEST'::timestamptz - n * interval '1 day' from generate_series(0,4) n;
n | ?column?
---+------------------------
0 | 2014-08-03 23:00:00+01
1 | 2014-08-02 23:00:00+01
2 | 2014-08-01 23:00:00+00
3 | 2014-07-31 23:00:00+00
4 | 2014-07-30 23:00:00+00
(5 rows)
What I'm inclined to do about that is get rid of the totally-irrelevant-
to-this-test interval subtractions, and just write the desired timestamps
as constants.
regards, tom lane
Hi, On 2021-09-30 15:19:30 -0400, Andrew Dunstan wrote: > On 9/30/21 2:36 PM, Andres Freund wrote: > > Hi, > > > > CI showed me a failure in 002_types.pl on windows. I only just now noticed > > that because the subscription tests aren't run by any of the vcregress.pl > > steps :( > We have windows buildfarm animals running the subscription tests, e.g. > <https://buildfarm.postgresql.org/cgi-bin/show_stage_log.pl?nm=drongo&dt=2021-09-29%2019%3A08%3A23&stg=subscription-check> > and they do it by calling vcregress.pl. The point I was trying to make is that there's no "target" in vcregress.pl for it. You have to know that you need to call src/tools/msvc/vcregress.pl taptest src\test\subscription to run them. Contrasting to recoverycheck or so, which has it's own vcregress.pl target. Greetings, Andres Freund
On Fri, Oct 1, 2021 at 8:38 AM Tom Lane <tgl@sss.pgh.pa.us> wrote: > But the option of "Greenwich Daylight Time" suggests that Windows thinks > this means UK civil time, not UTC. Yes, it's been a while but IIRC Windows in the UK uses confusing terminology here even in user interfaces, so that in summer it appears to be wrong, which is annoying to anyone brought up on Eggert's system. The CLDR windowsZones.xml file shows this.
Andres Freund <andres@anarazel.de> writes:
> It turns out to be dependant on the current timezone. I have just about zero
> understanding how timezones work on windows, so I can't really interpret why
> that causes a problem on windows, but apparently not on linux.
As of 20f8671ef, "TZ=Africa/Casablanca make check-world" passes here,
so your CI should be okay. We still oughta fix the Windows
translation, though.
regards, tom lane
Hi,
On 2021-09-30 16:03:15 -0400, Tom Lane wrote:
> I wrote:
> > ... sure enough, 002_types.pl
> > falls over with TZ=Africa/Casablanca on my Linux machine, too.
>
> Independently of whether Africa/Casablanca is a sane translation of
> that Windows zone name, it'd be nice if 002_types.pl weren't so
> sensitive to the prevailing zone. I looked into exactly why it's
> falling over, and the answer seems to be this bit:
> (2, tstzrange('Mon Aug 04 00:00:00 2014 CEST'::timestamptz - interval '2 days', 'Mon Aug 04 00:00:00 2014
CEST'::timestamptz),'{"[2,3]", "[20,30]"}'),
> (3, tstzrange('Mon Aug 04 00:00:00 2014 CEST'::timestamptz - interval '3 days', 'Mon Aug 04 00:00:00 2014
CEST'::timestamptz),'{"[3,4]"}'),
> (4, tstzrange('Mon Aug 04 00:00:00 2014 CEST'::timestamptz - interval '4 days', 'Mon Aug 04 00:00:00 2014
CEST'::timestamptz),'{"[4,5]", NULL, "[40,50]"}'),
>
> The problem with this is the blithe assumption that "minus N days"
> is an immutable computation. It ain't. As bad luck would have it,
> these intervals all manage to cross a Moroccan DST boundary
> (Ramadan, I assume):
For a minute I was confused, because of course we should still get the same
result on the subscriber as on the publisher. But then I re-re-re-realized
that the comparison data is a constant in the test script...
> What I'm inclined to do about that is get rid of the totally-irrelevant-
> to-this-test interval subtractions, and just write the desired timestamps
> as constants.
Sounds like a plan.
Greetings,
Andres Freund
Hi, On 2021-09-30 16:31:33 -0400, Tom Lane wrote: > Andres Freund <andres@anarazel.de> writes: > > It turns out to be dependant on the current timezone. I have just about zero > > understanding how timezones work on windows, so I can't really interpret why > > that causes a problem on windows, but apparently not on linux. > > As of 20f8671ef, "TZ=Africa/Casablanca make check-world" passes here, > so your CI should be okay. We still oughta fix the Windows > translation, though. Indeed, it just passed (after reverting my timezone workaround): https://cirrus-ci.com/task/5899963000422400?logs=check#L129 It still fails in t/026_overwrite_contrecord.pl though. But that's another thread. Thanks! Andres
Thomas Munro <thomas.munro@gmail.com> writes:
> Yes, it's been a while but IIRC Windows in the UK uses confusing
> terminology here even in user interfaces, so that in summer it appears
> to be wrong, which is annoying to anyone brought up on Eggert's
> system. The CLDR windowsZones.xml file shows this.
Oh, thanks for the pointer to CLDR! I tried re-generating our data
based on theirs, and ended up with the attached draft patch.
My notes summarizing the changes say:
Choose Europe/London for "Greenwich Standard Time"
(CLDR doesn't do this, but all their mappings for it are insane)
Alphabetize a bit better
Zone name changes:
Jerusalem Standard Time -> Israel Standard Time
Numerous Russian zones slightly renamed
Should we preserve the old spellings of the above? It's not clear
how long-obsolete the old spellings are.
Maybe politically sensitive:
Asia/Hong_Kong -> Asia/Shanghai
I think the latter has way better claim on "China Standard Time",
and CLDR agrees.
Resolve Links to underlying real zones:
Asia/Kuwait -> Asia/Riyadh
Asia/Muscat -> Asia/Dubai
Australia/Canberra -> Australia/Sydney
Canada/Atlantic -> America/Halifax
Canada/Newfoundland -> America/St_Johns
Canada/Saskatchewan -> America/Regina
US/Alaska -> America/Anchorage
US/Arizona -> America/Phoenix
US/Central -> America/Chicago
US/Eastern -> America/New_York
US/Hawaii -> Pacific/Honolulu
US/Mountain -> America/Denver
US/Pacific -> America/Los_Angeles
Just plain wrong:
US/Aleutan (misspelling of US/Aleutian, which is a link anyway)
America/Salvador does not exist; tzdb says
# There are too many Salvadors elsewhere, so use America/Bahia instead
# of America/Salvador.
Etc/UTC+12 doesn't exist in tzdb
Indiana (East) is not the regular US/Eastern zone
Asia/Baku -> Asia/Yerevan (Baku is in Azerbaijan, Yerevan is in Armenia)
Asia/Dhaka -> Asia/Almaty (Dhaka has its own zone, and it's in Bangladesh
not Astana)
Europe/Sarajevo is a link to Europe/Belgrade these days, so use Warsaw
Chisinau is in Moldova not Romania
Chetumal is in Quintana Roo, which is represented by Cancun not Mexico City
Haiti has its own zone
America/Araguaina seems to just be a mistake; use Sao_Paulo
America/Buenos_Aires for SA Eastern Standard Time is a mistake
(it has its own zone)
likewise America/Caracas for SA Western Standard Time
Africa/Harare seems to be obsoleted by Africa/Johannesburg
Karachi is in Pakistan, not Tashkent
New Windows zones:
"South Sudan Standard Time" -> Africa/Juba
"West Bank Standard Time" -> Asia/Hebron
(CLDR seem to have this replacing Gaza, but I kept that one too)
"Yukon Standard Time" -> America/Whitehorse
uncomment "W. Central Africa Standard Time" as Africa/Lagos
regards, tom lane
diff --git a/src/bin/initdb/findtimezone.c b/src/bin/initdb/findtimezone.c
index 3c2b8d4e29..5ae5a576c9 100644
--- a/src/bin/initdb/findtimezone.c
+++ b/src/bin/initdb/findtimezone.c
@@ -750,12 +750,12 @@ static const struct
{
/* (UTC-09:00) Alaska */
"Alaskan Standard Time", "Alaskan Daylight Time",
- "US/Alaska"
+ "America/Anchorage"
},
{
/* (UTC-10:00) Aleutian Islands */
"Aleutian Standard Time", "Aleutian Daylight Time",
- "US/Aleutan"
+ "America/Adak"
},
{
/* (UTC+07:00) Barnaul, Gorno-Altaysk */
@@ -765,12 +765,12 @@ static const struct
{
/* (UTC+03:00) Kuwait, Riyadh */
"Arab Standard Time", "Arab Daylight Time",
- "Asia/Kuwait"
+ "Asia/Riyadh"
},
{
/* (UTC+04:00) Abu Dhabi, Muscat */
"Arabian Standard Time", "Arabian Daylight Time",
- "Asia/Muscat"
+ "Asia/Dubai"
},
{
/* (UTC+03:00) Baghdad */
@@ -795,7 +795,7 @@ static const struct
{
/* (UTC-04:00) Atlantic Time (Canada) */
"Atlantic Standard Time", "Atlantic Daylight Time",
- "Canada/Atlantic"
+ "America/Halifax"
},
{
/* (UTC+09:30) Darwin */
@@ -810,7 +810,7 @@ static const struct
{
/* (UTC+10:00) Canberra, Melbourne, Sydney */
"AUS Eastern Standard Time", "AUS Eastern Daylight Time",
- "Australia/Canberra"
+ "Australia/Sydney"
},
{
/* (UTC+04:00) Baku */
@@ -825,37 +825,32 @@ static const struct
{
/* (UTC-03:00) Salvador */
"Bahia Standard Time", "Bahia Daylight Time",
- "America/Salvador"
+ "America/Bahia"
},
{
/* (UTC+06:00) Dhaka */
"Bangladesh Standard Time", "Bangladesh Daylight Time",
"Asia/Dhaka"
},
- {
- /* (UTC+11:00) Bougainville Island */
- "Bougainville Standard Time", "Bougainville Daylight Time",
- "Pacific/Bougainville"
- },
{
/* (UTC+03:00) Minsk */
"Belarus Standard Time", "Belarus Daylight Time",
"Europe/Minsk"
},
+ {
+ /* (UTC+11:00) Bougainville Island */
+ "Bougainville Standard Time", "Bougainville Daylight Time",
+ "Pacific/Bougainville"
+ },
{
/* (UTC-01:00) Cabo Verde Is. */
"Cabo Verde Standard Time", "Cabo Verde Daylight Time",
"Atlantic/Cape_Verde"
},
- {
- /* (UTC+12:45) Chatham Islands */
- "Chatham Islands Standard Time", "Chatham Islands Daylight Time",
- "Pacific/Chatham"
- },
{
/* (UTC-06:00) Saskatchewan */
"Canada Central Standard Time", "Canada Central Daylight Time",
- "Canada/Saskatchewan"
+ "America/Regina"
},
{
/* (UTC-01:00) Cape Verde Is. */
@@ -865,7 +860,7 @@ static const struct
{
/* (UTC+04:00) Yerevan */
"Caucasus Standard Time", "Caucasus Daylight Time",
- "Asia/Baku"
+ "Asia/Yerevan"
},
{
/* (UTC+09:30) Adelaide */
@@ -881,7 +876,7 @@ static const struct
{
/* (UTC+06:00) Astana */
"Central Asia Standard Time", "Central Asia Daylight Time",
- "Asia/Dhaka"
+ "Asia/Almaty"
},
{
/* (UTC-04:00) Cuiaba */
@@ -896,7 +891,7 @@ static const struct
{
/* (UTC+01:00) Sarajevo, Skopje, Warsaw, Zagreb */
"Central European Standard Time", "Central European Daylight Time",
- "Europe/Sarajevo"
+ "Europe/Warsaw"
},
{
/* (UTC+11:00) Solomon Is., New Caledonia */
@@ -906,17 +901,27 @@ static const struct
{
/* (UTC-06:00) Central Time (US & Canada) */
"Central Standard Time", "Central Daylight Time",
- "US/Central"
+ "America/Chicago"
},
{
/* (UTC-06:00) Guadalajara, Mexico City, Monterrey */
"Central Standard Time (Mexico)", "Central Daylight Time (Mexico)",
"America/Mexico_City"
},
+ {
+ /* (UTC+12:45) Chatham Islands */
+ "Chatham Islands Standard Time", "Chatham Islands Daylight Time",
+ "Pacific/Chatham"
+ },
{
/* (UTC+08:00) Beijing, Chongqing, Hong Kong, Urumqi */
"China Standard Time", "China Daylight Time",
- "Asia/Hong_Kong"
+ "Asia/Shanghai"
+ },
+ {
+ /* (UTC) Coordinated Universal Time */
+ "Coordinated Universal Time", "Coordinated Universal Time",
+ "UTC"
},
{
/* (UTC-05:00) Havana */
@@ -926,7 +931,7 @@ static const struct
{
/* (UTC-12:00) International Date Line West */
"Dateline Standard Time", "Dateline Daylight Time",
- "Etc/UTC+12"
+ "Etc/GMT+12"
},
{
/* (UTC+03:00) Nairobi */
@@ -941,27 +946,27 @@ static const struct
{
/* (UTC+02:00) Chisinau */
"E. Europe Standard Time", "E. Europe Daylight Time",
- "Europe/Bucharest"
+ "Europe/Chisinau"
},
{
/* (UTC-03:00) Brasilia */
"E. South America Standard Time", "E. South America Daylight Time",
- "America/Araguaina"
+ "America/Sao_Paulo"
+ },
+ {
+ /* (UTC-06:00) Easter Island */
+ "Easter Island Standard Time", "Easter Island Daylight Time",
+ "Pacific/Easter"
},
{
/* (UTC-05:00) Eastern Time (US & Canada) */
"Eastern Standard Time", "Eastern Daylight Time",
- "US/Eastern"
+ "America/New_York"
},
{
/* (UTC-05:00) Chetumal */
"Eastern Standard Time (Mexico)", "Eastern Daylight Time (Mexico)",
- "America/Mexico_City"
- },
- {
- /* (UTC-06:00) Easter Island */
- "Easter Island Standard Time", "Easter Island Daylight Time",
- "Pacific/Easter"
+ "America/Cancun"
},
{
/* (UTC+02:00) Cairo */
@@ -970,7 +975,7 @@ static const struct
},
{
/* (UTC+05:00) Ekaterinburg */
- "Ekaterinburg Standard Time (RTZ 4)", "Ekaterinburg Daylight Time",
+ "Ekaterinburg Standard Time", "Ekaterinburg Daylight Time",
"Asia/Yekaterinburg"
},
{
@@ -1001,7 +1006,7 @@ static const struct
{
/* (UTC+00:00) Monrovia, Reykjavik */
"Greenwich Standard Time", "Greenwich Daylight Time",
- "Africa/Casablanca"
+ "Europe/London"
},
{
/* (UTC+02:00) Athens, Bucharest */
@@ -1011,12 +1016,12 @@ static const struct
{
/* (UTC-05:00) Haiti */
"Haiti Standard Time", "Haiti Daylight Time",
- "US/Eastern"
+ "America/Port-au-Prince"
},
{
/* (UTC-10:00) Hawaii */
"Hawaiian Standard Time", "Hawaiian Daylight Time",
- "US/Hawaii"
+ "Pacific/Honolulu"
},
{
/* (UTC+05:30) Chennai, Kolkata, Mumbai, New Delhi */
@@ -1030,7 +1035,7 @@ static const struct
},
{
/* (UTC+02:00) Jerusalem */
- "Jerusalem Standard Time", "Jerusalem Daylight Time",
+ "Israel Standard Time", "Israel Daylight Time",
"Asia/Jerusalem"
},
{
@@ -1038,6 +1043,11 @@ static const struct
"Jordan Standard Time", "Jordan Daylight Time",
"Asia/Amman"
},
+ {
+ /* (UTC+02:00) Kaliningrad */
+ "Kaliningrad Standard Time", "Kaliningrad Daylight Time",
+ "Europe/Kaliningrad"
+ },
{
/* (UTC+12:00) Petropavlovsk-Kamchatsky - Old */
"Kamchatka Standard Time", "Kamchatka Daylight Time",
@@ -1073,6 +1083,11 @@ static const struct
"Magallanes Standard Time", "Magallanes Daylight Time",
"America/Punta_Arenas"
},
+ {
+ /* (UTC+08:00) Kuala Lumpur, Singapore */
+ "Malay Peninsula Standard Time", "Malay Peninsula Daylight Time",
+ "Asia/Kuala_Lumpur"
+ },
{
/* (UTC-09:30) Marquesas Islands */
"Marquesas Standard Time", "Marquesas Daylight Time",
@@ -1116,7 +1131,7 @@ static const struct
{
/* (UTC-07:00) Mountain Time (US & Canada) */
"Mountain Standard Time", "Mountain Daylight Time",
- "US/Mountain"
+ "America/Denver"
},
{
/* (UTC-07:00) Chihuahua, La Paz, Mazatlan */
@@ -1129,7 +1144,7 @@ static const struct
"Asia/Rangoon"
},
{
- /* (UTC+06:00) Novosibirsk (RTZ 5) */
+ /* (UTC+07:00) Novosibirsk */
"N. Central Asia Standard Time", "N. Central Asia Daylight Time",
"Asia/Novosibirsk"
},
@@ -1151,7 +1166,7 @@ static const struct
{
/* (UTC-03:30) Newfoundland */
"Newfoundland Standard Time", "Newfoundland Daylight Time",
- "Canada/Newfoundland"
+ "America/St_Johns"
},
{
/* (UTC+11:00) Norfolk Island */
@@ -1159,7 +1174,7 @@ static const struct
"Pacific/Norfolk"
},
{
- /* (UTC+08:00) Irkutsk, Ulaan Bataar */
+ /* (UTC+08:00) Irkutsk */
"North Asia East Standard Time", "North Asia East Daylight Time",
"Asia/Irkutsk"
},
@@ -1191,7 +1206,7 @@ static const struct
{
/* (UTC-08:00) Pacific Time (US & Canada) */
"Pacific Standard Time", "Pacific Daylight Time",
- "US/Pacific"
+ "America/Los_Angeles"
},
{
/* (UTC-08:00) Baja California */
@@ -1219,69 +1234,29 @@ static const struct
"Europe/Brussels"
},
{
- /* (UTC+02:00) Kaliningrad */
- "Russia TZ 1 Standard Time", "Russia TZ 1 Daylight Time",
- "Europe/Kaliningrad"
+ /* (UTC+11:00) Chokurdakh */
+ "Russia Time Zone 10", "Russia Time Zone 10",
+ "Asia/Srednekolymsk"
},
{
- /* (UTC+03:00) Moscow, St. Petersburg */
- "Russia TZ 2 Standard Time", "Russia TZ 2 Daylight Time",
- "Europe/Moscow"
+ /* (UTC+12:00) Anadyr, Petropavlovsk-Kamchatsky */
+ "Russia Time Zone 11", "Russia Time Zone 11",
+ "Asia/Kamchatka"
},
{
/* (UTC+04:00) Izhevsk, Samara */
- "Russia TZ 3 Standard Time", "Russia TZ 3 Daylight Time",
+ "Russia Time Zone 3", "Russia Time Zone 3",
"Europe/Samara"
},
{
- /* (UTC+05:00) Ekaterinburg */
- "Russia TZ 4 Standard Time", "Russia TZ 4 Daylight Time",
- "Asia/Yekaterinburg"
- },
- {
- /* (UTC+06:00) Novosibirsk (RTZ 5) */
- "Russia TZ 5 Standard Time", "Russia TZ 5 Daylight Time",
- "Asia/Novosibirsk"
- },
- {
- /* (UTC+07:00) Krasnoyarsk */
- "Russia TZ 6 Standard Time", "Russia TZ 6 Daylight Time",
- "Asia/Krasnoyarsk"
- },
- {
- /* (UTC+08:00) Irkutsk */
- "Russia TZ 7 Standard Time", "Russia TZ 7 Daylight Time",
- "Asia/Irkutsk"
- },
- {
- /* (UTC+09:00) Yakutsk */
- "Russia TZ 8 Standard Time", "Russia TZ 8 Daylight Time",
- "Asia/Yakutsk"
- },
- {
- /* (UTC+10:00) Vladivostok */
- "Russia TZ 9 Standard Time", "Russia TZ 9 Daylight Time",
- "Asia/Vladivostok"
- },
- {
- /* (UTC+11:00) Chokurdakh */
- "Russia TZ 10 Standard Time", "Russia TZ 10 Daylight Time",
- "Asia/Magadan"
- },
- {
- /* (UTC+12:00) Anadyr, Petropavlovsk-Kamchatsky */
- "Russia TZ 11 Standard Time", "Russia TZ 11 Daylight Time",
- "Asia/Anadyr"
- },
- {
- /* (UTC+03:00) Moscow, St. Petersburg, Volgograd */
+ /* (UTC+03:00) Moscow, St. Petersburg */
"Russian Standard Time", "Russian Daylight Time",
"Europe/Moscow"
},
{
/* (UTC-03:00) Cayenne, Fortaleza */
"SA Eastern Standard Time", "SA Eastern Daylight Time",
- "America/Buenos_Aires"
+ "America/Cayenne"
},
{
/* (UTC-05:00) Bogota, Lima, Quito, Rio Branco */
@@ -1291,13 +1266,18 @@ static const struct
{
/* (UTC-04:00) Georgetown, La Paz, Manaus, San Juan */
"SA Western Standard Time", "SA Western Daylight Time",
- "America/Caracas"
+ "America/La_Paz"
},
{
/* (UTC-03:00) Saint Pierre and Miquelon */
"Saint Pierre Standard Time", "Saint Pierre Daylight Time",
"America/Miquelon"
},
+ {
+ /* (UTC+11:00) Sakhalin */
+ "Sakhalin Standard Time", "Sakhalin Daylight Time",
+ "Asia/Sakhalin"
+ },
{
/* (UTC+13:00) Samoa */
"Samoa Standard Time", "Samoa Daylight Time",
@@ -1320,18 +1300,18 @@ static const struct
},
{
/* (UTC+08:00) Kuala Lumpur, Singapore */
- "Malay Peninsula Standard Time", "Malay Peninsula Daylight Time",
- "Asia/Kuala_Lumpur"
- },
- {
- /* (UTC+11:00) Sakhalin */
- "Sakhalin Standard Time", "Sakhalin Daylight Time",
- "Asia/Sakhalin"
+ "Singapore Standard Time", "Singapore Daylight Time",
+ "Asia/Singapore"
},
{
/* (UTC+02:00) Harare, Pretoria */
"South Africa Standard Time", "South Africa Daylight Time",
- "Africa/Harare"
+ "Africa/Johannesburg"
+ },
+ {
+ /* (UTC+02:00) Juba */
+ "South Sudan Standard Time", "South Sudan Daylight Time",
+ "Africa/Juba"
},
{
/* (UTC+05:30) Sri Jayawardenepura */
@@ -1368,16 +1348,16 @@ static const struct
"Tokyo Standard Time", "Tokyo Daylight Time",
"Asia/Tokyo"
},
- {
- /* (UTC+13:00) Nuku'alofa */
- "Tonga Standard Time", "Tonga Daylight Time",
- "Pacific/Tongatapu"
- },
{
/* (UTC+07:00) Tomsk */
"Tomsk Standard Time", "Tomsk Daylight Time",
"Asia/Tomsk"
},
+ {
+ /* (UTC+13:00) Nuku'alofa */
+ "Tonga Standard Time", "Tonga Daylight Time",
+ "Pacific/Tongatapu"
+ },
{
/* (UTC+09:00) Chita */
"Transbaikal Standard Time", "Transbaikal Daylight Time",
@@ -1390,28 +1370,23 @@ static const struct
},
{
/* (UTC-05:00) Turks and Caicos */
- "Turks and Caicos Standard Time", "Turks and Caicos Daylight Time",
+ "Turks And Caicos Standard Time", "Turks And Caicos Daylight Time",
"America/Grand_Turk"
},
{
/* (UTC+08:00) Ulaanbaatar */
"Ulaanbaatar Standard Time", "Ulaanbaatar Daylight Time",
- "Asia/Ulaanbaatar",
+ "Asia/Ulaanbaatar"
},
{
/* (UTC-05:00) Indiana (East) */
"US Eastern Standard Time", "US Eastern Daylight Time",
- "US/Eastern"
+ "America/Indianapolis"
},
{
/* (UTC-07:00) Arizona */
"US Mountain Standard Time", "US Mountain Daylight Time",
- "US/Arizona"
- },
- {
- /* (UTC) Coordinated Universal Time */
- "Coordinated Universal Time", "Coordinated Universal Time",
- "UTC"
+ "America/Phoenix"
},
{
/* (UTC+12:00) Coordinated Universal Time+12 */
@@ -1446,10 +1421,10 @@ static const struct
{
/* (UTC-04:00) Caracas */
"Venezuela Standard Time", "Venezuela Daylight Time",
- "America/Caracas",
+ "America/Caracas"
},
{
- /* (UTC+10:00) Vladivostok (RTZ 9) */
+ /* (UTC+10:00) Vladivostok */
"Vladivostok Standard Time", "Vladivostok Daylight Time",
"Asia/Vladivostok"
},
@@ -1463,14 +1438,11 @@ static const struct
"W. Australia Standard Time", "W. Australia Daylight Time",
"Australia/Perth"
},
-#ifdef NOT_USED
- /* Could not find a match for this one (just a guess). Excluded for now. */
{
/* (UTC+01:00) West Central Africa */
"W. Central Africa Standard Time", "W. Central Africa Daylight Time",
- "WAT"
+ "Africa/Lagos"
},
-#endif
{
/* (UTC+01:00) Amsterdam, Berlin, Bern, Rome, Stockholm, Vienna */
"W. Europe Standard Time", "W. Europe Daylight Time",
@@ -1484,13 +1456,18 @@ static const struct
{
/* (UTC+05:00) Ashgabat, Tashkent */
"West Asia Standard Time", "West Asia Daylight Time",
- "Asia/Karachi"
+ "Asia/Tashkent"
},
{
/* (UTC+02:00) Gaza, Hebron */
"West Bank Gaza Standard Time", "West Bank Gaza Daylight Time",
"Asia/Gaza"
},
+ {
+ /* (UTC+02:00) Gaza, Hebron */
+ "West Bank Standard Time", "West Bank Daylight Time",
+ "Asia/Hebron"
+ },
{
/* (UTC+10:00) Guam, Port Moresby */
"West Pacific Standard Time", "West Pacific Daylight Time",
@@ -1501,6 +1478,11 @@ static const struct
"Yakutsk Standard Time", "Yakutsk Daylight Time",
"Asia/Yakutsk"
},
+ {
+ /* (UTC-07:00) Yukon */
+ "Yukon Standard Time", "Yukon Daylight Time",
+ "America/Whitehorse"
+ },
{
NULL, NULL, NULL
}
Thomas Munro <thomas.munro@gmail.com> writes:
>> Yes, it's been a while but IIRC Windows in the UK uses confusing
>> terminology here even in user interfaces, so that in summer it appears
>> to be wrong, which is annoying to anyone brought up on Eggert's
>> system. The CLDR windowsZones.xml file shows this.
BTW, on closer inspection of CLDR's data, the Windows zone name they
associate with Europe/London is "GMT Standard Time". "Greenwich Standard
Time" is associated with a bunch of places that happen to lie near the
prime meridian, but whose timekeeping likely has nothing to do with UK
civil time:
<!-- (UTC+00:00) Monrovia, Reykjavik -->
<mapZone other="Greenwich Standard Time" territory="001" type="Atlantic/Reykjavik"/>
<mapZone other="Greenwich Standard Time" territory="BF" type="Africa/Ouagadougou"/>
<mapZone other="Greenwich Standard Time" territory="CI" type="Africa/Abidjan"/>
<mapZone other="Greenwich Standard Time" territory="GH" type="Africa/Accra"/>
<mapZone other="Greenwich Standard Time" territory="GL" type="America/Danmarkshavn"/>
<mapZone other="Greenwich Standard Time" territory="GM" type="Africa/Banjul"/>
<mapZone other="Greenwich Standard Time" territory="GN" type="Africa/Conakry"/>
<mapZone other="Greenwich Standard Time" territory="GW" type="Africa/Bissau"/>
<mapZone other="Greenwich Standard Time" territory="IS" type="Atlantic/Reykjavik"/>
<mapZone other="Greenwich Standard Time" territory="LR" type="Africa/Monrovia"/>
<mapZone other="Greenwich Standard Time" territory="ML" type="Africa/Bamako"/>
<mapZone other="Greenwich Standard Time" territory="MR" type="Africa/Nouakchott"/>
<mapZone other="Greenwich Standard Time" territory="SH" type="Atlantic/St_Helena"/>
<mapZone other="Greenwich Standard Time" territory="SL" type="Africa/Freetown"/>
<mapZone other="Greenwich Standard Time" territory="SN" type="Africa/Dakar"/>
<mapZone other="Greenwich Standard Time" territory="TG" type="Africa/Lome"/>
So arguably, the problem that started this thread was Andres' user
error: I doubt he expected "Greenwich Standard Time" to mean any
of these. Still, I think we're better off to map that to London,
because he won't be the only one to make that mistake.
BTW, I find those "territory" annotations in the CLDR data to be
fascinating. If that corresponds to something that we could retrieve
at runtime, it'd allow far better mapping of Windows zones than we
are doing now. I have no interest in working on that myself though.
regards, tom lane
On 9/30/21 3:38 PM, Tom Lane wrote: > Andrew Dunstan <andrew@dunslane.net> writes: >> On 9/30/21 2:36 PM, Andres Freund wrote: >>> CI showed me a failure in 002_types.pl on windows. I only just now noticed >>> that because the subscription tests aren't run by any of the vcregress.pl >>> steps :( >> We have windows buildfarm animals running the subscription tests, e.g. >> <https://buildfarm.postgresql.org/cgi-bin/show_stage_log.pl?nm=drongo&dt=2021-09-29%2019%3A08%3A23&stg=subscription-check> >> and they do it by calling vcregress.pl. > But are they running with the prevailing zone set to "Greenwich Standard > Time"? drongo's timezone is set to plain "UTC". It also offers me "UTC+00:00(Dublin, Edinburgh, Lisbon, London)" and "UTC+00:00(Monrovia, Reykjavik)" cheers andrew -- Andrew Dunstan EDB: https://www.enterprisedb.com
Andrew Dunstan <andrew@dunslane.net> writes:
> On 9/30/21 3:38 PM, Tom Lane wrote:
>> But are they running with the prevailing zone set to "Greenwich Standard
>> Time"?
> drongo's timezone is set to plain "UTC".
> It also offers me "UTC+00:00(Dublin, Edinburgh, Lisbon, London)" and
> "UTC+00:00(Monrovia, Reykjavik)"
Yeah, the last of those is (was) the problematic one.
regards, tom lane
I wrote:
> Oh, thanks for the pointer to CLDR! I tried re-generating our data
> based on theirs, and ended up with the attached draft patch.
Hearing no objections, pushed after another round of review
and a couple more fixes.
For the archives' sake, here are the remaining discrepancies
between our mapping and CLDR's entries for "territory 001",
which I take to be their recommended defaults:
* Our documented decision to map "Central America" to "CST6",
on the grounds that most of Central America doesn't actually
observe DST nowadays.
* Now-documented decision to map "Greenwich Standard Time"
to Europe/London, not Atlantic/Reykjavik as they have it.
* The miscellaneous deltas shown in the attached diff, which in
many cases boil down to "we chose the first name mentioned for the
zone, while CLDR did something else". I felt that our historical
mappings of these cases weren't wrong enough to justify any
political flak I might take for changing them. OTOH, maybe we
should just say "we follow CLDR" and be done with it.
regards, tom lane
--- findtimezone.c 2021-10-02 15:37:17.309929827 -0400
+++ cldr_transformed.c 2021-10-02 14:32:32.359818338 -0400
@@ -888,7 +131,7 @@ static const struct
{
/* (UTC+01:00) Belgrade, Bratislava, Budapest, Ljubljana, Prague */
"Central Europe Standard Time", "Central Europe Daylight Time",
- "Europe/Belgrade"
+ "Europe/Budapest"
},
{
/* (UTC+01:00) Sarajevo, Skopje, Warsaw, Zagreb */
@@ -898,7 +141,7 @@ static const struct
{
/* (UTC+11:00) Solomon Is., New Caledonia */
"Central Pacific Standard Time", "Central Pacific Daylight Time",
- "Pacific/Noumea"
+ "Pacific/Guadalcanal"
},
{
/* (UTC-06:00) Central Time (US & Canada) */
@@ -988,7 +226,7 @@ static const struct
{
/* (UTC+02:00) Helsinki, Kyiv, Riga, Sofia, Tallinn, Vilnius */
"FLE Standard Time", "FLE Daylight Time",
- "Europe/Helsinki"
+ "Europe/Kiev"
},
{
/* (UTC+04:00) Tbilisi */
@@ -1006,7 +244,7 @@ static const struct
{
/* (UTC+02:00) Athens, Bucharest */
"GTB Standard Time", "GTB Daylight Time",
- "Europe/Athens"
+ "Europe/Bucharest"
},
{
/* (UTC-05:00) Haiti */
@@ -1244,7 +441,7 @@ static const struct
{
/* (UTC+01:00) Brussels, Copenhagen, Madrid, Paris */
"Romance Standard Time", "Romance Daylight Time",
- "Europe/Brussels"
+ "Europe/Paris"
},
{
/* (UTC+11:00) Chokurdakh */
@@ -1349,7 +491,7 @@ static const struct
{
/* (UTC+13:00) Samoa */
"Samoa Standard Time", "Samoa Daylight Time",
- "Pacific/Samoa"
+ "Pacific/Apia"
},
{
/* (UTC+00:00) Sao Tome */
@@ -1519,7 +661,7 @@ static const struct
{
/* (UTC+01:00) Amsterdam, Berlin, Bern, Rome, Stockholm, Vienna */
"W. Europe Standard Time", "W. Europe Daylight Time",
- "CET"
+ "Europe/Berlin"
},
{
/* (UTC+07:00) Hovd */
@@ -1533,7 +675,7 @@ static const struct
{
/* (UTC+10:00) Guam, Port Moresby */
"West Pacific Standard Time", "West Pacific Daylight Time",
- "Pacific/Guam"
+ "Pacific/Port_Moresby"
},
{
/* (UTC+09:00) Yakutsk */
On Sun, Oct 3, 2021 at 9:42 AM Tom Lane <tgl@sss.pgh.pa.us> wrote: > * Now-documented decision to map "Greenwich Standard Time" > to Europe/London, not Atlantic/Reykjavik as they have it. Hmm. It's hard to pick a default from that set of merged zones, but the funny thing about this choice is that Europe/London is the one Olson zone that it's sure *not* to be, because then your system would be using that other name, IIUC. > * The miscellaneous deltas shown in the attached diff, which in > many cases boil down to "we chose the first name mentioned for the > zone, while CLDR did something else". I felt that our historical > mappings of these cases weren't wrong enough to justify any > political flak I might take for changing them. OTOH, maybe we > should just say "we follow CLDR" and be done with it. Eyeballing these, three look strange to me in a list of otherwise city-based names: Pacific/Guam (instead of Port Moresby, capital of PNG which apparently shares zone rules with the territory of Guam) and Pacific/Samoa (country name instead of its capital Apia; the city avoids any potential confusion with American Samoa which is on the other side of the date line) and then "CET", an abbreviation. But debating individual points of geography and politics like this seems a bit silly... I wasn't really aware of this Windows->Olson zone name problem lurking in our tree before, but it sounds to me like switching to 100% "we use CLDR, if you think it's wrong, please file a report at cldr.unicode.org" wouldn't be a bad idea at all!
On Sat, Oct 2, 2021 at 1:55 AM Tom Lane <tgl@sss.pgh.pa.us> wrote: > BTW, I find those "territory" annotations in the CLDR data to be > fascinating. If that corresponds to something that we could retrieve > at runtime, it'd allow far better mapping of Windows zones than we > are doing now. I have no interest in working on that myself though. I wonder if it could be derived from the modern standards-based locale name, which we're not currently using as a default locale but probably should[1]. For single-zone countries you might be able to match exactly one zone mapping. [1] https://www.postgresql.org/message-id/flat/CA%2BhUKGJ%3DXThErgAQRoqfCy1bKPxXVuF0%3D2zDbB%2BSxDs59pv7Fw%40mail.gmail.com
Thomas Munro <thomas.munro@gmail.com> writes:
> On Sun, Oct 3, 2021 at 9:42 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> * Now-documented decision to map "Greenwich Standard Time"
>> to Europe/London, not Atlantic/Reykjavik as they have it.
> Hmm. It's hard to pick a default from that set of merged zones, but
> the funny thing about this choice is that Europe/London is the one
> Olson zone that it's sure *not* to be, because then your system would
> be using that other name, IIUC.
Agreed, this choice is definitely formally wrong. However, the example
we started the thread with is that Andres thought "Greenwich Standard
Time" would get him UTC, or at least something a lot less oddball than
what he got.
But wait a minute ... looking into the tzdb sources, I find that Iceland
hasn't observed DST since 1968, and tzdb spells their zone abbreviation as
"GMT" since then. That means that Atlantic/Reykjavik is actually a way
better approximation to "plain GMT" than Europe/London is. Maybe there
is some method in CLDR's madness here.
>> * The miscellaneous deltas shown in the attached diff, which in
>> many cases boil down to "we chose the first name mentioned for the
>> zone, while CLDR did something else". I felt that our historical
>> mappings of these cases weren't wrong enough to justify any
>> political flak I might take for changing them. OTOH, maybe we
>> should just say "we follow CLDR" and be done with it.
> Eyeballing these, three look strange to me in a list of otherwise
> city-based names: Pacific/Guam (instead of Port Moresby, capital of
> PNG which apparently shares zone rules with the territory of Guam) and
> Pacific/Samoa (country name instead of its capital Apia; the city
> avoids any potential confusion with American Samoa which is on the
> other side of the date line) and then "CET", an abbreviation.
Oooh. Looking closer, I see that the Windows zone is defined as
<!-- (UTC+13:00) Samoa -->
which makes it *definitely* Pacific/Apia ... Pacific/Samoa is a
link to Pacific/Pago_Pago which is in American Samoa, at UTC-11.
So our mapping was kind of okay up till 2011 when Samoa decided
they wanted to be on the other side of the date line, but now
it's wrong as can be. Ooops.
> But
> debating individual points of geography and politics like this seems a
> bit silly... I wasn't really aware of this Windows->Olson zone name
> problem lurking in our tree before, but it sounds to me like switching
> to 100% "we use CLDR, if you think it's wrong, please file a report at
> cldr.unicode.org" wouldn't be a bad idea at all!
I'd still defend our exception for Central America: CLDR maps that
to Guatemala which seems pretty random, even if they haven't observed
DST there for a few years. For the rest of it, though, "we follow CLDR"
has definitely got a lot of attraction. The one change that makes me
nervous is adopting Europe/Berlin for "W. Europe Standard Time",
on account of the flak Paul Eggert just got from trying to make a
somewhat-similar change :-(. (If you don't read the tz mailing list
you may not be aware of that particular tempest in a teapot, but he
tried to merge a bunch of zones into Europe/Berlin, and there were
a lot of complaints. Some from me.)
regards, tom lane
Hi, On October 2, 2021 3:26:35 PM PDT, Tom Lane <tgl@sss.pgh.pa.us> wrote: >However, the example >we started the thread with is that Andres thought "Greenwich Standard >Time" would get him UTC, or at least something a lot less oddball than >what he got. FWIW, that was just the default on those machines (which in turn seems to be the default of some containers Microsoft distributes),not something I explicitly chose. - Andres -- Sent from my Android device with K-9 Mail. Please excuse my brevity.
On Sun, Oct 3, 2021 at 11:26 AM Tom Lane <tgl@sss.pgh.pa.us> wrote: > > Eyeballing these, three look strange to me in a list of otherwise > > city-based names: Pacific/Guam (instead of Port Moresby, capital of > > PNG which apparently shares zone rules with the territory of Guam) and > > Pacific/Samoa (country name instead of its capital Apia; the city > > avoids any potential confusion with American Samoa which is on the > > other side of the date line) and then "CET", an abbreviation. > > Oooh. Looking closer, I see that the Windows zone is defined as > <!-- (UTC+13:00) Samoa --> > which makes it *definitely* Pacific/Apia ... Pacific/Samoa is a > link to Pacific/Pago_Pago which is in American Samoa, at UTC-11. > So our mapping was kind of okay up till 2011 when Samoa decided > they wanted to be on the other side of the date line, but now > it's wrong as can be. Ooops. Hah. That's a *terrible* link to have. > I'd still defend our exception for Central America: CLDR maps that > to Guatemala which seems pretty random, even if they haven't observed > DST there for a few years. For the rest of it, though, "we follow CLDR" > has definitely got a lot of attraction. The one change that makes me > nervous is adopting Europe/Berlin for "W. Europe Standard Time", > on account of the flak Paul Eggert just got from trying to make a > somewhat-similar change :-(. It would be interesting to know if that idea of matching BCP47 locale names to territories could address that. Perhaps we should get that modern-locale-name patch first (I think I got stuck on "let's kill off old Windows versions so we can use this", due to confusing versioning and a lack of a guiding policy on our part, but I think I should just propose something), and then revisit this? > (If you don't read the tz mailing list > you may not be aware of that particular tempest in a teapot, but he > tried to merge a bunch of zones into Europe/Berlin, and there were > a lot of complaints. Some from me.) I don't follow the list but there was a nice summary in LWN: "A fork for the time-zone database?". From the peanut gallery, I thought it was a bit of a double standard, considering the rejection of that idea of yours about getting rid of longitude-based pre-standard times on data stability grounds, and a lot less justifiable. I hope there isn't a fork.
Andres Freund <andres@anarazel.de> writes:
> On October 2, 2021 3:26:35 PM PDT, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> However, the example
>> we started the thread with is that Andres thought "Greenwich Standard
>> Time" would get him UTC, or at least something a lot less oddball than
>> what he got.
> FWIW, that was just the default on those machines (which in turn seems to be the default of some containers Microsoft
distributes),not something I explicitly chose.
So *somebody* thought it was an unsurprising default ...
regards, tom lane
Thomas Munro <thomas.munro@gmail.com> writes: > On Sun, Oct 3, 2021 at 11:26 AM Tom Lane <tgl@sss.pgh.pa.us> wrote: >> I'd still defend our exception for Central America: CLDR maps that >> to Guatemala which seems pretty random, even if they haven't observed >> DST there for a few years. For the rest of it, though, "we follow CLDR" >> has definitely got a lot of attraction. Actually ... digging in the archives, the reason we have a special case for Central America is that there was a user complaint about the previous mapping to CST6CDT: https://www.postgresql.org/message-id/flat/1316149023380-4809498.post%40n5.nabble.com CST6CDT was *way* wrong, because it implies USA DST rules, so the complaint was well-founded. I wrote in that thread: > I think we ought to map "Central America Standard Time" to plain CST6. > (Or we could map to one of America/Costa_Rica, America/Guatemala, > America/El_Salvador, etc, but that seems more likely to offend people in > the other countries than provide any additional precision.) However, if we can cite CLDR as authority, I see no reason why America/Guatemala should be any more offensive than any of the other fairly-arbitrary choices CLDR has made. None of those zones have observed DST for a decade or more, so at least in recent years it wouldn't make any difference anyway. So, I'm now sold on just making all our mappings match CLDR. I'll do that in a couple of days if I don't hear objections. > It would be interesting to know if that idea of matching BCP47 locale > names to territories could address that. Perhaps we should get that > modern-locale-name patch first (I think I got stuck on "let's kill off > old Windows versions so we can use this", due to confusing versioning > and a lack of a guiding policy on our part, but I think I should just > propose something), and then revisit this? That seems like potentially a nice long-term solution, but it doesn't sound likely to be back-patchable. So I'd like to get the existing data in as good shape as we can before we go looking for a replacement mechanism. regards, tom lane