From: Tom Lane <tgl@sss.pgh.pa.us>
To: Aleksander Alekseev <aleksander@timescale.com>
Cc: Michael Paquier <michael@paquier.xyz>
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: TimestampTz->Text->TimestampTz casting fails with DateStyle 'Postgres'
Date: Wed, 11 Dec 2024 17:40:38 -0500
Message-ID: <536069.1733956838@sss.pgh.pa.us> (raw)
In-Reply-To: <521765.1733949428@sss.pgh.pa.us>
References: <CAJ7c6TOATjJqvhnYsui0=CO5XFMF4dvTGH+skzB--jNhqSQu5g@mail.gmail.com>
<3416600.1730987836@sss.pgh.pa.us>
<CAJ7c6TMzJDLutYt+pcm1Jt+sDSRHjvjezM9o8heEoo_LwgP75A@mail.gmail.com>
<CAJ7c6TMpBUMLqep4ycxySN+SFsf-eG2v-nUaVSzu2jSd5eDMAA@mail.gmail.com>
<Z1eKmoJHXVd8_oz1@paquier.xyz>
<4167039.1733796752@sss.pgh.pa.us>
<394372.1733884998@sss.pgh.pa.us>
<521765.1733949428@sss.pgh.pa.us>
I wrote:
> I had a second thought here. The original problem is not really
> restricted to "LMT", though that case tends to give an obvious
> "no such timezone abbreviation" failure. There is a more insidious
> possibility that the cast-back-and-forth succeeds but yields a changed
> timestamp value, because the abbreviation emitted by timestamptz_out
> is recognized but interpreted differently by timestamptz_in.
> I believe that that's at least theoretically possible today, because
> what timestamptz_out prints for the abbreviation comes out of the
> prevailing zone's TZDB entry, but what timestamptz_in consults is the
> timezone_abbreviations list. If our list is out of sync with TZDB,
> even for just part of the history of a zone, we've got problems.
I decided to troll through TZDB to see whether this is a live issue
or not, and it didn't take me more than a couple minutes to find
such a case:
regression=# set timezone to 'America/Montevideo';
SET
regression=# set datestyle to postgres;
SET
regression=# select '1912-01-01'::timestamptz;
timestamptz
------------------------------
Mon Jan 01 00:00:00 1912 MMT
(1 row)
regression=# select '1912-01-01'::timestamptz::text::timestamptz;
timestamptz
------------------------------
Sun Dec 31 13:45:09 1911 MMT
(1 row)
That's because our default timezone_abbreviations list thinks MMT
means
MMT 23400 # Myanmar Time (obsolete)
whereas TZDB has this for Montevideo:
Zone America/Montevideo -3:44:51 - LMT 1908 Jun 10
-3:44:51 - MMT 1920 May 1 # Montevideo MT
-4:00 - %z 1923 Oct 1
...
Other examples are not hard to come by, eg the same date
in zone Europe/Athens. So this shows that the problem is
real and it can result in sizable errors.
I now recall that our timezone abbreviation list was built by
considering *current* zone abbreviations that appeared in TZDB
whenever we made that list, a decade or two back. We never
thought of trying to cope with historical values. There are
many more conflicting abbreviations if you include the
historical entries.
So it feels like we'd better do something about this, if we
don't want to deprecate the Postgres datestyle entirely.
I find it scary that the solution involves changing the
behavior of timestamptz_in, because that's surely got hazards
of unexpected side-effects; but it's clear that this is a mess.
regards, tom lane
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, aleksander@timescale.com, michael@paquier.xyz, pgsql-bugs@lists.postgresql.org
Subject: Re: TimestampTz->Text->TimestampTz casting fails with DateStyle 'Postgres'
In-Reply-To: <536069.1733956838@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