public inbox for [email protected]
help / color / mirror / Atom feedFrom: David E. Wheeler <[email protected]>
To: jian he <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Subject: Re: Document DateStyle effect on jsonpath string()
Date: Tue, 9 Jul 2024 10:45:37 -0400
Message-ID: <[email protected]> (raw)
In-Reply-To: <CACJufxHE6q47-GEf3Kk0pD+Snr3yZMM4Byig2=bQKV8js7sP-A@mail.gmail.com>
References: <[email protected]>
<CACJufxHREyTFzKOWz3f-yD14HxAdfeP1vGQQ2SQrusZ+ziRJyA@mail.gmail.com>
<[email protected]>
<CACJufxHE6q47-GEf3Kk0pD+Snr3yZMM4Byig2=bQKV8js7sP-A@mail.gmail.com>
On Jul 9, 2024, at 10:35, jian he <[email protected]> wrote:
> one tiny complaint would be maybe we need `reset datestyle`.
That’s fair. Done.
D
Attachments:
[application/octet-stream] v2-0001-Document-impact-of-datestyle-on-jsonpath-string.patch (6.2K, ../[email protected]/2-v2-0001-Document-impact-of-datestyle-on-jsonpath-string.patch)
download | inline diff:
From a99ffd07a93c3c3ca65efae80ecbb35b2e93f51e Mon Sep 17 00:00:00 2001
From: "David E. Wheeler" <[email protected]>
Date: Tue, 9 Jul 2024 10:44:56 -0400
Subject: [PATCH v2] Document impact of datestyle on jsonpath string()
Add tests demonstrating the output of the jsonpath `string()` method for
the different date and time data types, and how the `datestyle` GUC
determines that output. Note this relationship in the documentation for
the `string()` method, as well.
---
doc/src/sgml/func.sgml | 4 +-
src/test/regress/expected/jsonb_jsonpath.out | 89 ++++++++++++++++++++
src/test/regress/sql/jsonb_jsonpath.sql | 18 ++++
3 files changed, 110 insertions(+), 1 deletion(-)
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index f9d7a8f9c3..8a93d25137 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -17965,7 +17965,9 @@ ERROR: jsonpath member accessor can only be applied to an object
<returnvalue><replaceable>string</replaceable></returnvalue>
</para>
<para>
- String value converted from a JSON boolean, number, string, or datetime
+ String value converted from a JSON boolean, number, string, or
+ datetime. Note that the string output of datetimes is determined by
+ the <xref linkend="guc-datestyle"/> parameter.
</para>
<para>
<literal>jsonb_path_query_array('[1.23, "xyz", false]', '$[*].string()')</literal>
diff --git a/src/test/regress/expected/jsonb_jsonpath.out b/src/test/regress/expected/jsonb_jsonpath.out
index a6112e86fa..361edb92ec 100644
--- a/src/test/regress/expected/jsonb_jsonpath.out
+++ b/src/test/regress/expected/jsonb_jsonpath.out
@@ -2607,6 +2607,95 @@ select jsonb_path_query_array('[1.23, "yes", false]', '$[*].string().type()');
["string", "string", "string"]
(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"
+(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"
+(1 row)
+
+select jsonb_path_query('"2023-08-15 12:34:56"', '$.timestamp().string()');
+ jsonb_path_query
+----------------------------
+ "Tue Aug 15 12:34:56 2023"
+(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()'); -- should work
+ 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
+------------------
+ "08-15-2023"
+(1 row)
+
+set datestyle = 'ISO';
+select jsonb_path_query_tz('"2023-08-15 12:34:56"', '$.timestamp_tz().string()'); -- should work
+ jsonb_path_query_tz
+--------------------------
+ "2023-08-15 12:34:56-07"
+(1 row)
+
+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"
+(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()'); -- should work
+ 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"
+(1 row)
+
+reset datestyle;
-- 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 5e14f7759b..6299f11236 100644
--- a/src/test/regress/sql/jsonb_jsonpath.sql
+++ b/src/test/regress/sql/jsonb_jsonpath.sql
@@ -591,6 +591,24 @@ select jsonb_path_query('"2023-08-15 12:34:56 +5:30"', '$.timestamp().string()')
select jsonb_path_query_tz('"2023-08-15 12:34:56 +5:30"', '$.timestamp().string()'); -- should work
select jsonb_path_query_array('[1.23, "yes", false]', '$[*].string()');
select jsonb_path_query_array('[1.23, "yes", false]', '$[*].string().type()');
+select jsonb_path_query('"2023-08-15 12:34:56"', '$.timestamp_tz().string()');
+select jsonb_path_query_tz('"2023-08-15 12:34:56"', '$.timestamp_tz().string()'); -- should work
+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()'); -- should work
+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()'); -- should work
+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()'); -- should work
+select jsonb_path_query('"12:34:56"', '$.time().string()');
+select jsonb_path_query('"2023-08-15"', '$.date().string()');
+reset datestyle;
-- Test .time()
select jsonb_path_query('null', '$.time()');
--
2.45.2
view thread (14+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: Document DateStyle effect on jsonpath string()
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox