PG Bug reporting form <noreply@postgresql.org> writes:
> With current_setting('timezone') = 'Europe/Brussels', the following
> conversion results are quite strange :
> 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.
Well, you're asking a silly question and getting a silly answer.
In that zone, clocks advanced from 1:59:59 directly to 3:00:00 on
that day, so that the time 02:59:59 didn't really exist. Our approach
for such cases is to assume that the invalid time is meant to represent
local standard time, making it 0:59:59 later than the DST transition
instant, while 03:00:00 is read as exactly the transition instant.
See
https://www.postgresql.org/docs/current/datetime-invalid-input.html
> 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.
If you'd made that suggestion about twenty years ago, we might have
taken it, but at this point backwards compatibility is a pretty strong
argument for not changing it. In any case, there's lots of precedent
for this type of behavior, eg mktime(3) acts similarly on most Unix
platforms.
regards, tom lane
