From: Tom Lane <tgl@sss.pgh.pa.us>
To: edouard.hibon@free.fr
cc: pgsql-bugs@lists.postgresql.org
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>
References: <17244-bda5a623ad3ca0ca@postgresql.org>
Comments: In-reply-to PG Bug reporting form <noreply@postgresql.org>
	message dated "Fri, 22 Oct 2021 14:12:02 -0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <2712288.1634916436.1@sss.pgh.pa.us>
Date: Fri, 22 Oct 2021 11:27:16 -0400
Message-ID: <2712289.1634916436@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/2712289.1634916436%40sss.pgh.pa.us>
Precedence: bulk

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