"David E. Wheeler" <da...@justatheory.com> writes:
> On Sep 11, 2024, at 12:26, Tom Lane <t...@sss.pgh.pa.us> wrote:
>> Building on that thought, maybe we could fix it as attached?
> It looks like that’s what datum_to_json_internal() in json.c does, 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
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 applied to an object
</para>
<para>
String value converted from a JSON boolean, number, string, or
- datetime (the output format for datetimes is determined by
- the <xref linkend="guc-datestyle"/> parameter)
+ datetime
</para>
<para>
<literal>jsonb_path_query_array('[1.23, "xyz", false]', '$[*].string()')</literal>
<returnvalue>["1.23", "xyz", "false"]</returnvalue>
</para>
<para>
- <literal>jsonb_path_query('"2023-08-15"', '$.datetime().string()')</literal>
- <returnvalue>"2023-08-15"</returnvalue>
+ <literal>jsonb_path_query('"2023-08-15 12:34:56"', '$.timestamp().string()')</literal>
+ <returnvalue>"2023-08-15T12:34:56"</returnvalue>
</para></entry>
</row>
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 *cxt, JsonPathItem *jsp,
break;
case jbvDatetime:
{
- switch (jb->val.datetime.typid)
- {
- case DATEOID:
- tmp = DatumGetCString(DirectFunctionCall1(date_out,
- jb->val.datetime.value));
- break;
- case TIMEOID:
- tmp = DatumGetCString(DirectFunctionCall1(time_out,
- jb->val.datetime.value));
- break;
- case TIMETZOID:
- tmp = DatumGetCString(DirectFunctionCall1(timetz_out,
- jb->val.datetime.value));
- break;
- case TIMESTAMPOID:
- tmp = DatumGetCString(DirectFunctionCall1(timestamp_out,
- jb->val.datetime.value));
- break;
- case TIMESTAMPTZOID:
- tmp = 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 = pstrdup(buf);
}
break;
case jbvNull:
diff --git a/src/test/regress/expected/jsonb_jsonpath.out b/src/test/regress/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:30"', '$.timestamp().string()')
ERROR: cannot convert value from timestamptz to timestamp without time zone 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 zone usage
HINT: Use *_tz() function for time zone support.
select jsonb_path_query_tz('"2023-08-15 12:34:56"', '$.timestamp_tz().string()'); -- 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().string()');
select jsonb_path_query('"2023-08-15"', '$.date().string()');
jsonb_path_query
------------------
- "08-15-2023"
-(1 row)
-
-set datestyle = 'ISO';
-select jsonb_path_query_tz('"2023-08-15 12:34:56"', '$.timestamp_tz().string()');
- 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 = 'UTC';
+set local datestyle = '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/sql/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 = 'ISO';
-select jsonb_path_query_tz('"2023-08-15 12:34:56"', '$.timestamp_tz().string()');
+-- .string() does not react to timezone or datestyle
+begin;
+set local timezone = 'UTC';
+set local datestyle = '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()');
