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.klaver@aklaver.com