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, 30 Jul 2024 09:47:22 -0400
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
<CACJufxHREyTFzKOWz3f-yD14HxAdfeP1vGQQ2SQrusZ+ziRJyA@mail.gmail.com>
<[email protected]>
<CACJufxHE6q47-GEf3Kk0pD+Snr3yZMM4Byig2=bQKV8js7sP-A@mail.gmail.com>
<[email protected]>
<[email protected]>
On Jul 19, 2024, at 10:22, David E. Wheeler <[email protected]> wrote:
>
> Here’s a rebase on 5784a49. I also updated the commitfest item[1] to link to a new pull request[2], since I seem to have turned the other one into the tz conversion bug fix.
>
> [1]: https://commitfest.postgresql.org/49/5101/
> [2]: https://github.com/theory/postgres/pull/8
Rebase on 47c9803. I also changed the commitfest item[1] to “ready for committer”, since jian reviewed it, though I couldn’t see a way to add jian as a reviewer in the app. Hope that makes sense.
Best,
David
Attachments:
[application/octet-stream] v4-0001-Document-impact-of-datestyle-on-jsonpath-string.patch (6.2K, ../[email protected]/2-v4-0001-Document-impact-of-datestyle-on-jsonpath-string.patch)
download | inline diff:
From 524500cfc09851688f7714e690542632be57fd66 Mon Sep 17 00:00:00 2001
From: "David E. Wheeler" <[email protected]>
Date: Fri, 19 Jul 2024 10:19:46 -0400
Subject: [PATCH v4] 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 b39f97dc8d..efc60641df 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -17971,7 +17971,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 02abaac689..8e1e45d758 100644
--- a/src/test/regress/expected/jsonb_jsonpath.out
+++ b/src/test/regress/expected/jsonb_jsonpath.out
@@ -2657,6 +2657,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 17f9d038c0..efb332faef 100644
--- a/src/test/regress/sql/jsonb_jsonpath.sql
+++ b/src/test/regress/sql/jsonb_jsonpath.sql
@@ -602,6 +602,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