From: Tom Lane <tgl@sss.pgh.pa.us>
To: Andrew Dunstan <andrew@dunslane.net>
Cc: Andres Freund <andres@anarazel.de>
Cc: pgsql-hackers@postgresql.org, Peter Eisentraut <peter_e@gmx.net>
Cc: Petr Jelinek <petr@2ndquadrant.com>
Subject: Re: 002_types.pl fails on some timezones on windows
Date: Thu, 30 Sep 2021 16:03:15 -0400
Message-ID: <3205377.1633032195@sss.pgh.pa.us> (raw)
In-Reply-To: <3204190.1633030718@sss.pgh.pa.us>
References: <20210930183641.7lh4jhvpipvromca@alap3.anarazel.de>
<f4b9c195-7caa-4849-ba67-49a5d153e203@dunslane.net>
<3204190.1633030718@sss.pgh.pa.us>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: tgl@sss.pgh.pa.us, andrew@dunslane.net, andres@anarazel.de, peter_e@gmx.net, petr@2ndquadrant.com
Subject: Re: 002_types.pl fails on some timezones on windows
In-Reply-To: <3205377.1633032195@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox