Re: jsonpath: Inconsistency of timestamp_tz() Output

> On Jul 8, 2024, at 21:44, Junwang Zhao <zhjwpku(at)gmail(dot)com> wrote:
>
> # 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.

Right, which now that I think about it seems odd, because timestamptz does not actually store an offset. As you say, it converts the time to UTC and stores only that, then displays the offset relative to the current time zone setting.

So in plain SQL it always displays relative to the current TZ offset:

david=# set time zone 'America/New_York';
SET
david=# select '2023-08-15 12:34:56-05'::timestamptz;
timestamptz
------------------------
2023-08-15 13:34:56-04
(1 row)

david=# select '2023-08-15 12:34:56'::timestamptz;
timestamptz
------------------------
2023-08-15 12:34:56-04
(1 row)

In jsopath expressions, however, it does not, as your example demonstrates:

david=# 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"

How is it retaining the offset? Should it?

The display is properly adjusted when using string():

david=# select jsonb_path_query_tz('"2024-08-15 12:34:56-05"', '$.timestamp_tz().string()');
jsonb_path_query_tz
--------------------------
"2024-08-15 13:34:56-04"
(1 row)

So perhaps I had things reversed before. Maybe it’s actually doing the right then when it converts a timestamp to a timestamptz, but not when it the input contains an offset, as in your example.

D