public inbox for [email protected]  

help / color / mirror / Atom feed

From: Bryn Llewellyn <[email protected]>
To: Pg Docs <[email protected]>
Subject: Where is the syntax "<-08>+08" documented—comes from "select current_setting('timezone')"
Date: Thu, 13 May 2021 12:38:27 -0700
Message-ID: <[email protected]> (raw)

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?

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: Where is the syntax "<-08>+08" documented—comes from "select current_setting('timezone')"
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox