Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sYnEj-00E6ag-2l for pgsql-hackers@arkaria.postgresql.org; Tue, 30 Jul 2024 13:49:57 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1sYnEh-00AHyR-Ms for pgsql-hackers@arkaria.postgresql.org; Tue, 30 Jul 2024 13:49:55 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sYnCU-00ADTb-LI for pgsql-hackers@lists.postgresql.org; Tue, 30 Jul 2024 13:47:38 +0000 Received: from pb-smtp2.pobox.com ([64.147.108.71]) by magus.postgresql.org with esmtps (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sYnCR-002FoJ-F2 for pgsql-hackers@lists.postgresql.org; Tue, 30 Jul 2024 13:47:38 +0000 Received: from pb-smtp2.pobox.com (unknown [127.0.0.1]) by pb-smtp2.pobox.com (Postfix) with ESMTP id 6023F2D7B4; Tue, 30 Jul 2024 09:47:34 -0400 (EDT) (envelope-from david@justatheory.com) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed; d=pobox.com; h=from :message-id:content-type:mime-version:subject:date:in-reply-to :cc:to:references; s=sasl; bh=P3MT8B4IPBo0kLsmDLFsIopsmgbCTQGSvR L3k2KqYAc=; b=WUN2BEPas73XZCVwsHJ451zwgkTF/XCKi/FzYhicmHfVsbvpye 5aV1Pn+III0KUvHj8dZd6NuOSr9787cWa7WVfohOf2sFKvS8nj8fsNe/SNfHwVb0 CRqXwb9inPpyS4InllWK3xMQTAMOl8OVkdZN0Z3cYmE5nMTeUrLgtjV3Y= Received: from pb-smtp2.nyi.icgroup.com (unknown [127.0.0.1]) by pb-smtp2.pobox.com (Postfix) with ESMTP id 588F12D7B3; Tue, 30 Jul 2024 09:47:34 -0400 (EDT) (envelope-from david@justatheory.com) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed; d=justatheory.com; h=from:message-id:content-type:mime-version:subject:date:in-reply-to:cc:to:references; s=2016-12.pbsmtp; bh=P3MT8B4IPBo0kLsmDLFsIopsmgbCTQGSvRL3k2KqYAc=; b=UosMUrzVW7LtX2pxEVW2HrdqFgJ7Qx7mAC9DxPw5+Lp63oEAlhaEaFFc7TT6Ixiey1h4AMbLVmk+lkHrUDKT8bvli4HJCtvwg/eSQp12pQuK+Vo/eiDDc/2+OqxZfTu82DaOxvrq2I/ZjqWBds52alE3qYhPD5IRt1Al1EJfolw= Received: from smtpclient.apple (unknown [158.222.197.125]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (No client certificate requested) by pb-smtp2.pobox.com (Postfix) with ESMTPSA id BD58F2D7B2; Tue, 30 Jul 2024 09:47:33 -0400 (EDT) (envelope-from david@justatheory.com) From: "David E. Wheeler" Message-Id: Content-Type: multipart/mixed; boundary="Apple-Mail=_72B8E175-A1A7-42E3-8D17-AF200A7EB69D" Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3774.600.62\)) Subject: Re: Document DateStyle effect on jsonpath string() Date: Tue, 30 Jul 2024 09:47:22 -0400 In-Reply-To: Cc: PostgreSQL Hackers To: jian he References: <56955B33-6959-4FDA-A459-F00363ECDFEE@justatheory.com> <4D874C72-8939-4083-8336-AB114D9E29AD@justatheory.com> X-Mailer: Apple Mail (2.3774.600.62) X-Pobox-Relay-ID: 45F3E4B4-4E7A-11EF-A86B-BAC1940A682E-76319746!pb-smtp2.pobox.com List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --Apple-Mail=_72B8E175-A1A7-42E3-8D17-AF200A7EB69D Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 On Jul 19, 2024, at 10:22, David E. Wheeler = wrote: >=20 > Here=E2=80=99s a rebase on 5784a49. I also updated the commitfest = item[1] to link to a new pull request[2], since I seem to have turned = the other one into the tz conversion bug fix. >=20 > [1]: https://commitfest.postgresql.org/49/5101/ > [2]: https://github.com/theory/postgres/pull/8 Rebase on 47c9803. I also changed the commitfest item[1] to =E2=80=9Cready= for committer=E2=80=9D, since jian reviewed it, though I couldn=E2=80=99t= see a way to add jian as a reviewer in the app. Hope that makes sense. Best, David --Apple-Mail=_72B8E175-A1A7-42E3-8D17-AF200A7EB69D Content-Disposition: attachment; filename=v4-0001-Document-impact-of-datestyle-on-jsonpath-string.patch Content-Type: application/octet-stream; x-unix-mode=0644; name="v4-0001-Document-impact-of-datestyle-on-jsonpath-string.patch" Content-Transfer-Encoding: quoted-printable =46rom=20524500cfc09851688f7714e690542632be57fd66=20Mon=20Sep=2017=20= 00:00:00=202001=0AFrom:=20"David=20E.=20Wheeler"=20= =0ADate:=20Fri,=2019=20Jul=202024=2010:19:46=20= -0400=0ASubject:=20[PATCH=20v4]=20Document=20impact=20of=20datestyle=20= on=20jsonpath=20string()=0A=0AAdd=20tests=20demonstrating=20the=20output=20= of=20the=20jsonpath=20`string()`=20method=20for=0Athe=20different=20date=20= and=20time=20data=20types,=20and=20how=20the=20`datestyle`=20GUC=0A= determines=20that=20output.=20Note=20this=20relationship=20in=20the=20= documentation=20for=0Athe=20`string()`=20method,=20as=20well.=0A---=0A=20= doc/src/sgml/func.sgml=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20|=20=204=20+-=0A=20= src/test/regress/expected/jsonb_jsonpath.out=20|=2089=20= ++++++++++++++++++++=0A=20src/test/regress/sql/jsonb_jsonpath.sql=20=20=20= =20=20=20|=2018=20++++=0A=203=20files=20changed,=20110=20insertions(+),=20= 1=20deletion(-)=0A=0Adiff=20--git=20a/doc/src/sgml/func.sgml=20= b/doc/src/sgml/func.sgml=0Aindex=20b39f97dc8d..efc60641df=20100644=0A---=20= a/doc/src/sgml/func.sgml=0A+++=20b/doc/src/sgml/func.sgml=0A@@=20= -17971,7=20+17971,9=20@@=20ERROR:=20=20jsonpath=20member=20accessor=20= can=20only=20be=20applied=20to=20an=20object=0A=20=20=20=20=20=20=20=20=20= string=0A=20=20=20=20= =20=20=20=20=0A=20=20=20=20=20=20=20=20=0A-=20=20=20=20=20=20= =20=20String=20value=20converted=20from=20a=20JSON=20boolean,=20number,=20= string,=20or=20datetime=0A+=20=20=20=20=20=20=20=20String=20value=20= converted=20from=20a=20JSON=20boolean,=20number,=20string,=20or=0A+=20=20= =20=20=20=20=20=20datetime.=20Note=20that=20the=20string=20output=20of=20= datetimes=20is=20determined=20by=0A+=20=20=20=20=20=20=20=20the=20=20parameter.=0A=20=20=20=20=20=20=20=20= =0A=20=20=20=20=20=20=20=20=0A=20=20=20=20=20=20=20=20=20= jsonb_path_query_array('[1.23,=20"xyz",=20false]',=20= '$[*].string()')=0Adiff=20--git=20= a/src/test/regress/expected/jsonb_jsonpath.out=20= b/src/test/regress/expected/jsonb_jsonpath.out=0Aindex=20= 02abaac689..8e1e45d758=20100644=0A---=20= a/src/test/regress/expected/jsonb_jsonpath.out=0A+++=20= b/src/test/regress/expected/jsonb_jsonpath.out=0A@@=20-2657,6=20+2657,95=20= @@=20select=20jsonb_path_query_array('[1.23,=20"yes",=20false]',=20= '$[*].string().type()');=0A=20=20["string",=20"string",=20"string"]=0A=20= (1=20row)=0A=20=0A+select=20jsonb_path_query('"2023-08-15=2012:34:56"',=20= '$.timestamp_tz().string()');=0A+ERROR:=20=20cannot=20convert=20value=20= from=20timestamp=20to=20timestamptz=20without=20time=20zone=20usage=0A= +HINT:=20=20Use=20*_tz()=20function=20for=20time=20zone=20support.=0A= +select=20jsonb_path_query_tz('"2023-08-15=2012:34:56"',=20= '$.timestamp_tz().string()');=20--=20should=20work=0A+=20=20=20=20=20=20= jsonb_path_query_tz=20=20=20=20=20=20=20=0A= +--------------------------------=0A+=20"Tue=20Aug=2015=2012:34:56=20= 2023=20PDT"=0A+(1=20row)=0A+=0A+select=20jsonb_path_query('"2023-08-15=20= 12:34:56=20+5:30"',=20'$.timestamp_tz().string()');=0A+=20=20=20=20=20=20= =20=20jsonb_path_query=20=20=20=20=20=20=20=20=0A= +--------------------------------=0A+=20"Tue=20Aug=2015=2000:04:56=20= 2023=20PDT"=0A+(1=20row)=0A+=0A+select=20jsonb_path_query('"2023-08-15=20= 12:34:56"',=20'$.timestamp().string()');=0A+=20=20=20=20=20=20= jsonb_path_query=20=20=20=20=20=20=0A+----------------------------=0A+=20= "Tue=20Aug=2015=2012:34:56=202023"=0A+(1=20row)=0A+=0A+select=20= jsonb_path_query('"12:34:56=20+5:30"',=20'$.time_tz().string()');=0A+=20= jsonb_path_query=20=0A+------------------=0A+=20"12:34:56+05:30"=0A+(1=20= row)=0A+=0A+select=20jsonb_path_query_tz('"12:34:56"',=20= '$.time_tz().string()');=20--=20should=20work=0A+=20jsonb_path_query_tz=20= =0A+---------------------=0A+=20"12:34:56-07"=0A+(1=20row)=0A+=0A+select=20= jsonb_path_query('"12:34:56"',=20'$.time().string()');=0A+=20= jsonb_path_query=20=0A+------------------=0A+=20"12:34:56"=0A+(1=20row)=0A= +=0A+select=20jsonb_path_query('"2023-08-15"',=20'$.date().string()');=0A= +=20jsonb_path_query=20=0A+------------------=0A+=20"08-15-2023"=0A+(1=20= row)=0A+=0A+set=20datestyle=20=3D=20'ISO';=0A+select=20= jsonb_path_query_tz('"2023-08-15=2012:34:56"',=20= '$.timestamp_tz().string()');=20--=20should=20work=0A+=20=20=20= jsonb_path_query_tz=20=20=20=20=0A+--------------------------=0A+=20= "2023-08-15=2012:34:56-07"=0A+(1=20row)=0A+=0A+select=20= jsonb_path_query('"2023-08-15=2012:34:56=20+5:30"',=20= '$.timestamp_tz().string()');=0A+=20=20=20=20=20jsonb_path_query=20=20=20= =20=20=0A+--------------------------=0A+=20"2023-08-15=2000:04:56-07"=0A= +(1=20row)=0A+=0A+select=20jsonb_path_query('"2023-08-15=2012:34:56"',=20= '$.timestamp().string()');=0A+=20=20=20jsonb_path_query=20=20=20=20=0A= +-----------------------=0A+=20"2023-08-15=2012:34:56"=0A+(1=20row)=0A+=0A= +select=20jsonb_path_query('"12:34:56=20+5:30"',=20= '$.time_tz().string()');=0A+=20jsonb_path_query=20=0A+------------------=0A= +=20"12:34:56+05:30"=0A+(1=20row)=0A+=0A+select=20= jsonb_path_query_tz('"12:34:56"',=20'$.time_tz().string()');=20--=20= should=20work=0A+=20jsonb_path_query_tz=20=0A+---------------------=0A+=20= "12:34:56-07"=0A+(1=20row)=0A+=0A+select=20= jsonb_path_query('"12:34:56"',=20'$.time().string()');=0A+=20= jsonb_path_query=20=0A+------------------=0A+=20"12:34:56"=0A+(1=20row)=0A= +=0A+select=20jsonb_path_query('"2023-08-15"',=20'$.date().string()');=0A= +=20jsonb_path_query=20=0A+------------------=0A+=20"2023-08-15"=0A+(1=20= row)=0A+=0A+reset=20datestyle;=0A=20--=20Test=20.time()=0A=20select=20= jsonb_path_query('null',=20'$.time()');=0A=20ERROR:=20=20jsonpath=20item=20= method=20.time()=20can=20only=20be=20applied=20to=20a=20string=0Adiff=20= --git=20a/src/test/regress/sql/jsonb_jsonpath.sql=20= b/src/test/regress/sql/jsonb_jsonpath.sql=0Aindex=20= 17f9d038c0..efb332faef=20100644=0A---=20= a/src/test/regress/sql/jsonb_jsonpath.sql=0A+++=20= b/src/test/regress/sql/jsonb_jsonpath.sql=0A@@=20-602,6=20+602,24=20@@=20= select=20jsonb_path_query('"2023-08-15=2012:34:56=20+5:30"',=20= '$.timestamp().string()')=0A=20select=20jsonb_path_query_tz('"2023-08-15=20= 12:34:56=20+5:30"',=20'$.timestamp().string()');=20--=20should=20work=0A=20= select=20jsonb_path_query_array('[1.23,=20"yes",=20false]',=20= '$[*].string()');=0A=20select=20jsonb_path_query_array('[1.23,=20"yes",=20= false]',=20'$[*].string().type()');=0A+select=20= jsonb_path_query('"2023-08-15=2012:34:56"',=20= '$.timestamp_tz().string()');=0A+select=20= jsonb_path_query_tz('"2023-08-15=2012:34:56"',=20= '$.timestamp_tz().string()');=20--=20should=20work=0A+select=20= jsonb_path_query('"2023-08-15=2012:34:56=20+5:30"',=20= '$.timestamp_tz().string()');=0A+select=20jsonb_path_query('"2023-08-15=20= 12:34:56"',=20'$.timestamp().string()');=0A+select=20= jsonb_path_query('"12:34:56=20+5:30"',=20'$.time_tz().string()');=0A= +select=20jsonb_path_query_tz('"12:34:56"',=20'$.time_tz().string()');=20= --=20should=20work=0A+select=20jsonb_path_query('"12:34:56"',=20= '$.time().string()');=0A+select=20jsonb_path_query('"2023-08-15"',=20= '$.date().string()');=0A+=0A+set=20datestyle=20=3D=20'ISO';=0A+select=20= jsonb_path_query_tz('"2023-08-15=2012:34:56"',=20= '$.timestamp_tz().string()');=20--=20should=20work=0A+select=20= jsonb_path_query('"2023-08-15=2012:34:56=20+5:30"',=20= '$.timestamp_tz().string()');=0A+select=20jsonb_path_query('"2023-08-15=20= 12:34:56"',=20'$.timestamp().string()');=0A+select=20= jsonb_path_query('"12:34:56=20+5:30"',=20'$.time_tz().string()');=0A= +select=20jsonb_path_query_tz('"12:34:56"',=20'$.time_tz().string()');=20= --=20should=20work=0A+select=20jsonb_path_query('"12:34:56"',=20= '$.time().string()');=0A+select=20jsonb_path_query('"2023-08-15"',=20= '$.date().string()');=0A+reset=20datestyle;=0A=20=0A=20--=20Test=20= .time()=0A=20select=20jsonb_path_query('null',=20'$.time()');=0A--=20=0A= 2.45.2=0A=0A= --Apple-Mail=_72B8E175-A1A7-42E3-8D17-AF200A7EB69D--