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 1lhI2B-00054y-QC for pgsql-docs@arkaria.postgresql.org; Thu, 13 May 2021 20:34:15 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1lhI2A-0007sf-Im for pgsql-docs@arkaria.postgresql.org; Thu, 13 May 2021 20:34:14 +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 1lhI2A-0007sW-AS for pgsql-docs@lists.postgresql.org; Thu, 13 May 2021 20:34:14 +0000 Received: from mail-pf1-x435.google.com ([2607:f8b0:4864:20::435]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1lhI27-0000Ea-DN for pgsql-docs@lists.postgresql.org; Thu, 13 May 2021 20:34:13 +0000 Received: by mail-pf1-x435.google.com with SMTP id d16so2093631pfn.12 for ; Thu, 13 May 2021 13:34:10 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yugabyte.com; s=google; h=from:message-id:mime-version:subject:date:in-reply-to:cc:to :references; bh=ffwnHsAuNMre+JM8gX6sqCf+204ZsWsvDUCWki6M7Uo=; b=Qo4aVLaeX9eDnpo6JRtSHcLr2F51LyYr8uvlug3hbxFTDsk01zsUA2VxarEDt0kLid OAEBX3LoIJwNdvbju9oX2PmeG1c9Qo6dsbZItxH5bm6icI5XOvCKTFiWL3n3gylDtOCD 7dUcKoH25ZEg3lDsa0t/FhIXJwb+lDDx92QWU= X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:from:message-id:mime-version:subject:date :in-reply-to:cc:to:references; bh=ffwnHsAuNMre+JM8gX6sqCf+204ZsWsvDUCWki6M7Uo=; b=enuqDi0qs3VMaXP+6bnE/erKCVn9Uc+ebbx1NAOqGPvU7bUV+jU+Mb1mApgJfkw+Dx etRCTPNUVfi26mKN/DUguz8UuPpRUeYRHmiuyAnac1dNH8iNfbwJidtNuTVapPtrwZci lsLpN5rFW7flCHnnN69mHiuE+PAJ2NWX/Ilntg13FEMNbAp04TYoqajIcbYRa7xSEHqj KxHZTMvCrinK+60bojTztOrWJw8bkoAcZ8gtyRMJOCM8GI1aouC0Hclc+xaD6rdYf+aq sJesSu49goshr58XefA9BvovXZD6MI3wQADSHFYqz1YL0xfJ7+i3cZ1Vq4C+XJis69hV /79A== X-Gm-Message-State: AOAM532o1xYorc+NiobpTKN6wzLrnq1l03Vf5rntbMARG6J+CTJZb+mr 7MljXXc/gvMtnUrQwjpBfSlevw== X-Google-Smtp-Source: ABdhPJzcrAH/hvpjHSAhAVXY2MI/R99ll/TKC2b337xcdLHWbekxmvHimWTujwfWvKmmuM/KTCE/YQ== X-Received: by 2002:a62:4e10:0:b029:2cb:cf3b:d195 with SMTP id c16-20020a624e100000b02902cbcf3bd195mr13107544pfb.74.1620938048468; Thu, 13 May 2021 13:34:08 -0700 (PDT) Received: from smtpclient.apple ([2601:646:101:7fda:886c:e99c:eade:479c]) by smtp.gmail.com with ESMTPSA id s8sm2623250pfe.112.2021.05.13.13.34.07 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Thu, 13 May 2021 13:34:08 -0700 (PDT) From: Bryn Llewellyn Message-Id: <8A97A8C5-566D-4C4C-9A1F-816A49A64704@yugabyte.com> Content-Type: multipart/alternative; boundary="Apple-Mail=_9129A5CC-9CA8-4106-B7C9-0A02D63A5F57" Mime-Version: 1.0 (Mac OS X Mail 14.0 \(3654.80.0.2.43\)) Subject: =?utf-8?Q?Re=3A_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?= Date: Thu, 13 May 2021 13:34:06 -0700 In-Reply-To: Cc: Pg Docs To: "David G. Johnston" References: <88C5909B-A649-4E0C-9037-19CCAED9944A@yugabyte.com> 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=_9129A5CC-9CA8-4106-B7C9-0A02D63A5F57 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 > david.g.johnston@gmail.com wrote: >=20 > On Thu, May 13, 2021 at 12:38 PM Bryn Llewellyn > wrote: > =C2=AB POSIX has positive signs west of Greenwich, but many people = expect positive signs east of Greenwich. =C2=BB >=20 > 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? >=20 > That ultimately comes from the appendix. >=20 > = https://www.postgresql.org/docs/current/datetime-posix-timezone-specs.html= = >=20 > As noted here: >=20 > = https://www.postgresql.org/docs/current/datatype-datetime.html#DATATYPE-TI= MEZONES = >=20 > 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. >=20 > 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 =C2=AB Where is = the syntax "<-08>+08" documented? =C2=BB 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. --Apple-Mail=_9129A5CC-9CA8-4106-B7C9-0A02D63A5F57 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8
david.g.johnston@gmail.com wrote:

On Thu, May 13, 2021 at 12:38 PM Bryn = Llewellyn <bryn@yugabyte.com> wrote:
=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?

That ultimately comes from the appendix.
https://www.postgresql.org/docs/current/datetime-posix-timezone-s= pecs.html

As = noted here:

https://www.postgresql.org/docs/current/datatype-datetime.html#DA= TATYPE-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.
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 =C2=AB Where is the syntax "<-08>+08" documented? =C2=BB= 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.


= --Apple-Mail=_9129A5CC-9CA8-4106-B7C9-0A02D63A5F57--