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
thereis stringification of timetz or timestamptz, which can be created by the .time_tz() and .timstamp_tz() functions,
andtherefore 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
