Re: 002_types.pl fails on some timezones on windows - Mailing list pgsql-hackers

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
     }