Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lhHAK-0002uF-Kh for pgsql-docs@arkaria.postgresql.org; Thu, 13 May 2021 19:38:36 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1lhHAI-00034p-HN for pgsql-docs@arkaria.postgresql.org; Thu, 13 May 2021 19:38:34 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lhHAI-00034h-97 for pgsql-docs@lists.postgresql.org; Thu, 13 May 2021 19:38:34 +0000 Received: from mail-pg1-x532.google.com ([2607:f8b0:4864:20::532]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1lhHAG-0008AC-4J for pgsql-docs@lists.postgresql.org; Thu, 13 May 2021 19:38:33 +0000 Received: by mail-pg1-x532.google.com with SMTP id t193so9848815pgb.4 for ; Thu, 13 May 2021 12:38:31 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yugabyte.com; s=google; h=from:mime-version:subject:message-id:date:to; bh=Ey+tF89T6VwppShSkSCH1ch8OxOVd1oR7T6zz7eBAtY=; b=hhQSKd/O3LRm2YSXlkfQasHthPwCSvV6hnxdI/QNWFr2M/3BkO5ThyC2s/cqG48Lj7 9u9g0kzZAk6kzJJE4jYpI+St3TfegOMLFe2Y1CA4MgTp9RiP/B2VG5E/vXoA6b2p4DZK NIAhg5seG3zinhSY7HZFyD0sdK+iY2Mmax6Tw= X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:from:mime-version:subject:message-id:date:to; bh=Ey+tF89T6VwppShSkSCH1ch8OxOVd1oR7T6zz7eBAtY=; b=Ij2ylAAuHmuDBGMIC5XTksKY6/fO1eRKnn3u1ioY3OP29BGWVgDYSyT7idQsxKV8p4 EotNBS7sFaBDZQ8YBg0TErkJsDR4TxqTYaPeU3JX8VjJgaFoUMyV6HzT0MlUd6jgWswI vUbRxmNpT2bZhkK/tarokm5cvTi+0yrEyzaVydb3tkuqWVe9WIC6O4BTRlzhMmK7H04V Q9HTpvSmrr0c35tLKU4XblOEN0nhoNjCMIhHk7qVwuvYAWYX+lx2Qp/HLxNEPMEkT1xr tx3U/HhTceOHZoH2degJSt30klB/TETGv+tzy1WgCA3SQX9rFlWNeERO6Jz0XblOe0PA OfFQ== X-Gm-Message-State: AOAM533AgBFnBG8WW2+gAw9kmWQB+njxx/boSqyaNfZ1OZe6k536VynK jhFWE6E5qzp973xmH570ATARz5AGej0jLA== X-Google-Smtp-Source: ABdhPJy4qkUJm6ouf2C3zJtzvOo+8ESgvxo0UC2OfxpdPWCIFSQi67evJgLOanIWJ9JpcEC4oEH1Iw== X-Received: by 2002:a63:ab05:: with SMTP id p5mr42622734pgf.149.1620934709373; Thu, 13 May 2021 12:38:29 -0700 (PDT) Received: from smtpclient.apple ([2601:646:101:7fda:886c:e99c:eade:479c]) by smtp.gmail.com with ESMTPSA id x35sm2551696pfu.209.2021.05.13.12.38.28 for (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Thu, 13 May 2021 12:38:29 -0700 (PDT) From: Bryn Llewellyn Content-Type: multipart/alternative; boundary="Apple-Mail=_DA4CD0F7-77D7-4041-8201-837855AD7E13" Mime-Version: 1.0 (Mac OS X Mail 14.0 \(3654.80.0.2.43\)) Subject: =?utf-8?Q?Where_is_the_syntax_=22=3C-08=3E+08=22_documented?= =?utf-8?Q?=E2=80=94comes_from_=22select_current=5Fsetting=28=27timezone?= =?utf-8?Q?=27=29=22?= Message-Id: <88C5909B-A649-4E0C-9037-19CCAED9944A@yugabyte.com> Date: Thu, 13 May 2021 12:38:27 -0700 To: Pg Docs X-Mailer: Apple Mail (2.3654.80.0.2.43) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --Apple-Mail=_DA4CD0F7-77D7-4041-8201-837855AD7E13 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 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=E2=80=99s some examples that produce the strangely formatted = answer: set timezone =3D -8; -- no quotes show timezone; or: set timezone =3D '-8'; -- notice the quotes show timezone; or: set timezone =3D interval '-8 hours'; show timezone; or even: set timezone =3D '<-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 =3D '+08'; show timezone; I get an upper-cased rendition of what I said: "+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 =3D '-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: =C2=AB POSIX has positive signs west of Greenwich, but many people = expect positive signs east of Greenwich. =C2=BB Is there a setting that I can do to make "set timezone =3D '-08:00'" = treat it as a request to set the negative value that I said? --Apple-Mail=_DA4CD0F7-77D7-4041-8201-837855AD7E13 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8
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=E2=80=99s = some examples that produce the strangely formatted answer:

set timezone =3D -8; -- = no quotes
show timezone;

or:

set timezone =3D '-8'; -- notice the = quotes
show timezone;

or:

set timezone =3D = interval '-8 hours';
show timezone;

or even:

set = timezone =3D '<-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 =3D '<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 =3D '-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:

=C2=AB POSIX has = positive signs west of Greenwich, but many people expect positive = signs east of Greenwich. =C2=BB

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

= --Apple-Mail=_DA4CD0F7-77D7-4041-8201-837855AD7E13--