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
Date: Fri, 21 Jun 2024 10:50:26 -0400
Message-ID: <590601.1718981426@sss.pgh.pa.us> (raw)
In-Reply-To: <18518-e16f6cc69c9ce413@postgresql.org>
References: <18518-e16f6cc69c9ce413@postgresql.org>
PG Bug reporting form <noreply@postgresql.org> writes:
> select dat, (dat at time zone 'utc')::timestamp without time zone from aaa
> 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 00: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=America/New_York date --date='1892-12-31 23:00:00 UTC'
Sat Dec 31 18:00:00 EST 1892
$ TZ=Europe/Berlin date --date='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=America/New_York date --date='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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, nicola.oricchio@vertigis.com, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18518: ::timestamp add minutes and seconds to the converted values
In-Reply-To: <590601.1718981426@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox