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