Received: from malur.postgresql.org ([217.196.149.56])
	by arkaria.postgresql.org with esmtps
 (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256)
	(Exim 4.92)
	(envelope-from <pgsql-hackers-owner+archive@lists.postgresql.org>)
	id 1mWI4N-0008UI-6P
	for pgsql-hackers@arkaria.postgresql.org; Fri, 01 Oct 2021 12:55:19 +0000
Received: from localhost ([127.0.0.1] helo=malur.postgresql.org)
	by malur.postgresql.org with esmtp (Exim 4.92)
	(envelope-from <pgsql-hackers-owner+archive@lists.postgresql.org>)
	id 1mWI4M-0005Ix-1H
	for pgsql-hackers@arkaria.postgresql.org; Fri, 01 Oct 2021 12:55:18 +0000
Received: from makus.postgresql.org ([2001:4800:3e1:1::229])
	by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256)
	(Exim 4.92)
	(envelope-from <tgl@sss.pgh.pa.us>)
	id 1mWI4L-0005G9-PB
	for pgsql-hackers@lists.postgresql.org; Fri, 01 Oct 2021 12:55:17 +0000
Received: from sss.pgh.pa.us ([66.207.139.130])
	by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256)
	(Exim 4.92)
	(envelope-from <tgl@sss.pgh.pa.us>)
	id 1mWI4J-0007rs-GF
	for pgsql-hackers@postgresql.org; Fri, 01 Oct 2021 12:55:16 +0000
Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1])
	by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 191Ct4b23302649;
	Fri, 1 Oct 2021 08:55:04 -0400
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Thomas Munro <thomas.munro@gmail.com>
cc: Andrew Dunstan <andrew@dunslane.net>, Andres Freund <andres@anarazel.de>,
        pgsql-hackers <pgsql-hackers@postgresql.org>,
        Peter Eisentraut <peter_e@gmx.net>,
        Petr Jelinek <petr@2ndquadrant.com>
Subject: Re: 002_types.pl fails on some timezones on windows
In-reply-to: <3266414.1633045628@sss.pgh.pa.us>
References: <20210930183641.7lh4jhvpipvromca@alap3.anarazel.de>
 <f4b9c195-7caa-4849-ba67-49a5d153e203@dunslane.net>
 <3204190.1633030718@sss.pgh.pa.us>
 <CA+hUKG+2P0FWyPPPdCC0wFTqPeiMxFM4fCCT6r2zPYztxi5O7w@mail.gmail.com>
 <3266414.1633045628@sss.pgh.pa.us>
Comments: In-reply-to Tom Lane <tgl@sss.pgh.pa.us>
	message dated "Thu, 30 Sep 2021 19:47:08 -0400"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <3302647.1633092904.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Fri, 01 Oct 2021 08:55:04 -0400
Message-ID: <3302648.1633092904@sss.pgh.pa.us>
List-Id: <pgsql-hackers.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-hackers@lists.postgresql.org>
List-Owner: <mailto:pgsql-hackers-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-hackers>
Archived-At: 
 <https://www.postgresql.org/message-id/3302648.1633092904%40sss.pgh.pa.us>
Precedence: bulk

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=3D"Greenwich Standard Time" territory=3D"001" type=3D"Atlan=
tic/Reykjavik"/>
<mapZone other=3D"Greenwich Standard Time" territory=3D"BF" type=3D"Africa=
/Ouagadougou"/>
<mapZone other=3D"Greenwich Standard Time" territory=3D"CI" type=3D"Africa=
/Abidjan"/>
<mapZone other=3D"Greenwich Standard Time" territory=3D"GH" type=3D"Africa=
/Accra"/>
<mapZone other=3D"Greenwich Standard Time" territory=3D"GL" type=3D"Americ=
a/Danmarkshavn"/>
<mapZone other=3D"Greenwich Standard Time" territory=3D"GM" type=3D"Africa=
/Banjul"/>
<mapZone other=3D"Greenwich Standard Time" territory=3D"GN" type=3D"Africa=
/Conakry"/>
<mapZone other=3D"Greenwich Standard Time" territory=3D"GW" type=3D"Africa=
/Bissau"/>
<mapZone other=3D"Greenwich Standard Time" territory=3D"IS" type=3D"Atlant=
ic/Reykjavik"/>
<mapZone other=3D"Greenwich Standard Time" territory=3D"LR" type=3D"Africa=
/Monrovia"/>
<mapZone other=3D"Greenwich Standard Time" territory=3D"ML" type=3D"Africa=
/Bamako"/>
<mapZone other=3D"Greenwich Standard Time" territory=3D"MR" type=3D"Africa=
/Nouakchott"/>
<mapZone other=3D"Greenwich Standard Time" territory=3D"SH" type=3D"Atlant=
ic/St_Helena"/>
<mapZone other=3D"Greenwich Standard Time" territory=3D"SL" type=3D"Africa=
/Freetown"/>
<mapZone other=3D"Greenwich Standard Time" territory=3D"SN" type=3D"Africa=
/Dakar"/>
<mapZone other=3D"Greenwich Standard Time" territory=3D"TG" type=3D"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