From: "David E. Wheeler" <david@justatheory.com>
Message-Id: <2AB9BBC7-65BE-42B6-827E-A99859BDB1D3@justatheory.com>
Content-Type: multipart/mixed;
	boundary="Apple-Mail=_98B25F03-731C-41E3-B651-593A127424A8"
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3774.600.62\))
Subject: Re: jsonpath: Inconsistency of timestamp_tz() Output
Date: Wed, 10 Jul 2024 10:33:05 -0400
In-Reply-To: <CAEG8a3LB530nDGiwV+xTchGtGZ2ZE79WX_oKvFKKTxbDDeZScw@mail.gmail.com>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
To: Junwang Zhao <zhjwpku@gmail.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>
 <BB334A4C-9856-4D3F-9B21-4EFD4E9811FC@justatheory.com>
 <CAEG8a3LB530nDGiwV+xTchGtGZ2ZE79WX_oKvFKKTxbDDeZScw@mail.gmail.com>
Archived-At: <https://www.postgresql.org/message-id/2AB9BBC7-65BE-42B6-827E-A99859BDB1D3%40justatheory.com>
Precedence: bulk


--Apple-Mail=_98B25F03-731C-41E3-B651-593A127424A8
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain;
	charset=utf-8

On Jul 10, 2024, at 01:48, Junwang Zhao <zhjwpku@gmail.com> wrote:

> I apply your patch with some minor change(to make the server not =
crash):

Oh, thank you! Kicking myself for not catching the obvious.

> It now gives the local tz:
>=20
> [local] postgres@postgres:5432-54960=3D# set time zone =
'America/New_York';
> SET
> Time: 2.894 ms
> [local] postgres@postgres:5432-54960=3D# select
> jsonb_path_query_tz('"2024-08-15 12:34:56"', '$.timestamp_tz()');
> =
=E2=94=8C=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=
=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=
=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=
=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=90
> =E2=94=82     jsonb_path_query_tz     =E2=94=82
> =
=E2=94=9C=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=
=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=
=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=
=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=A4
> =E2=94=82 "2024-08-15T12:34:56-04:00" =E2=94=82
> =
=E2=94=94=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=
=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=
=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=
=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=98
> (1 row)
>=20
> Time: 293813.022 ms (04:53.813)

Yes, and I think that=E2=80=99s what we want, since it preserves and =
displays the offset for strings that contain them:

    david=3D# set time zone 'America/New_York';
    SET
    david=3D# select jsonb_path_query_tz('"2024-08-15 12:34:56+10"', =
'$.timestamp_tz()');
        jsonb_path_query_tz
    -----------------------------
    "2024-08-15T12:34:56+10:00"

> I'm not sure whether the SQL/JSON standard mentioned this, I searched =
a
> little bit, but found no clue :(

Yeah I don=E2=80=99t know either, but now at least it=E2=80=99s =
consistent. I=E2=80=99ve attached a patch to fix it.

Ideally, I think, we wouldn=E2=80=99t convert the value and determine =
the offset twice, but teach date_timestamptz and timestamp_timestamptz =
(or date2timestamptz and timestamp2timestamptz?) how to return the =
offset, or create alternate functions that do so. Not sure what calling =
style should be adopted here, but this at least addresses the issue. =
Happy to resubmit something more efficient upon function design =
feedback.

Best,

David


--Apple-Mail=_98B25F03-731C-41E3-B651-593A127424A8
Content-Disposition: attachment;
	filename=v1-0001-Preserve-tz-when-converting-to-jsonb-timestamptz.patch
Content-Type: application/octet-stream;
	x-unix-mode=0644;
	name="v1-0001-Preserve-tz-when-converting-to-jsonb-timestamptz.patch"
Content-Transfer-Encoding: quoted-printable

=46rom=2031fea30552dc4e6a05c969adf61cdb010cfb9fbe=20Mon=20Sep=2017=20=
00:00:00=202001=0AFrom:=20"David=20E.=20Wheeler"=20=
<david@justatheory.com>=0ADate:=20Wed,=2010=20Jul=202024=2010:23:24=20=
-0400=0ASubject:=20[PATCH=20v1]=20Preserve=20tz=20when=20converting=20to=20=
jsonb=20timestamptz=0A=0AThe=20JSONB=20jbvDatetime=20type=20has=20a=20=
field=20for=20offset,=20and=20displays=20the=20time=0Ain=20that=20=
offset.=20For=20example,=20when=20the=20time=20zone=20GUC=20is=20set=20=
to=0AAmerica/New_York,=20the=20jsonpath=20`timestamp_tz()`=20method=20=
returns=20a=20value=0Athat=20displays=20a=20parsed=20value=20with=20its=20=
offset,=20not=20the=20local=20offset:=0A=0A=20=20=20=20david=3D#=20set=20=
time=20zone=20'America/New_York';=0A=20=20=20=20SET=0A=20=20=20=20=
david=3D#=20select=20jsonb_path_query_tz('"2024-08-15=2012:34:56+10"',=20=
'$.timestamp_tz()');=0A=20=20=20=20=20=20=20=20jsonb_path_query_tz=0A=20=20=
=20=20-----------------------------=0A=20=20=20=20=
"2024-08-15T12:34:56+10:00"=0A=0AThis=20was=20not=20true=20for=20values=20=
parsed=20by=20`timestamp_tz()`=20that=20lacked=20an=0Aoffset.=20It=20=
correctly=20assumes=20the=20local=20time=20zone,=20but=20displays=20it=20=
in=0AUTC:=0A=0A=20=20=20=20david=3D#=20select=20=
jsonb_path_query_tz('"2024-08-15=2012:34:56"',=20'$.timestamp_tz()');=0A=20=
=20=20=20=20=20=20=20jsonb_path_query_tz=0A=20=20=20=20=
-----------------------------=0A=20=20=20=20"2024-08-15T16:34:56+00:00"=0A=
=0ATo=20fix=20this=20inconsistent=20behavior,=20determine=20the=20offset=20=
for=20values=20being=0Acast=20from=20`DATEOID`=20and=20`DATEOID`=20types=20=
to=20`jpiTimestampTz`=20and=20store=20it=0Ain=20the=20resulting=20=
jbvDatetime=20value.=20With=20this=20change,=20the=20result=20now=0A=
preserves=20the=20offset=20just=20as=20it=20does=20when=20converting=20=
from=20offset-aware=0Avalues:=0A=0A=20=20=20=20david=3D#=20select=20=
jsonb_path_query_tz('"2024-08-15=2012:34:56"',=20'$.timestamp_tz()');=0A=20=
=20=20=20=20=20=20=20jsonb_path_query_tz=0A=20=20=20=20=
-----------------------------=0A=20=20=20=20"2023-08-15T12:34:56-04:00"=0A=
=0AAuthor:=20David=20Wheeler=0AReviewed-by:=20Junwang=20Zhao=0A=
Discussion:=20=
https://postgr.es/m/7DE080CE-6D8C-4794-9BD1-7D9699172FAB%40justatheory.com=
=0A---=0A=20src/backend/utils/adt/jsonpath_exec.c=20=20=20=20=20=20=20=20=
|=208=20++++++++=0A=20src/test/regress/expected/jsonb_jsonpath.out=20|=20=
4=20++--=0A=202=20files=20changed,=2010=20insertions(+),=202=20=
deletions(-)=0A=0Adiff=20--git=20a/src/backend/utils/adt/jsonpath_exec.c=20=
b/src/backend/utils/adt/jsonpath_exec.c=0Aindex=20d79c929822..f7d255746b=20=
100644=0A---=20a/src/backend/utils/adt/jsonpath_exec.c=0A+++=20=
b/src/backend/utils/adt/jsonpath_exec.c=0A@@=20-2707,12=20+2707,17=20@@=20=
executeDateTimeMethod(JsonPathExecContext=20*cxt,=20JsonPathItem=20*jsp,=0A=
=20=09=09=09break;=0A=20=09=09case=20jpiTimestampTz:=0A=20=09=09=09{=0A+=09=
=09=09=09struct=20pg_tm=20tm;=0A+=09=09=09=09fsec_t=09=09fsec;=0A=20=09=09=
=09=09/*=20Convert=20result=20type=20to=20timestamp=20with=20time=20zone=20=
*/=0A=20=09=09=09=09switch=20(typid)=0A=20=09=09=09=09{=0A=20=09=09=09=09=
=09case=20DATEOID:=0A=20=09=09=09=09=09=09=
checkTimezoneIsUsedForCast(cxt->useTz,=0A=20=09=09=09=09=09=09=09=09=09=09=
=09=09=20=20=20"date",=20"timestamptz");=0A+=09=09=09=09=09=09if=20=
(timestamp2tm(DatumGetTimestamp(value),=20NULL,=20&tm,=20&fsec,=20NULL,=20=
NULL)=20=3D=3D=200)=20{=0A+=09=09=09=09=09=09=09tz=20=3D=20=
DetermineTimeZoneOffset(&tm,=20session_timezone);=0A+=09=09=09=09=09=09}=0A=
=20=09=09=09=09=09=09value=20=3D=20DirectFunctionCall1(date_timestamptz,=0A=
=20=09=09=09=09=09=09=09=09=09=09=09=09=09value);=0A=20=09=09=09=09=09=09=
break;=0A@@=20-2726,6=20+2731,9=20@@=20=
executeDateTimeMethod(JsonPathExecContext=20*cxt,=20JsonPathItem=20*jsp,=0A=
=20=09=09=09=09=09case=20TIMESTAMPOID:=0A=20=09=09=09=09=09=09=
checkTimezoneIsUsedForCast(cxt->useTz,=0A=20=09=09=09=09=09=09=09=09=09=09=
=09=09=20=20=20"timestamp",=20"timestamptz");=0A+=09=09=09=09=09=09if=20=
(timestamp2tm(DatumGetTimestamp(value),=20NULL,=20&tm,=20&fsec,=20NULL,=20=
NULL)=20=3D=3D=200)=20{=0A+=09=09=09=09=09=09=09tz=20=3D=20=
DetermineTimeZoneOffset(&tm,=20session_timezone);=0A+=09=09=09=09=09=09}=0A=
=20=09=09=09=09=09=09value=20=3D=20=
DirectFunctionCall1(timestamp_timestamptz,=0A=20=09=09=09=09=09=09=09=09=09=
=09=09=09=09value);=0A=20=09=09=09=09=09=09break;=0Adiff=20--git=20=
a/src/test/regress/expected/jsonb_jsonpath.out=20=
b/src/test/regress/expected/jsonb_jsonpath.out=0Aindex=20=
a6112e86fa..62cc25f159=20100644=0A---=20=
a/src/test/regress/expected/jsonb_jsonpath.out=0A+++=20=
b/src/test/regress/expected/jsonb_jsonpath.out=0A@@=20-2914,7=20+2914,7=20=
@@=20HINT:=20=20Use=20*_tz()=20function=20for=20time=20zone=20support.=0A=
=20select=20jsonb_path_query_tz('"2023-08-15"',=20'$.timestamp_tz()');=20=
--=20should=20work=0A=20=20=20=20=20=20jsonb_path_query_tz=20=20=20=20=20=
=0A=20-----------------------------=0A-=20"2023-08-15T07:00:00+00:00"=0A=
+=20"2023-08-14T23:00:00-08:00"=0A=20(1=20row)=0A=20=0A=20select=20=
jsonb_path_query('"12:34:56"',=20'$.timestamp_tz()');=0A@@=20-3101,7=20=
+3101,7=20@@=20HINT:=20=20Use=20*_tz()=20function=20for=20time=20zone=20=
support.=0A=20select=20jsonb_path_query_tz('"2023-08-15=2012:34:56"',=20=
'$.timestamp_tz()');=20--=20should=20work=0A=20=20=20=20=20=20=
jsonb_path_query_tz=20=20=20=20=20=0A=20-----------------------------=0A=
-=20"2023-08-15T02:34:56+00:00"=0A+=20"2023-08-15T12:34:56+10:00"=0A=20=
(1=20row)=0A=20=0A=20select=20jsonb_path_query('"2023-08-15=2012:34:56=20=
+05:30"',=20'$.timestamp_tz()');=0A--=20=0A2.45.2=0A=0A=

--Apple-Mail=_98B25F03-731C-41E3-B651-593A127424A8
Content-Transfer-Encoding: 7bit
Content-Type: text/plain;
	charset=us-ascii


--Apple-Mail=_98B25F03-731C-41E3-B651-593A127424A8--