Horton

Re: 002_types.pl fails on some timezones on windows

Tom Lane <tgl@sss.pgh.pa.us>

View thread View on postgr.es
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Andrew Dunstan <andrew@dunslane.net>
Cc: Andres Freund <andres@anarazel.de>, pgsql-hackers@postgresql.org, Peter Eisentraut <peter_e@gmx.net>, Petr Jelinek <petr@2ndquadrant.com>
Date: 2021-09-30T20:03:15Z
Lists: pgsql-hackers 
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

Commits

  1. Doc: improve timezone/README's recipe for tracking Windows zones.

  2. Update our mapping of Windows time zone names some more.

  3. Update our mapping of Windows time zone names using CLDR info.

  4. Re-alphabetize the win32_tzmap[] array.

  5. Remove gratuitous environment dependency in 002_types.pl test.