jsonpath: Inconsistency of timestamp_tz() Output - Mailing list pgsql-hackers

Hackers,

There’s an odd difference in the behavior of timestamp_tz() outputs. Running with America/New_York as my TZ, it looks
finefor 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
usesdate2timestamptz_opt_overflow()[2] to make the conversion, which appears to set the offset from the time zone GUC,
soI’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’ttell 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
coulduse 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