Re: IANA timezone abbreviations versus timezone_abbreviations - Mailing list pgsql-hackers

"Jelte Fennema-Nio" <postgres@jeltef.nl> writes:
> The current situation seems utterly messed up though. One thing that
> shocks me is that we're, by default and without warning, parsing IST as
> Israel Standard Time instead of the timezone that 17% of the world's
> population uses: Indian Standard Time. And even with this patch that
> behaviour only changes if you set your timezone to Asia/India. Which
> seems suboptimal, because even as a European myself, IST means Indian
> Standard Time.

This argument would hold more water if we'd gotten more than
approximately zero field complaints about the current setup.
I think most folk who want that just use the documented solution
of setting timezone_abbreviations = 'India'.

Certainly there's an argument to be made that we should have gone
for a minimal rather than maximal default list of abbreviations.
But it's a couple of decades too late to be making that argument ---
at this point backwards compatibility is a huge consideration IMV.
(The current design dates from d8b5c95ca of 2006-07-25, just to
clarify how long this has stood.)

> 1. Change the default of timezone_abbreviations to an empty list.

The villagers would be on our doorstep with pitchforks if we did
that.  It's remarkable how many of these abbreviations have live
constituencies --- try searching the commit log for "abbreviation"
to find a lot of commits that added zone abbrevs based on user
complaints.  Now, most of those complaints were ten or more years
back, so maybe people no longer care as much ... but I doubt that's
the way to bet.

> 2. When parsing search for the abbreviation string in the IANA timezone
>    database. 

Searching the whole IANA database isn't very practical I fear.
We do something approximately that costly to identify the default
timezone setting --- and that is code that got moved into initdb
precisely because it was too slow to be tolerable as part of
postmaster start.  It might work to calculate the set of abbrevs
known in the IANA data once and cache it; but where, and how
would we know when to update the cache?

Another thing that complicates all this is that our
timezone_abbreviations data reflects a very old state of the IANA
database, from when they were of a mindset that every zone should have
alphabetic timezone abbrevs even if they had to make them up.  A few
of those choices achieved real-world currency, but most didn't, and
starting in about 2015 the IANA crew removed (most of?) the ones
they'd made up in favor of numerical UTC offsets.  We kept them all,
for fear of user complaints if we removed them.  So we're now in a
situation where timezone_abbreviations knows a lot of abbrevs that
are no longer to be found in IANA, and it's very unclear how many
people might be depending on those entries.  It's probably more than
none though.  (Conversely, IANA does still have some historical
abbrevs such as "LMT" that aren't in timezone_abbreviations.)

Anyway, I'm of the opinion that we should be after a minimal change
not a grand rewrite that removes all ambiguity.  Timezones are such
a political mess that we probably couldn't reach 100% clarity anyway.
We could definitely do major damage to the system's usability
though, if we don't tread carefully.

            regards, tom lane