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?