From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: edouard.hibon@free.fr
Subject: BUG #17244: strange string to timestamp with time zone conversion due to summer time / winter time switch ?
Date: Fri, 22 Oct 2021 14:12:02 +0000
Message-ID: <17244-bda5a623ad3ca0ca@postgresql.org> (raw)
The following bug has been logged on the website:
Bug reference: 17244
Logged by: Edouard HIBON
Email address: edouard.hibon@free.fr
PostgreSQL version: 14.0
Operating system: windows 10
Description:
With current_setting('timezone') = 'Europe/Brussels', the following
conversion results are quite strange :
SELECT '2003-03-30 02:59:59'::timestamp without time zone < '2003-03-30
03:00:00'::timestamp without time zone returns True which sounds good.
SELECT '2003-03-30 02:59:59'::timestamp with time zone < '2003-03-30
03:00:00'::timestamp with time zone returns False which sounds strange.
In the same way, SELECT tstzrange('2003-03-30 02:59:59', '2003-03-30
03:00:00') generates the ERROR 'the lower bound must be less or equal than
the upper bound'.
In the real world, one hour has been added at 2.00AM on the 30th of March
2003 and which corresponds to the winter time / summer time switch, so that
the time between 2.00AM and 2.59AM never existed on that day, and people
have 'virtually' lost 1 hour according to their watch.
In the PostgresSQL conversion, it seems that the offset of the timezone is
increased at 02.00AM on the 30th of March 2003 which sounds good, and one
hour is automatically added to the timestamps with time zone between 02.00AM
and 2.59AM. From 03.00AM the conversion doesn't add one hour anymore :
SELECT '2003-03-30 01:59:59'::timestamp with time zone -> 2003-03-30
01:59:59+01 => OK
SELECT '2003-03-30 02:00:00'::timestamp with time zone -> 2003-03-30
03:00:00+02 => OK because '2003-03-30 02:00:00+01' = '2003-03-30
03:00:00+02'
SELECT '2003-03-30 02:59:59'::timestamp with time zone -> 2003-03-30
03:59:59+02 => OK because '2003-03-30 02:59:59+01' = '2003-03-30
03:59:59+02'
SELECT '2003-03-30 03:00:00'::timestamp with time zone -> 2003-03-30
03:00:00+02 => KO because the conversion of the string '2003-03-30 03:00:00'
is 59 minutes 59 seconds before the conversion of the string '2003-03-30
02:59:59' and this is not consistent from a timeline perspective.
This behavior might be not understandable from a user standpoint and may
conduct to some errors in the system. I would suggest to forbid the
conversion to timestamp with time zone for strings between '2003-03-30
02:00:00' and '2003-03-30 02:59:59' with an explicit error message saying
that the resulting timestamp doesn't correrspond to a real time due to the
winter time / summer time switch.
The documentation explains well the time zones effects, but I didn't see
anything about the summer time / winter time switch.
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-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, edouard.hibon@free.fr
Subject: Re: BUG #17244: strange string to timestamp with time zone conversion due to summer time / winter time switch ?
In-Reply-To: <17244-bda5a623ad3ca0ca@postgresql.org>
* 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