Re: The contents of the pg_timezone_names view bring some surprises - Mailing list pgsql-general

On 5/22/21 5:52 PM, Bryn Llewellyn wrote:
> Tom, David, Adrian, and Peter—thank you all very much for taking an 
> interest in my questions. Your responses have, collectively, been an 
> enormous help. I deleted the text of the exchanges in this particular 
> branch of the overall discussion because it's become rather difficult to 
> work out who said what in response to what. It's all there in the 
> "pgsql-general" email archive.
> 
> Here's my summary (in no particular order) of what I've learned. Please 
> feel free to ignore it.
> 
> ----------------------------------------------------------------------
> 
> (1) In the context of discussing timezones, the English word 
> "abbreviation" maps to two distinct notions in PG: 
> "pg_timezone_names.abbrev"; and "pg_timezone_abbrevs.abbrev". Tom 
> earlier said « there are two views [because there are] two sources of 
> truth involved ». This really means that these two columns list 
> different classes of facts. It's therefore unfortunate that they both 
> have the same name.

They are reporting the same facts, just in different context. 
pg_timezone_abbrevs.abbrev is a list of un-duplicated, as I found out, 
abbreviations. pg_timezone_names.abbrev shows the abbreviations or 
offsets in effect at CURRENT_TIMESTAMP.

> 
> ----------------------------------------------------------------------
> 
> (2) It's no wonder, therefore, that I was confused. Anybody with even a 
> slight exposure to relational design would guess 
> that "pg_timezone_names.abbrev" is a FK to the PK in 
> "pg_timezone_abbrevs.abbrev". And they might wonder why "utc_offset" and 
> "is_dst" seem to be denormalized. But they'd probably recall that such 
> things are common, as a usability convenience, in views. Anyway, I'm 
> beyond that confusion now.

Since the views are based on functions that are displaying different 
context I don't find it surprising.

> 
> ----------------------------------------------------------------------
> 
> (3) It helps me to think of "pg_timezone_names.abbrev" as 
> "nickname"—which notion is unique, just for each name. It's useful only 
> informally, and only when a particular timezone observes DST, as a 
> shorthand for disambiguation. I used the example earlier:
> 

> 
> The fact that Asia/Manila happens not to observe DST makes my example a 
> little less powerful. Never mind, their politicians could decide to 
> introduce it presently and to give DST the nickname BST (or DOG). It's 
> remarkable, in itself, that "ST" in "BST" means "Summer Time" but that 
> it means "Standard Time" in "PST". But this nicely unerlines the point 
> that there's no rhyme or reason in the design of these abbreviations.
> 
> Notably, the "nickname" in "pg_timezone_names" has nothing at all 
> (formally) to do with "abbrev" in "pg_timezone_abbrevs".

Yes it does. It just there is not always a one-to-one correspondence 
between the two. For instance duplicate abbreviations do exist but they 
are filtered out of pg_timezone_abbrevs.

> 
> ----------------------------------------------------------------------
> 
> (4) My realization, as set out in #3, helps me now to understand the rule:
> 
> « PostgreSQL allows you to specify time zones in three different 
> forms... A time zone abbreviation, for example PST [but ONLY] in 
> date/time input values and with the AT TIME ZONE operator… Such a 
> specification merely defines a particular offset from UTC... The 
> recognized abbreviations are listed in the pg_timezone_abbrevs view »
> 
> This (to my embarrassment, now) does say that a "nickname" from 
> "pg_timezone_names" is not allowed as the argument for the AT TIME ZONE 
> operator. But it doesn't adumbrate that the English word "abbreviation" 
> means what I now understand it to mean. You need to get the point from 
> one terse sentence without the luxury of examples to strengthen the 
> explanation.
> 

> silently succeeds. Sure enough, the doc does say « PostgreSQL will 
> accept POSIX-style time zone specifications, as described in Section 
> B.5. [as the argument for AT TIME ZONE] » But what a quagmire of 
> confusion this is. I said elsewhere that the same aim (get the local 
> time at -42 hours w.r.t. UTC) can be met with transparent syntax, thus:
> 
> *select ('2021-05-22 15:00:00 America/Los_Angeles'::timestamptz at time 
> zone 'UTC') - '42:00'::interval;*
> 
> I know which syntax I prefer!

That is the root of the above, preference. There are many ways to 
express time zones and as a general purpose database Postgres needs to 
deal with all of them.

> 
> ----------------------------------------------------------------------
> 
> (5) I was embarrassingly slow to find this in the doc for the SET command:
> 
> « Timezone settings given as numbers or intervals are internally 
> translated to POSIX timezone syntax. For example, after SET TIME ZONE 
> -7, SHOW TIME ZONE would report <-07>+07. »
> 

> (David pointed this out.) This is confusing in itself. And it's 
> compounded by this fact: POSIX uses positive offsets to denote what both 
> the utc_offset column and to_char(<some timestamptz value>), with the 
> format element TH:TM, shows as negative. But it is what it is.

See my earlier post about trying to find logic in this.

> 
> ----------------------------------------------------------------------
> 
> (6) To the extent that a catalog view can have a business unique key 
> (and I believe that this must be the case for it to be useful), the 
> unique key for "pg_timezone_names" is "name" and the unique key for 
> "pg_timezone_abbrevs" is "abbrev". I'm going to assume that PG has code 
> to maintain these invariants. This gives me the way to interpret the 
> statement "an [abbreviation] specification merely defines a particular 
> offset from UTC".
> 
> 
> «
> The @OVERRIDE syntax indicates that subsequent entries in the file can 
> override previous entries (typically, entries obtained from included 
> files). Without this, conflicting definitions of the same timezone 
> abbreviation are considered an error.
> »
> 
> So even without "timezone_abbreviations" set to "default" at session 
> level, "pg_timezone_abbrevs.abbrev" will always be unique.

True.

> 
> ----------------------------------------------------------------------
> 
> (7) There are two different syntaxes for setting the timezone session 
> environment variable. This (or with TO instead of =):
> 
> *set timezone =*
> 
> and this:
> 
> *set time zone*
> 
> This, too, is hugely confusing. (Correspondingly, there's both "show 
> timezone" and "show time zone".)
> 
> The first "set" (and "show") alternative is consistent with how you set 
> all other session environment variables. Moreover, all are observable 
> with the current_setting() built-in function. And there the argument 
> "timezone" can only be spelled as one word. This makes me favor the "set 
> timezone" spelling.
> 
> However, only the "set time zone" spelling allows an argument that's an 
> explicit interval value like this:
> 
> *set time zone interval '-08:00';*
> 
> I said "explicit interval value" because this isn't true. For example, 
> these two fail:
> 
> *set time zone '-08:00'::interval;*

> 
> and
> 
> *set time zone make_interval(hours=>-8);*
> 
> both fail, even though each uses a legal interval value. This is 
> confusion on top of confusion. All the more reason to avoid it.

It is spelled out here:

https://www.postgresql.org/docs/12/sql-set.html

Basically set time zone is for customizing the timezone value, whereas 
set timezone is for using 'standard' timezone values. They both end up 
setting the same value.

> 
> 
> 
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com