Re: jsonpath: Inconsistency of timestamp_tz() Output - Mailing list pgsql-hackers
From | Junwang Zhao |
---|---|
Subject | Re: jsonpath: Inconsistency of timestamp_tz() Output |
Date | |
Msg-id | CAEG8a3J2rBZ-S2VpwJCsWoLSmdSqWfnd0QH9kO_YE4ZJFSqGXQ@mail.gmail.com Whole thread Raw |
In response to | jsonpath: Inconsistency of timestamp_tz() Output ("David E. Wheeler" <david@justatheory.com>) |
Responses |
Re: jsonpath: Inconsistency of timestamp_tz() Output
|
List | pgsql-hackers |
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
pgsql-hackers by date: