On Sep 11, 2024, at 11:11, Tom Lane <[email protected]> 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
