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?
