From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Andrew Dunstan <andrew(at)dunslane(dot)net> |
Cc: | Andres Freund <andres(at)anarazel(dot)de>, pgsql-hackers(at)postgresql(dot)org, Peter Eisentraut <peter_e(at)gmx(dot)net>, Petr Jelinek <petr(at)2ndquadrant(dot)com> |
Subject: | Re: 002_types.pl fails on some timezones on windows |
Date: | 2021-09-30 20:03:15 |
Message-ID: | 3205377.1633032195@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Alvaro Herrera | 2021-09-30 20:04:34 | Re: prevent immature WAL streaming |
Previous Message | Tom Lane | 2021-09-30 19:38:38 | Re: 002_types.pl fails on some timezones on windows |