public inbox for [email protected]
help / color / mirror / Atom feedFrom: David E. Wheeler <[email protected]>
To: PostgreSQL Hackers <[email protected]>
Subject: Document DateStyle effect on jsonpath string()
Date: Tue, 2 Jul 2024 12:51:19 -0400
Message-ID: <[email protected]> (raw)
Hackers,
In fuzing around trying to work out what’s 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 “PostreSQL” DateStyle and “ISO”. 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
Attachments:
[application/octet-stream] v1-0001-Document-impact-of-datestyle-on-jsonpath-string.patch (6.1K, ../[email protected]/2-v1-0001-Document-impact-of-datestyle-on-jsonpath-string.patch)
download | inline diff:
From 21957eb1e21fbce55792f47b7e240199139859ad Mon Sep 17 00:00:00 2001
From: "David E. Wheeler" <[email protected]>
Date: Tue, 2 Jul 2024 12:42:43 -0400
Subject: [PATCH v1] 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 | 88 ++++++++++++++++++++
src/test/regress/sql/jsonb_jsonpath.sql | 17 ++++
3 files changed, 108 insertions(+), 1 deletion(-)
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index f1f22a1960..0db4dd3f25 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..d224c5cc0f 100644
--- a/src/test/regress/expected/jsonb_jsonpath.out
+++ b/src/test/regress/expected/jsonb_jsonpath.out
@@ -2607,6 +2607,94 @@ 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)
+
-- 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..a6e6a0912d 100644
--- a/src/test/regress/sql/jsonb_jsonpath.sql
+++ b/src/test/regress/sql/jsonb_jsonpath.sql
@@ -591,6 +591,23 @@ 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()');
-- Test .time()
select jsonb_path_query('null', '$.time()');
--
2.45.2
view thread (3+ 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]
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