On Tue, Jul 9, 2024 at 11:38 PM David E. Wheeler <da...@justatheory.com> wrote: > > On Jul 9, 2024, at 11:08, Junwang Zhao <zhjw...@gmail.com> wrote: > > > In JsonbValue.val.datatime, there is a tz field, I think that's where > > the offset stored, it is 18000 in the first example > > > > struct > > { > > Datum value; > > Oid typid; > > int32 typmod; > > int tz; /* Numeric time zone, in seconds, for > > * TimestampTz data type */ > > } datetime; > > Oooh, okay, so it’s a jsonb variant of the type. Interesting. Ah, and it’s > assigned here[1]: > > jb->val.datetime.tz = tz; > > It seems like JSONB timestamptz values want to display the recorded time > zone, so I suspect we need to set it when the converting from a non-tz to a > local tz setting, something like this: > > ``` patch > diff --git a/src/backend/utils/adt/jsonpath_exec.c > b/src/backend/utils/adt/jsonpath_exec.c > index d79c929822..f63b3b9330 100644 > --- a/src/backend/utils/adt/jsonpath_exec.c > +++ b/src/backend/utils/adt/jsonpath_exec.c > @@ -2707,12 +2707,16 @@ executeDateTimeMethod(JsonPathExecContext *cxt, > JsonPathItem *jsp, > break; > case jpiTimestampTz: > { > + struct pg_tm *tm; > /* Convert result type to timestamp with time > zone */ > switch (typid) > { > case DATEOID: > > checkTimezoneIsUsedForCast(cxt->useTz, > > "date", "timestamptz"); > + if > (timestamp2tm(DatumGetTimestamp(value), NULL, tm, NULL, NULL, NULL) == 0) { > + tz = > DetermineTimeZoneOffset(tm, session_timezone); > + } > value = > DirectFunctionCall1(date_timestamptz, > > value); > break; > @@ -2726,6 +2730,9 @@ executeDateTimeMethod(JsonPathExecContext *cxt, > JsonPathItem *jsp, > case TIMESTAMPOID: > > checkTimezoneIsUsedForCast(cxt->useTz, > > "timestamp", "timestamptz"); > + if > (timestamp2tm(DatumGetTimestamp(value), NULL, tm, NULL, NULL, NULL) == 0) { > + tz = > DetermineTimeZoneOffset(tm, session_timezone); > + } > value = > DirectFunctionCall1(timestamp_timestamptz, > > value); > break; > ``` > > Only, you know, doesn’t crash the server.
I apply your patch with some minor change(to make the server not crash): diff --git a/src/backend/utils/adt/jsonpath_exec.c b/src/backend/utils/adt/jsonpath_exec.c index d79c9298227..87a695ef633 100644 --- a/src/backend/utils/adt/jsonpath_exec.c +++ b/src/backend/utils/adt/jsonpath_exec.c @@ -2708,6 +2708,8 @@ executeDateTimeMethod(JsonPathExecContext *cxt, JsonPathItem *jsp, case jpiTimestampTz: { /* Convert result type to timestamp with time zone */ + struct pg_tm tm; + fsec_t fsec; switch (typid) { case DATEOID: @@ -2726,6 +2728,9 @@ executeDateTimeMethod(JsonPathExecContext *cxt, JsonPathItem *jsp, case TIMESTAMPOID: checkTimezoneIsUsedForCast(cxt->useTz, "timestamp", "timestamptz"); + if (timestamp2tm(DatumGetTimestamp(value), NULL, &tm, &fsec, NULL, NULL) == 0) { + tz = DetermineTimeZoneOffset(&tm, session_timezone); + } value = DirectFunctionCall1(timestamp_timestamptz, value); break; It now gives the local tz: [local] postgres@postgres:5432-54960=# set time zone 'America/New_York'; SET Time: 2.894 ms [local] postgres@postgres:5432-54960=# select jsonb_path_query_tz('"2024-08-15 12:34:56"', '$.timestamp_tz()'); ┌─────────────────────────────┐ │ jsonb_path_query_tz │ ├─────────────────────────────┤ │ "2024-08-15T12:34:56-04:00" │ └─────────────────────────────┘ (1 row) Time: 293813.022 ms (04:53.813) I'm not sure whether the SQL/JSON standard mentioned this, I searched a little bit, but found no clue :( > > Best, > > David > > > [1]: > https://github.com/postgres/postgres/blob/629520be5f9da9d0192c7f6c8796bfddb4746760/src/backend/utils/adt/jsonpath_exec.c#L2784 > > -- Regards Junwang Zhao