Content-Type: text/plain;
	charset=utf-8
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3774.600.62\))
Subject: Re: jsonpath: Inconsistency of timestamp_tz() Output
From: "David E. Wheeler" <david@justatheory.com>
In-Reply-To: <CAEG8a3+QHYaLk+g8_LDnpbPMEC-Ht7-mOV2OsvTqqCmbWWJhew@mail.gmail.com>
Date: Tue, 9 Jul 2024 11:38:02 -0400
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Content-Transfer-Encoding: quoted-printable
Message-Id: <BB334A4C-9856-4D3F-9B21-4EFD4E9811FC@justatheory.com>
References: <7DE080CE-6D8C-4794-9BD1-7D9699172FAB@justatheory.com>
 <CAEG8a3J2rBZ-S2VpwJCsWoLSmdSqWfnd0QH9kO_YE4ZJFSqGXQ@mail.gmail.com>
 <53E4B67F-70FF-441E-A48B-DE520966A312@justatheory.com>
 <B086E009-9D3F-422D-B281-E83FF2A548CB@justatheory.com>
 <CAEG8a3+QHYaLk+g8_LDnpbPMEC-Ht7-mOV2OsvTqqCmbWWJhew@mail.gmail.com>
To: Junwang Zhao <zhjwpku@gmail.com>
Archived-At: <https://www.postgresql.org/message-id/BB334A4C-9856-4D3F-9B21-4EFD4E9811FC%40justatheory.com>
Precedence: bulk

On Jul 9, 2024, at 11:08, Junwang Zhao <zhjwpku@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
>=20
> struct
> {
> Datum value;
> Oid typid;
> int32 typmod;
> int tz; /* Numeric time zone, in seconds, for
>          * TimestampTz data type */
> } datetime;

Oooh, okay, so it=E2=80=99s a jsonb variant of the type. Interesting. =
Ah, and it=E2=80=99s assigned here[1]:

  jb->val.datetime.tz =3D 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) =3D=3D=
 0) {
+							tz =3D =
DetermineTimeZoneOffset(tm, session_timezone);
+						}
						value =3D =
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) =3D=3D=
 0) {
+							tz =3D =
DetermineTimeZoneOffset(tm, session_timezone);
+						}
						value =3D =
DirectFunctionCall1(timestamp_timestamptz,
										=
			value);
						break;
```

Only, you know, doesn=E2=80=99t crash the server.

Best,

David


[1]: =
https://github.com/postgres/postgres/blob/629520be5f9da9d0192c7f6c8796bfdd=
b4746760/src/backend/utils/adt/jsonpath_exec.c#L2784