Re: The contents of the pg_timezone_names view bring some surprises

On 5/18/21 11:31 PM, Bryn Llewellyn wrote:
> Some time zones have abbreviations that are identical to their names.
> This query:
>
> Here’s what seems to me to be a closely related dilemma. I’d thought
> that an abbrev uniquely specified the utc_offset. But this test shows
> that it doesn’t:
>
> with
>   v1 as (
>     select distinct abbrev, utc_offset
>     from pg_timezone_names),
>   v2 as (
>     select abbrev, count(*)
>     from v1
>     group by abbrev
>     having count(*) > 1)
> select name, abbrev, utc_offset, is_dst
> from pg_timezone_names
> where abbrev in (select abbrev from v2)
> order by abbrev;
>
> It gets 46 rows. Here’s an interesting subset:
>
> America/Monterrey              | CDT    | -05:00:00  | t
> America/Havana                 | CDT    | -04:00:00  | t
>
> ROC                            | CST    | 08:00:00   | f
> America/Costa_Rica             | CST    | -06:00:00  | f
>
> Eire                           | IST    | 01:00:00   | f
> Asia/Kolkata                   | IST    | 05:30:00   | f
>
> So here, the same text, even when used as abbrev, can denote different
> utc_offset values. (But note that there seems to be no way, in the 'at
> time zone' clause, that I can say that I want a text value to be taken
> as a name and not as an abbreviation, or vice versa.)
>
> This seems to be at odds with what section “8.5.3. Time Zones” at
>
> https://www.postgresql.org/docs/current/datatype-datetime.html#DATATYPE-TIMEZONES
> <https://www.postgresql.org/docs/current/datatype-datetime.html#DATATYPE-TIMEZONES>
>
> says:
>
> «
> A time zone abbreviation, for example PST. Such a specification merely
> defines a particular offset from UTC, in contrast to full time zone
> names which can imply a set of daylight savings transition rules as
> well. The recognized abbreviations are listed in
> the pg_timezone_abbrevs view (see Section 51.91). You cannot set the
> configuration parameters TimeZone or log_timezone to a time zone
> abbreviation, but you can use abbreviations in date/time input values
> and with the AT TIME ZONE operator.
> »
>
> This claims (as I read it) that a time zone abbreviation uniquely
> determines an offset from UTC.

It says no such thing and would be lying if it did. Take a look at this:

https://en.wikipedia.org/wiki/List_of_time_zone_abbreviations

and see the abbreviations that share offsets.

What it is saying that, for example, the timezone America/Los_Angeles
has two timezone abbreviations PDT(what I'm currently in) and PST. If
you use an abbreviation you don't get the DST transition rules that a
full timezone name has.

>
> It seems that the result of this is therefore undefined because CDT
> denotes two different utc_offset values.:
>
> select ('2000-01-01 12:00:00 +00:00'::timestamptz) at time zone 'CDT';
>
> The same goes for this:
>
> select ('2000-01-01 12:00:00 +00:00'::timestamptz) at time zone 'EET';
>
> In summary, each of these texts, for two kinds of reason, can denote two
> different utc_offset values.:
>
> CET
> EET
> CDT
> CST
> IST
>
> Am I missing an essential clue to resolving what seems to me to be a
> paradox? Or am I seeing two kinds of bug?
>
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com