> On Jul 8, 2024, at 21:44, Junwang Zhao <zhjw...@gmail.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@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
