Hackers,
There’s an odd difference in the behavior of timestamp_tz() outputs. Running
with America/New_York as my TZ, it looks fine for a full timestamptz, identical
to how casting the types directly works:
david=# set time zone 'America/New_York';
SET
david=# select '2024-08-15 12:34:56-04'::timestamptz;
timestamptz
------------------------
2024-08-15 12:34:56-04
(1 row)
david=# select jsonb_path_query_tz('"2024-08-15 12:34:56-04"',
'$.timestamp_tz()');
jsonb_path_query_tz
-----------------------------
"2024-08-15T12:34:56-04:00"
Both show the time in America/New_York, which is great. But when casting from a
date, the behavior differs. Casting directly:
david=# select '2024-08-15'::date::timestamptz;
timestamptz
------------------------
2024-08-15 00:00:00-04
It stringifies to the current zone setting again, as expected. But look at the
output from a path query:
david=# select jsonb_path_query_tz('"2023-08-15"', '$.timestamp_tz()');
jsonb_path_query_tz
-----------------------------
"2023-08-15T04:00:00+00:00"
It’s using UTC for the display output! Shouldn’t it be using America/New_York?
Note that I’m comparing a cast from date to timestamptz because that’s how the
jsonpath parsing works[1]: it ultimately uses
date2timestamptz_opt_overflow()[2] to make the conversion, which appears to set
the offset from the time zone GUC, so I’m not sure where it’s converted to UTC
before stringifying.
Maybe an argument is missing from the stringification path?
FWIW, explicitly calling the string() jsonpath method does produce a result in
the current TZ:
david=# select jsonb_path_query_tz('"2023-08-15"', '$.timestamp_tz().string()');
jsonb_path_query_tz
--------------------------
"2023-08-15 00:00:00-04"
That bit uses timestamptz_out to format the output, but JSONB has its own
stringification[4] (called here[5]), but I can’t tell what might be different
between a timestamptz cast from a date and one not cast from a date.
Note the same divergency in behavior occurs when the source value is a
timestamp, too. Compare:
david=# select '2024-08-15 12:34:56'::timestamp::timestamptz;
timestamptz
------------------------
2024-08-15 12:34:56-04
(1 row)
david=# select jsonb_path_query_tz('"2023-08-15 12:34:56"', '$.timestamp_tz()');
jsonb_path_query_tz
-----------------------------
"2023-08-15T16:34:56+00:00"
(1 row)
Anyway, should the output of timestamptz JSONB values be made more consistent?
I’m happy to make a patch to do so, but could use a hand figuring out where the
behavior varies.
Best,
David
[1]:
https://github.com/postgres/postgres/blob/3497c87/src/backend/utils/adt/jsonpath_exec.c#L2708-L2718
[2]:
https://github.com/postgres/postgres/blob/3497c87/src/backend/utils/adt/date.c#L613-L698
[3]:
https://github.com/postgres/postgres/blob/3fb59e789dd9f21610101d1ec106ad58095e24f3/src/backend/utils/adt/jsonpath_exec.c#L1650-L1653
[4]:
https://github.com/postgres/postgres/blob/3fb59e789dd9f21610101d1ec106ad58095e24f3/src/backend/utils/adt/json.c#L369-L407
[5]:
https://github.com/postgres/postgres/blob/3fb59e7/src/backend/utils/adt/jsonb.c#L743-L748
