From: Tom Lane <tgl@sss.pgh.pa.us>
To: nicola.oricchio@vertigis.com
cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18518: ::timestamp add minutes and seconds to the converted
 values
In-reply-to: <18518-e16f6cc69c9ce413@postgresql.org>
References: <18518-e16f6cc69c9ce413@postgresql.org>
Comments: In-reply-to PG Bug reporting form <noreply@postgresql.org>
	message dated "Thu, 20 Jun 2024 15:33:29 -0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <590600.1718981426.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Fri, 21 Jun 2024 10:50:26 -0400
Message-ID: <590601.1718981426@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/590601.1718981426%40sss.pgh.pa.us>
Precedence: bulk

PG Bug reporting form <noreply@postgresql.org> writes:
> select dat, (dat at time zone 'utc')::timestamp without time zone from a=
aa
> order by 1;

This will involve a time zone conversion from UTC to your current
timezone setting.  Possibly that's different between your two
installations?  The first results you show are consistent with
Europe/Berlin.

>  1892-12-31 23:00:00 | 1893-12-31 23:53:28 <-- it should be 1893-01-01 0=
0:00:00

This is not a bug, nor is it a recent behavioral change.  If you don't
like it, you can go complain to IANA's timezone database crew [1],
but your odds of getting them to change it are about zero IMO.  Their
practice is to use a zone offset based on the representative city's
local mean solar time in years before that area adopted standardized
timezones.  You'll find the same behavior in any other software that
depends on the IANA tz data.  For instance, on my Linux workstation:

$ TZ=3DAmerica/New_York date --date=3D'1892-12-31 23:00:00 UTC'
Sat Dec 31 18:00:00 EST 1892

$ TZ=3DEurope/Berlin date --date=3D'1892-12-31 23:00:00 UTC'
Sat Dec 31 23:53:28 LMT 1892

New York is not less wonky than Berlin; they just adopted a
standard zone a few years sooner, so you have to go back
a little further to see the "LMT" output:

$ TZ=3DAmerica/New_York date --date=3D'1880-12-31 23:00:00 UTC'
Fri Dec 31 18:03:58 LMT 1880

			regards, tom lane

[1] https://www.iana.org/time-zones