Re: jsonpath: Inconsistency of timestamp_tz() Output

On Mon, Jul 1, 2024 at 11:02 PM David E. Wheeler <david@justatheory.com> wrote:
>
> 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"

# select jsonb_path_query_tz('"2024-08-15 12:34:56-05"', '$.timestamp_tz()');

Do you also expect this to show the time in America/New_York?

This is what I get:

[local] postgres@postgres:5432-28176=# select
jsonb_path_query_tz('"2024-08-15 12:34:56-05"', '$.timestamp_tz()');
┌─────────────────────────────┐
│     jsonb_path_query_tz     │
├─────────────────────────────┤
│ "2024-08-15T12:34:56-05:00" │
└─────────────────────────────┘
(1 row)

The logic in executeDateTimeMethod seems to convert the input to a UTC
timestamp base on the session TZ,
the output seems not cast based on the TZ.

>
> 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
ultimatelyuses date2timestamptz_opt_overflow()[2] to make the conversion, which appears to set the offset from the time
zoneGUC, 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’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,
butcould 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
>
>
>


--
Regards
Junwang Zhao