Re: Where is the syntax "<-08>+08" documented—comes from "select current_setting('timezone')" - Mailing list pgsql-docs

From Bryn Llewellyn
Subject Re: Where is the syntax "<-08>+08" documented—comes from "select current_setting('timezone')"
Date
Msg-id 8A97A8C5-566D-4C4C-9A1F-816A49A64704@yugabyte.com
Whole thread Raw
In response to Re: Where is the syntax "<-08>+08" documented—comes from "select current_setting('timezone')"  ("David G. Johnston" <david.g.johnston@gmail.com>)
Responses Re: Where is the syntax "<-08>+08" documented—comes from "select current_setting('timezone')"  ("David G. Johnston" <david.g.johnston@gmail.com>)
List pgsql-docs
david.g.johnston@gmail.com wrote:

On Thu, May 13, 2021 at 12:38 PM Bryn Llewellyn <bryn@yugabyte.com> wrote:
« POSIX has positive signs west of Greenwich, but many people expect positive signs east of Greenwich. »

Is there a setting that I can do to make "set timezone = '-08:00'" treat it as a request to set the negative value that I said?

That ultimately comes from the appendix.


As noted here:


There are three ways to specify TimeZone in PostgreSQL.  You are using the third option and thus are dealing with the caveats noted in the third entry's description and described in detail in the appendix linked to above.

I suggest restricting your use to one of the other two, recommended, options.  If you cannot avoid using the third option you must learn and abide by its rules.

Thanks for the quick reply, David. I'd already worked out that the overwhelmingly common case for setting a time zone calls for using its _name_ because that's the key to the DST rules and therefore correct answers when you view an extant timestamptz value. For example, it would seem to be crazy when viewing an extant timestamptz value in San Francisco that denotes a date some time in June to ask to see it at an offset of -8 hours.

Reading between your lines, the answer to my question « Where is the syntax "<-08>+08" documented? » is NOWHERE! The "B.5. POSIX Time Zone Specifications" page that you pointed me to has not a single example of a "set timezone" statement that does in in the POSIX style. And not a single example of what "show timezone" gives after doing a POZIX style setting. (There's no hit for the left- or right-chevron on the page.)

I'll conclude that there's no usable doc on this because the recommendation that your second URL leads to is "Don't ever do anything that needs you to understand that counter-intuitive mess."

As I see it, even using a time zone abbreviation like "PDT" is unhelpful (as that second URL shows).

I'm presently documenting the date-time story for YugabyteDB's "as is" exposure of the PostgreSQL SQL processing layer. I'll simply recommend always to use a full name (from pg_timezone_names.name) as the argument of "set timezone" and mention that anything else is dangerous and generally not useful.


pgsql-docs by date:

Previous
From: "David G. Johnston"
Date:
Subject: Re: Where is the syntax "<-08>+08" documented—comes from "select current_setting('timezone')"
Next
From: "David G. Johnston"
Date:
Subject: Re: Where is the syntax "<-08>+08" documented—comes from "select current_setting('timezone')"