From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | Bryn Llewellyn <bryn(at)yugabyte(dot)com>, pgsql-general list <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: The contents of the pg_timezone_names view bring some surprises |
Date: | 2021-05-19 13:57:13 |
Message-ID: | bf3f1459-f1be-b816-4aa9-a56ff62abbc9@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2021-05-19 15:00:18 | Re: PG 10 experience different user execute same sql get different access plan |
Previous Message | Tom Lane | 2021-05-19 13:52:02 | Re: The contents of the pg_timezone_names view bring some surprises |