Re: BUG #17244: strange string to timestamp with time zone conversion due to summer time / winter time switch ?

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

Kontakt PG Bug reporting form (<noreply(at)postgresql(dot)org>) kirjutas kuupäeval
R, 22. oktoober 2021 kell 17:32:

> The following bug has been logged on the website:
>
> Bug reference: 17244
> Logged by: Edouard HIBON
> Email address: edouard(dot)hibon(at)free(dot)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.
>
>