From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | edouard(dot)hibon(at)free(dot)fr |
Cc: | pgsql-bugs(at)lists(dot)postgresql(dot)org |
Subject: | Re: BUG #17244: strange string to timestamp with time zone conversion due to summer time / winter time switch ? |
Date: | 2021-10-22 15:27:16 |
Message-ID: | 2712289.1634916436@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
PG Bug reporting form <noreply(at)postgresql(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2021-10-22 19:26:49 | Re: Inconsistent behavior of pg_dump/pg_restore on DEFAULT PRIVILEGES |
Previous Message | Marek Läll | 2021-10-22 14:48:14 | Re: BUG #17244: strange string to timestamp with time zone conversion due to summer time / winter time switch ? |