Re: Document DateStyle effect on jsonpath string()

On Sep 11, 2024, at 11:11, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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=# set timezone = 'America/New_York';
SET
david=# select jsonb_path_query('"2023-08-15 12:34:56-09"', '$.timestamp_tz().string()');
jsonb_path_query
--------------------------
"2023-08-15 17:34:56-04"

david=# set timezone = 'America/Los_Angeles';
SET
david=# select jsonb_path_query('"2023-08-15 12:34:56-09"', '$.timestamp_tz().string()');
jsonb_path_query
--------------------------
"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