Hi.
I don't know if it is useful but a big red competitor behaves in a way you suggest. It has been a reasonable choice for them.
Here is the log of experiment:
SELECT timestamp'2003-03-30 01:59:59 Europe/Brussels' from dual;
--------------------
2003-03-30T00:59:59Z
SELECT timestamp'2003-03-30 02:00:00 Europe/Brussels' from dual;
ORA-01878: specified field not found in datetime or interval
SELECT timestamp'2003-03-30 02:59:59 Europe/Brussels' from dual;
ORA-01878: specified field not found in datetime or interval
SELECT timestamp'2003-03-30 03:00:00 Europe/Brussels' from dual;
--------------------
2003-03-30T01:00:00Z
Regards
Marek
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.