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 1sOgkK-000avd-Ao for pgsql-hackers@arkaria.postgresql.org; Tue, 02 Jul 2024 16:52:48 +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 1sOgjJ-002teS-BA for pgsql-hackers@arkaria.postgresql.org; Tue, 02 Jul 2024 16:51:46 +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 1sOgjI-002teJ-J8 for pgsql-hackers@lists.postgresql.org; Tue, 02 Jul 2024 16:51:45 +0000 Received: from pb-smtp20.pobox.com ([173.228.157.52]) by magus.postgresql.org with esmtps (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sOgjE-0005CM-Qh for pgsql-hackers@lists.postgresql.org; Tue, 02 Jul 2024 16:51:43 +0000 Received: from pb-smtp20.pobox.com (unknown [127.0.0.1]) by pb-smtp20.pobox.com (Postfix) with ESMTP id 3C99925A84 for ; Tue, 2 Jul 2024 12:51:36 -0400 (EDT) (envelope-from david@justatheory.com) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed; d=pobox.com; h=from :content-type:mime-version:subject:message-id:date:to; s=sasl; bh=+GDRbsbHw1I1orBsTX6R3n32k2ygNoH9+FbLVJucM3g=; b=wPs0WwZSzYvQ CLQ6ArWZ8CKL5SCwkx+YKtjmb6NG0biGF1DgEnGZYYuw+MyEC3eLtLroHh0Aeg+k NxBlJ53SD5h7GqDsaJRNzXcHV5nYoNu3uV4xLfJ7odOWS0Lx2mORy1CINZujWJH/ lt0Diuas4vedcB8dBwqA2QLdzSUB97E= Received: from pb-smtp20.sea.icgroup.com (unknown [127.0.0.1]) by pb-smtp20.pobox.com (Postfix) with ESMTP id 3549C25A83 for ; Tue, 2 Jul 2024 12:51:36 -0400 (EDT) (envelope-from david@justatheory.com) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed; d=justatheory.com; h=from:content-type:mime-version:subject:message-id:date:to; s=2016-12.pbsmtp; bh=+GDRbsbHw1I1orBsTX6R3n32k2ygNoH9+FbLVJucM3g=; b=CCYgupKK7hJAzYBcI5P6TgyIrjpXmJBHegZFe3FquzWV+xAFCq11CroMFgGo8YorUdTQ2YG3j1rsNTKCa0DJn4I6HSWr5Xqm3dV6rnCC1dyJ7n+9jtfe3LQZSA7odAI0J56ffNznEFTRWg+b4RSQpste43FuwxiuuFepAgRvG3I= 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-smtp20.pobox.com (Postfix) with ESMTPSA id 3E53F25A82 for ; Tue, 2 Jul 2024 12:51:32 -0400 (EDT) (envelope-from david@justatheory.com) From: "David E. Wheeler" Content-Type: multipart/mixed; boundary="Apple-Mail=_0E650699-00C7-4D5A-9D99-216929DB3806" Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3774.500.171.1.1\)) Subject: Document DateStyle effect on jsonpath string() Message-Id: <56955B33-6959-4FDA-A459-F00363ECDFEE@justatheory.com> Date: Tue, 2 Jul 2024 12:51:19 -0400 To: PostgreSQL Hackers X-Mailer: Apple Mail (2.3774.500.171.1.1) X-Pobox-Relay-ID: 55DDC81A-3893-11EF-ADFF-C38742FD603B-76319746!pb-smtp20.pobox.com List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --Apple-Mail=_0E650699-00C7-4D5A-9D99-216929DB3806 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 Hackers, In fuzing around trying to work out what=E2=80=99s going on with the = formatting of timestamptz values cast by the timestamp_tz() jsonpath = method[1], I noticed that the formatting of the string() method applied = to date and time objects was not fully tested, or how the output is = determined by the DateStyle method. The attached path aims to rectify this situation by adding tests that = chain string() after the jsonpath date/time methods, both with the = default testing =E2=80=9CPostreSQL=E2=80=9D DateStyle and =E2=80=9CISO=E2=80= =9D. It also mentions the impact of the DateStyle parameter in the = string() documentation. Also available to review as a pull request[2]. Best, David [1]: = https://www.postgresql.org/message-id/7DE080CE-6D8C-4794-9BD1-7D9699172FAB= %40justatheory.com [2]: https://github.com/theory/postgres/pull/7/files --Apple-Mail=_0E650699-00C7-4D5A-9D99-216929DB3806 Content-Disposition: attachment; filename=v1-0001-Document-impact-of-datestyle-on-jsonpath-string.patch Content-Type: application/octet-stream; x-unix-mode=0644; name="v1-0001-Document-impact-of-datestyle-on-jsonpath-string.patch" Content-Transfer-Encoding: quoted-printable =46rom=2021957eb1e21fbce55792f47b7e240199139859ad=20Mon=20Sep=2017=20= 00:00:00=202001=0AFrom:=20"David=20E.=20Wheeler"=20= =0ADate:=20Tue,=202=20Jul=202024=2012:42:43=20= -0400=0ASubject:=20[PATCH=20v1]=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|=2088=20= ++++++++++++++++++++=0A=20src/test/regress/sql/jsonb_jsonpath.sql=20=20=20= =20=20=20|=2017=20++++=0A=203=20files=20changed,=20108=20insertions(+),=20= 1=20deletion(-)=0A=0Adiff=20--git=20a/doc/src/sgml/func.sgml=20= b/doc/src/sgml/func.sgml=0Aindex=20f1f22a1960..0db4dd3f25=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..d224c5cc0f=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,94=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=20--=20Test=20.time()=0A=20select=20jsonb_path_query('null',=20= '$.time()');=0A=20ERROR:=20=20jsonpath=20item=20method=20.time()=20can=20= only=20be=20applied=20to=20a=20string=0Adiff=20--git=20= a/src/test/regress/sql/jsonb_jsonpath.sql=20= b/src/test/regress/sql/jsonb_jsonpath.sql=0Aindex=20= 5e14f7759b..a6e6a0912d=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,23=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=20=0A=20--=20Test=20.time()=0A=20select=20= jsonb_path_query('null',=20'$.time()');=0A--=20=0A2.45.2=0A=0A= --Apple-Mail=_0E650699-00C7-4D5A-9D99-216929DB3806 Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset=us-ascii --Apple-Mail=_0E650699-00C7-4D5A-9D99-216929DB3806--