public inbox for [email protected]
help / color / mirror / Atom feedWhere is the syntax "<-08>+08" documented—comes from "select current_setting('timezone')"
5+ messages / 2 participants
[nested] [flat]
* Where is the syntax "<-08>+08" documented—comes from "select current_setting('timezone')"
@ 2021-05-13 19:38 Bryn Llewellyn <[email protected]>
2021-05-13 20:09 ` Re: Where is the syntax "<-08>+08" documented—comes from "select current_setting('timezone')" David G. Johnston <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: Bryn Llewellyn @ 2021-05-13 19:38 UTC (permalink / raw)
To: Pg Docs <[email protected]>
Summary: Where in the PG docs can I find a self-contained expository essay that explains everything that I need to know in order to understand what's going on in the examples that I show below, and to allow me always confidently to predict the results?
Notice that "show datestyle" gives me the answer "ISO, MDY". It seems to be the default in my env. (I'm in San Francisco.) I never did anything to influence it.
Before the actual tests:
deallocate all;
prepare q as select to_char('2020-01-01 12:00:00'::timestamptz, 'hh24:mi:ss TZH:TZM') as t;
Here’s some examples that produce the strangely formatted answer:
set timezone = -8; -- no quotes
show timezone;
or:
set timezone = '-8'; -- notice the quotes
show timezone;
or:
set timezone = interval '-8 hours';
show timezone;
or even:
set timezone = '<-08>+08';
show timezone;
When "show timezone" answers with "<-08>+08", this:
execute q;
gives the answer "20:00:00 -08:00" as I'd expect from the more transparent spelling of the "set" statements that I did.
Here's a "stress test":
set timezone = '<dog>+08';
show timezone;
I get an upper-cased rendition of what I said: "<DOG>+08". Even now, "execute q" gets the answer that I showed above with "-08:00" for the "TZH:TZM" part.
So it looks like "< ... >" inside the quotes before the number reverses the positive/negative sense in which the number is taken and serves as some kind of comment.
Finally, what looks sensible and tempting:
set timezone = '-08:00';
show timezone;
giving the answer "-08:00" from "show" brings "04:00:00 +08:00" from "execute q". In other words, maximally counter-intuitive.
My search skills aren't up to finding anything that explains what's going on here in the PG doc. I find stuff (who knows if I can trust it) on stack overflow and similar like this:
« 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?
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Where is the syntax "<-08>+08" documented—comes from "select current_setting('timezone')"
2021-05-13 19:38 Where is the syntax "<-08>+08" documented—comes from "select current_setting('timezone')" Bryn Llewellyn <[email protected]>
@ 2021-05-13 20:09 ` David G. Johnston <[email protected]>
2021-05-13 20:34 ` Re: Where is the syntax "<-08>+08" documented—comes from "select current_setting('timezone')" Bryn Llewellyn <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: David G. Johnston @ 2021-05-13 20:09 UTC (permalink / raw)
To: Bryn Llewellyn <[email protected]>; +Cc: Pg Docs <[email protected]>
On Thu, May 13, 2021 at 12:38 PM Bryn Llewellyn <[email protected]> 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.
https://www.postgresql.org/docs/current/datetime-posix-timezone-specs.html
As noted here:
https://www.postgresql.org/docs/current/datatype-datetime.html#DATATYPE-TIMEZONES
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.
David J.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Where is the syntax "<-08>+08" documented—comes from "select current_setting('timezone')"
2021-05-13 19:38 Where is the syntax "<-08>+08" documented—comes from "select current_setting('timezone')" Bryn Llewellyn <[email protected]>
2021-05-13 20:09 ` Re: Where is the syntax "<-08>+08" documented—comes from "select current_setting('timezone')" David G. Johnston <[email protected]>
@ 2021-05-13 20:34 ` Bryn Llewellyn <[email protected]>
2021-05-13 20:56 ` Re: Where is the syntax "<-08>+08" documented—comes from "select current_setting('timezone')" David G. Johnston <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: Bryn Llewellyn @ 2021-05-13 20:34 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; +Cc: Pg Docs <[email protected]>
> [email protected] wrote:
>
> On Thu, May 13, 2021 at 12:38 PM Bryn Llewellyn <[email protected] <mailto:[email protected]>> 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.
>
> https://www.postgresql.org/docs/current/datetime-posix-timezone-specs.html <https://www.google.com/url?q=https://www.postgresql.org/docs/current/datetime-posix-timezone-specs.h...;
>
> As noted here:
>
> https://www.postgresql.org/docs/current/datatype-datetime.html#DATATYPE-TIMEZONES <https://www.google.com/url?q=https://www.postgresql.org/docs/current/datatype-datetime.html%23DATATY...;
>
> 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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Where is the syntax "<-08>+08" documented—comes from "select current_setting('timezone')"
2021-05-13 19:38 Where is the syntax "<-08>+08" documented—comes from "select current_setting('timezone')" Bryn Llewellyn <[email protected]>
2021-05-13 20:09 ` Re: Where is the syntax "<-08>+08" documented—comes from "select current_setting('timezone')" David G. Johnston <[email protected]>
2021-05-13 20:34 ` Re: Where is the syntax "<-08>+08" documented—comes from "select current_setting('timezone')" Bryn Llewellyn <[email protected]>
@ 2021-05-13 20:56 ` David G. Johnston <[email protected]>
2021-05-13 21:12 ` Re: Where is the syntax "<-08>+08" documented—comes from "select current_setting('timezone')" David G. Johnston <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: David G. Johnston @ 2021-05-13 20:56 UTC (permalink / raw)
To: Bryn Llewellyn <[email protected]>; +Cc: Pg Docs <[email protected]>
--000000000000febbd805c23c5d85
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
On Thu, May 13, 2021 at 1:34 PM Bryn Llewellyn <[email protected]> wrote:
> Reading between your lines, the answer to my question =C2=AB Where is the
> syntax "<-08>+08" documented? =C2=BB is NOWHERE! The "B.5. POSIX Time Zon=
e
> 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 singl=
e
> 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.)
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Where is the syntax "<-08>+08" documented—comes from "select current_setting('timezone')"
2021-05-13 19:38 Where is the syntax "<-08>+08" documented—comes from "select current_setting('timezone')" Bryn Llewellyn <[email protected]>
2021-05-13 20:09 ` Re: Where is the syntax "<-08>+08" documented—comes from "select current_setting('timezone')" David G. Johnston <[email protected]>
2021-05-13 20:34 ` Re: Where is the syntax "<-08>+08" documented—comes from "select current_setting('timezone')" Bryn Llewellyn <[email protected]>
2021-05-13 20:56 ` Re: Where is the syntax "<-08>+08" documented—comes from "select current_setting('timezone')" David G. Johnston <[email protected]>
@ 2021-05-13 21:12 ` David G. Johnston <[email protected]>
0 siblings, 0 replies; 5+ messages in thread
From: David G. Johnston @ 2021-05-13 21:12 UTC (permalink / raw)
To: Bryn Llewellyn <[email protected]>; +Cc: Pg Docs <[email protected]>
On Thu, May 13, 2021 at 1:56 PM David G. Johnston <
[email protected]> wrote:
> On Thu, May 13, 2021 at 1:34 PM Bryn Llewellyn <[email protected]> wrote:
>
>> 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.)
>>
>
> From the appendix:
>
>
Reading again I find this:
"In this syntax, a zone abbreviation can be a string of letters, such as
EST, or an arbitrary string surrounded by angle brackets, such as <UTC-05>.
Note that the zone abbreviations given here are only used for output, and
even then only in some timestamp output formats."
So, the definition of "STD" describes this - it's arguable whether adding
the <> to the syntax diagram would be an improvement.
David J.
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2021-05-13 21:12 UTC | newest]
Thread overview: 5+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-05-13 19:38 Where is the syntax "<-08>+08" documented—comes from "select current_setting('timezone')" Bryn Llewellyn <[email protected]>
2021-05-13 20:09 ` David G. Johnston <[email protected]>
2021-05-13 20:34 ` Bryn Llewellyn <[email protected]>
2021-05-13 20:56 ` David G. Johnston <[email protected]>
2021-05-13 21:12 ` David G. Johnston <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox