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 1soSiq-00BRfp-OJ for pgsql-hackers@arkaria.postgresql.org; Wed, 11 Sep 2024 19:09:49 +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 1soShp-00675v-RE for pgsql-hackers@arkaria.postgresql.org; Wed, 11 Sep 2024 19:08:45 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1soShp-00674g-D1 for pgsql-hackers@lists.postgresql.org; Wed, 11 Sep 2024 19:08:45 +0000 Received: from sss.pgh.pa.us ([68.162.161.243]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1soShl-000gDW-Rf for pgsql-hackers@lists.postgresql.org; Wed, 11 Sep 2024 19:08:43 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 48BJ8Zt13892236; Wed, 11 Sep 2024 15:08:35 -0400 From: Tom Lane To: "David E. Wheeler" cc: Andrew Dunstan , Peter Eisentraut , jian he , PostgreSQL Hackers Subject: Re: Document DateStyle effect on jsonpath string() In-reply-to: References: <56955B33-6959-4FDA-A459-F00363ECDFEE@justatheory.com> <4D874C72-8939-4083-8336-AB114D9E29AD@justatheory.com> <3541398.1725994308@sss.pgh.pa.us> <3654686.1725999372@sss.pgh.pa.us> <5e8879d0-a3c8-4be2-950f-d83aa2af953a@eisentraut.org> <3811774.1726063865@sss.pgh.pa.us> <3824427.1726067484@sss.pgh.pa.us> <5801E26B-E2AB-4BA3-8B69-EF8837389284@justatheory.com> <3832087.1726070787@sss.pgh.pa.us> <3835867.1726072002@sss.pgh.pa.us> Comments: In-reply-to "David E. Wheeler" message dated "Wed, 11 Sep 2024 13:06:00 -0400" MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="----- =_aaaaaaaaaa0" Content-ID: <3892181.1726081679.0@sss.pgh.pa.us> Date: Wed, 11 Sep 2024 15:08:35 -0400 Message-ID: <3892235.1726081715@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk ------- =_aaaaaaaaaa0 Content-Type: text/plain; charset="UTF-8" Content-ID: <3892181.1726081679.1@sss.pgh.pa.us> Content-Transfer-Encoding: quoted-printable "David E. Wheeler" writes: > On Sep 11, 2024, at 12:26, Tom Lane wrote: >> Building on that thought, maybe we could fix it as attached? > It looks like that=E2=80=99s what datum_to_json_internal() in json.c doe= s, which IIUC is the default stringification for date and time values. Right. I actually lifted the code from convertJsonbScalar in jsonb_util.c. Here's a more fleshed-out patch with docs and regression test fixes. I figured we could shorten the tests a bit now that the point is just to verify that datestyle *doesn't* affect it. regards, tom lane ------- =_aaaaaaaaaa0 Content-Type: text/x-diff; name="v1-fix-jsonpath-string-method-for-timestamps.patch"; charset="us-ascii" Content-ID: <3892181.1726081679.2@sss.pgh.pa.us> Content-Description: v1-fix-jsonpath-string-method-for-timestamps.patch Content-Transfer-Encoding: quoted-printable diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 1bde4091ca..aa1ac2c4fe 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -18017,16 +18017,15 @@ ERROR: jsonpath member accessor can only be app= lied to an object String value converted from a JSON boolean, number, string, or - datetime (the output format for datetimes is determined by - the parameter) + datetime jsonb_path_query_array('[1.23, "xyz", false]', '$[*].str= ing()') ["1.23", "xyz", "false"] - jsonb_path_query('"2023-08-15"', '$.datetime().string()'= ) - "2023-08-15" + jsonb_path_query('"2023-08-15 12:34:56"', '$.timestamp()= .string()') + "2023-08-15T12:34:56" = diff --git a/src/backend/utils/adt/jsonpath_exec.c b/src/backend/utils/adt= /jsonpath_exec.c index e3ee0093d4..b9c2443b65 100644 --- a/src/backend/utils/adt/jsonpath_exec.c +++ b/src/backend/utils/adt/jsonpath_exec.c @@ -72,6 +72,7 @@ #include "utils/datetime.h" #include "utils/float.h" #include "utils/formatting.h" +#include "utils/json.h" #include "utils/jsonpath.h" #include "utils/lsyscache.h" #include "utils/memutils.h" @@ -1629,32 +1630,13 @@ executeItemOptUnwrapTarget(JsonPathExecContext *cx= t, JsonPathItem *jsp, break; case jbvDatetime: { - switch (jb->val.datetime.typid) - { - case DATEOID: - tmp =3D DatumGetCString(DirectFunctionCall1(date_out, - jb->val.datetime.value)); - break; - case TIMEOID: - tmp =3D DatumGetCString(DirectFunctionCall1(time_out, - jb->val.datetime.value)); - break; - case TIMETZOID: - tmp =3D DatumGetCString(DirectFunctionCall1(timetz_out, - jb->val.datetime.value)); - break; - case TIMESTAMPOID: - tmp =3D DatumGetCString(DirectFunctionCall1(timestamp_out, - jb->val.datetime.value)); - break; - case TIMESTAMPTZOID: - tmp =3D DatumGetCString(DirectFunctionCall1(timestamptz_out, - jb->val.datetime.value)); - break; - default: - elog(ERROR, "unrecognized SQL/JSON datetime type oid: %u", - jb->val.datetime.typid); - } + char buf[MAXDATELEN + 1]; + + JsonEncodeDateTime(buf, + jb->val.datetime.value, + jb->val.datetime.typid, + &jb->val.datetime.tz); + tmp =3D pstrdup(buf); } break; case jbvNull: diff --git a/src/test/regress/expected/jsonb_jsonpath.out b/src/test/regre= ss/expected/jsonb_jsonpath.out index 70eeb655a2..acdf7e436f 100644 --- a/src/test/regress/expected/jsonb_jsonpath.out +++ b/src/test/regress/expected/jsonb_jsonpath.out @@ -2652,30 +2652,30 @@ select jsonb_path_query('"2023-08-15 12:34:56 +5:3= 0"', '$.timestamp().string()') ERROR: cannot convert value from timestamptz to timestamp without time z= one usage HINT: Use *_tz() function for time zone support. select jsonb_path_query_tz('"2023-08-15 12:34:56 +5:30"', '$.timestamp().= string()'); -- should work - jsonb_path_query_tz = ----------------------------- - "Tue Aug 15 00:04:56 2023" + jsonb_path_query_tz = +----------------------- + "2023-08-15T00:04:56" (1 row) = select jsonb_path_query('"2023-08-15 12:34:56"', '$.timestamp_tz().string= ()'); ERROR: cannot convert value from timestamp to timestamptz without time z= one usage HINT: Use *_tz() function for time zone support. select jsonb_path_query_tz('"2023-08-15 12:34:56"', '$.timestamp_tz().str= ing()'); -- should work - jsonb_path_query_tz = --------------------------------- - "Tue Aug 15 12:34:56 2023 PDT" + jsonb_path_query_tz = +----------------------------- + "2023-08-15T12:34:56-07:00" (1 row) = select jsonb_path_query('"2023-08-15 12:34:56 +5:30"', '$.timestamp_tz().= string()'); - jsonb_path_query = --------------------------------- - "Tue Aug 15 00:04:56 2023 PDT" + jsonb_path_query = +----------------------------- + "2023-08-15T12:34:56+05:30" (1 row) = select jsonb_path_query('"2023-08-15 12:34:56"', '$.timestamp().string()'= ); - jsonb_path_query = ----------------------------- - "Tue Aug 15 12:34:56 2023" + jsonb_path_query = +----------------------- + "2023-08-15T12:34:56" (1 row) = select jsonb_path_query('"12:34:56 +5:30"', '$.time_tz().string()'); @@ -2687,7 +2687,7 @@ select jsonb_path_query('"12:34:56 +5:30"', '$.time_= tz().string()'); select jsonb_path_query_tz('"12:34:56"', '$.time_tz().string()'); jsonb_path_query_tz = --------------------- - "12:34:56-07" + "12:34:56-07:00" (1 row) = select jsonb_path_query('"12:34:56"', '$.time().string()'); @@ -2699,53 +2699,26 @@ select jsonb_path_query('"12:34:56"', '$.time().st= ring()'); select jsonb_path_query('"2023-08-15"', '$.date().string()'); jsonb_path_query = ------------------ - "08-15-2023" -(1 row) - -set datestyle =3D 'ISO'; -select jsonb_path_query_tz('"2023-08-15 12:34:56"', '$.timestamp_tz().str= ing()'); - jsonb_path_query_tz = --------------------------- - "2023-08-15 12:34:56-07" + "2023-08-15" (1 row) = +-- .string() does not react to timezone or datestyle +begin; +set local timezone =3D 'UTC'; +set local datestyle =3D 'German'; select jsonb_path_query('"2023-08-15 12:34:56 +5:30"', '$.timestamp_tz().= string()'); - jsonb_path_query = --------------------------- - "2023-08-15 00:04:56-07" + jsonb_path_query = +----------------------------- + "2023-08-15T12:34:56+05:30" (1 row) = select jsonb_path_query('"2023-08-15 12:34:56"', '$.timestamp().string()'= ); jsonb_path_query = ----------------------- - "2023-08-15 12:34:56" -(1 row) - -select jsonb_path_query('"12:34:56 +5:30"', '$.time_tz().string()'); - jsonb_path_query = ------------------- - "12:34:56+05:30" -(1 row) - -select jsonb_path_query_tz('"12:34:56"', '$.time_tz().string()'); - jsonb_path_query_tz = ---------------------- - "12:34:56-07" -(1 row) - -select jsonb_path_query('"12:34:56"', '$.time().string()'); - jsonb_path_query = ------------------- - "12:34:56" -(1 row) - -select jsonb_path_query('"2023-08-15"', '$.date().string()'); - jsonb_path_query = ------------------- - "2023-08-15" + "2023-08-15T12:34:56" (1 row) = -reset datestyle; +rollback; -- Test .time() select jsonb_path_query('null', '$.time()'); ERROR: jsonpath item method .time() can only be applied to a string diff --git a/src/test/regress/sql/jsonb_jsonpath.sql b/src/test/regress/sq= l/jsonb_jsonpath.sql index 4d57e13eda..da3f7969ca 100644 --- a/src/test/regress/sql/jsonb_jsonpath.sql +++ b/src/test/regress/sql/jsonb_jsonpath.sql @@ -611,15 +611,13 @@ select jsonb_path_query_tz('"12:34:56"', '$.time_tz(= ).string()'); select jsonb_path_query('"12:34:56"', '$.time().string()'); select jsonb_path_query('"2023-08-15"', '$.date().string()'); = -set datestyle =3D 'ISO'; -select jsonb_path_query_tz('"2023-08-15 12:34:56"', '$.timestamp_tz().str= ing()'); +-- .string() does not react to timezone or datestyle +begin; +set local timezone =3D 'UTC'; +set local datestyle =3D 'German'; select jsonb_path_query('"2023-08-15 12:34:56 +5:30"', '$.timestamp_tz().= string()'); select jsonb_path_query('"2023-08-15 12:34:56"', '$.timestamp().string()'= ); -select jsonb_path_query('"12:34:56 +5:30"', '$.time_tz().string()'); -select jsonb_path_query_tz('"12:34:56"', '$.time_tz().string()'); -select jsonb_path_query('"12:34:56"', '$.time().string()'); -select jsonb_path_query('"2023-08-15"', '$.date().string()'); -reset datestyle; +rollback; = -- Test .time() select jsonb_path_query('null', '$.time()'); ------- =_aaaaaaaaaa0--