Content-Type: text/plain;
	charset=us-ascii
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3776.700.51\))
Subject: Re: Document DateStyle effect on jsonpath string()
From: "David E. Wheeler" <david@justatheory.com>
In-Reply-To: <3824427.1726067484@sss.pgh.pa.us>
Date: Wed, 11 Sep 2024 11:52:31 -0400
Cc: Peter Eisentraut <peter@eisentraut.org>,
 jian he <jian.universality@gmail.com>,
 PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Content-Transfer-Encoding: quoted-printable
Message-Id: <5801E26B-E2AB-4BA3-8B69-EF8837389284@justatheory.com>
References: <56955B33-6959-4FDA-A459-F00363ECDFEE@justatheory.com>
 <CACJufxHREyTFzKOWz3f-yD14HxAdfeP1vGQQ2SQrusZ+ziRJyA@mail.gmail.com>
 <4D874C72-8939-4083-8336-AB114D9E29AD@justatheory.com>
 <CACJufxHE6q47-GEf3Kk0pD+Snr3yZMM4Byig2=bQKV8js7sP-A@mail.gmail.com>
 <B8E7081B-A8BE-4BAF-9C6B-AACD4F6EAB00@justatheory.com>
 <F492B399-37CB-471C-AEAD-25E1C19B607D@justatheory.com>
 <A556B643-F7E6-442D-9235-2D46A8A887C1@justatheory.com>
 <3541398.1725994308@sss.pgh.pa.us>
 <D6137AEA-0BE7-4328-9718-71B7E6BB4F6C@justatheory.com>
 <b847bc16-57b4-4701-870b-c4ed33d71e51@eisentraut.org>
 <3654686.1725999372@sss.pgh.pa.us>
 <5e8879d0-a3c8-4be2-950f-d83aa2af953a@eisentraut.org>
 <3811774.1726063865@sss.pgh.pa.us>
 <E443FB23-3BEE-448D-95E4-F10904A10E5D@justatheory.com>
 <3824427.1726067484@sss.pgh.pa.us>
To: Tom Lane <tgl@sss.pgh.pa.us>
Archived-At: <https://www.postgresql.org/message-id/5801E26B-E2AB-4BA3-8B69-EF8837389284%40justatheory.com>
Precedence: bulk

On Sep 11, 2024, at 11:11, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> What "let result be stringified" behavior are you thinking of,
> exactly?  AFAICS there's not sensitivity to timezone unless you
> use the _tz variant, otherwise it just regurgitates the input.

There is stringification of a time, date, or timestamp value, which has =
no TZ, but is still affected by DateStyle. Then there is stringification =
of timetz or timestamptz, which can be created by the .time_tz() and =
.timstamp_tz() functions, and therefore are impacted by both the =
DateStyle and TimeZone configs, even when not using the _tz variant:

david=3D# set timezone =3D 'America/New_York';
SET
david=3D# select jsonb_path_query('"2023-08-15 12:34:56-09"', =
'$.timestamp_tz().string()');
     jsonb_path_query    =20
--------------------------
 "2023-08-15 17:34:56-04"

david=3D# set timezone =3D 'America/Los_Angeles';
SET
david=3D# select jsonb_path_query('"2023-08-15 12:34:56-09"', =
'$.timestamp_tz().string()');
     jsonb_path_query    =20
--------------------------
 "2023-08-15 14:34:56-07"
(1 row)

> I agree that we should force ISO datestyle, but I'm not quite sure
> about whether we're in the clear with timezone handling.  We already
> had a bunch of specialized rules about timezone handling in the _tz
> and not-_tz variants of these functions.  It seems to me that simply
> forcing UTC would not be consistent with that pre-existing behavior.
> However, I may not have absorbed enough caffeine yet.

True, it would not be consistent with the existing behaviors, but I =
believe these are all new in Postgres 17.

Best,

David