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 1sRC6Q-00DocJ-2V for pgsql-hackers@arkaria.postgresql.org; Tue, 09 Jul 2024 14:45:58 +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 1sRC6O-00Fwle-Ek for pgsql-hackers@arkaria.postgresql.org; Tue, 09 Jul 2024 14:45:56 +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 1sRC6N-00FwlW-On for pgsql-hackers@lists.postgresql.org; Tue, 09 Jul 2024 14:45:56 +0000 Received: from pb-smtp1.pobox.com ([64.147.108.70]) by magus.postgresql.org with esmtps (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sRC6K-001EkZ-Mo for pgsql-hackers@lists.postgresql.org; Tue, 09 Jul 2024 14:45:54 +0000 Received: from pb-smtp1.pobox.com (unknown [127.0.0.1]) by pb-smtp1.pobox.com (Postfix) with ESMTP id CDEE333B7D; Tue, 9 Jul 2024 10:45:48 -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=Odva4JsyqHZV4Gw0qQR7wzvcLOvTdPdxo1 G6tMkorP8=; b=yUqNs/ZrBWc4teYqXrMZIImYwqx6/yaFqva9LRptGHtQRezBAY v6zMkMfnoVWKn3A7mHD1rpAT3CQzGvkGhGPyXfEcK04YyCZ9UQRQocjItOSg2eah dKGa5TLVjlL2t++P3cEwyUrK7BHQQMbQ0j21XCy6WHKFHUiep+GZb+ulM= Received: from pb-smtp1.nyi.icgroup.com (unknown [127.0.0.1]) by pb-smtp1.pobox.com (Postfix) with ESMTP id C60DD33B7C; Tue, 9 Jul 2024 10:45:48 -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=Odva4JsyqHZV4Gw0qQR7wzvcLOvTdPdxo1G6tMkorP8=; b=B2THc9j5Y1o3IP7i9bvJ8RbbM1zu0U1TCiA8eKKzX05KfLevXkFXgCHXp6OzZziuDHMj/oJiiUT5oIRexzAYJ+dEO87EiSzAZEPcCcrJyH854uvVDKmMQIOgHH0iED6iafhRpV13wP9vTsvW2v7IHdlStQbCp+7tdy1clUyw+nc= 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-smtp1.pobox.com (Postfix) with ESMTPSA id 230B033B7B; Tue, 9 Jul 2024 10:45:48 -0400 (EDT) (envelope-from david@justatheory.com) From: "David E. Wheeler" Message-Id: Content-Type: multipart/mixed; boundary="Apple-Mail=_1981A977-A95D-4C99-9B59-4321D4BFBC00" Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3774.600.62\)) Subject: Re: Document DateStyle effect on jsonpath string() Date: Tue, 9 Jul 2024 10:45:37 -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: EE162870-3E01-11EF-AAA2-5B6DE52EC81B-76319746!pb-smtp1.pobox.com List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --Apple-Mail=_1981A977-A95D-4C99-9B59-4321D4BFBC00 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 On Jul 9, 2024, at 10:35, jian he wrote: > one tiny complaint would be maybe we need `reset datestyle`. That=E2=80=99s fair. Done. D --Apple-Mail=_1981A977-A95D-4C99-9B59-4321D4BFBC00 Content-Disposition: attachment; filename=v2-0001-Document-impact-of-datestyle-on-jsonpath-string.patch Content-Type: application/octet-stream; x-unix-mode=0644; name="v2-0001-Document-impact-of-datestyle-on-jsonpath-string.patch" Content-Transfer-Encoding: quoted-printable =46rom=20a99ffd07a93c3c3ca65efae80ecbb35b2e93f51e=20Mon=20Sep=2017=20= 00:00:00=202001=0AFrom:=20"David=20E.=20Wheeler"=20= =0ADate:=20Tue,=209=20Jul=202024=2010:44:56=20= -0400=0ASubject:=20[PATCH=20v2]=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=20f9d7a8f9c3..8a93d25137=20100644=0A---=20= a/doc/src/sgml/func.sgml=0A+++=20b/doc/src/sgml/func.sgml=0A@@=20= -17965,7=20+17965,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= a6112e86fa..361edb92ec=20100644=0A---=20= a/src/test/regress/expected/jsonb_jsonpath.out=0A+++=20= b/src/test/regress/expected/jsonb_jsonpath.out=0A@@=20-2607,6=20+2607,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= 5e14f7759b..6299f11236=20100644=0A---=20= a/src/test/regress/sql/jsonb_jsonpath.sql=0A+++=20= b/src/test/regress/sql/jsonb_jsonpath.sql=0A@@=20-591,6=20+591,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=_1981A977-A95D-4C99-9B59-4321D4BFBC00--