Re: jsonpath: Inconsistency of timestamp_tz() Output

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

# 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(at)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 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
>
>
>

--
Regards
Junwang Zhao