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
