Skip to content

Commit ed055d2

Browse files
tglsfdctglsfdc
committed
Improve documentation and testing of jsonpath string() for datetimes.
Point out that the output format depends on DateStyle, and test that, along with testing some cases previously not covered. In passing, adjust the horology test to verify that the prevailing DateStyle is 'Postgres, MDY', much as it has long verified the prevailing TimeZone. We expect pg_regress to have set these up, and there are multiple regression tests relying on these settings. Also make the formatting of entries in table 9.50 more consistent. David Wheeler (marginal additional hacking by me); review by jian he Discussion: https://postgr.es/m/56955B33-6959-4FDA-A459-F00363ECDFEE@justatheory.com
1 parent 8610864 commit ed055d2

File tree

5 files changed

+135
-19
lines changed

5 files changed

+135
-19
lines changed

‎doc/src/sgml/func.sgml

Lines changed: 10 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -18016,7 +18016,9 @@ ERROR: jsonpath member accessor can only be applied to an object
1801618016
<returnvalue><replaceable>string</replaceable></returnvalue>
1801718017
</para>
1801818018
<para>
18019-
String value converted from a JSON boolean, number, string, or datetime
18019+
String value converted from a JSON boolean, number, string, or
18020+
datetime (the output format for datetimes is determined by
18021+
the <xref linkend="guc-datestyle"/> parameter)
1802018022
</para>
1802118023
<para>
1802218024
<literal>jsonb_path_query_array('[1.23, "xyz", false]', '$[*].string()')</literal>
@@ -18105,7 +18107,9 @@ ERROR: jsonpath member accessor can only be applied to an object
1810518107
<returnvalue><replaceable>decimal</replaceable></returnvalue>
1810618108
</para>
1810718109
<para>
18108-
Rounded decimal value converted from a JSON number or string. <literal>precision</literal> and <literal>scale</literal> must be integer values.
18110+
Rounded decimal value converted from a JSON number or string
18111+
(<literal>precision</literal> and <literal>scale</literal> must be
18112+
integer values)
1810918113
</para>
1811018114
<para>
1811118115
<literal>jsonb_path_query('1234.5678', '$.decimal(6, 2)')</literal>
@@ -18207,7 +18211,7 @@ ERROR: jsonpath member accessor can only be applied to an object
1820718211
</para>
1820818212
<para>
1820918213
Time without time zone value converted from a string, with fractional
18210-
seconds adjusted to the given precision.
18214+
seconds adjusted to the given precision
1821118215
</para>
1821218216
<para>
1821318217
<literal>jsonb_path_query('"12:34:56.789"', '$.time(2)')</literal>
@@ -18236,7 +18240,7 @@ ERROR: jsonpath member accessor can only be applied to an object
1823618240
</para>
1823718241
<para>
1823818242
Time with time zone value converted from a string, with fractional
18239-
seconds adjusted to the given precision.
18243+
seconds adjusted to the given precision
1824018244
</para>
1824118245
<para>
1824218246
<literal>jsonb_path_query('"12:34:56.789 +05:30"', '$.time_tz(2)')</literal>
@@ -18265,7 +18269,7 @@ ERROR: jsonpath member accessor can only be applied to an object
1826518269
</para>
1826618270
<para>
1826718271
Timestamp without time zone value converted from a string, with
18268-
fractional seconds adjusted to the given precision.
18272+
fractional seconds adjusted to the given precision
1826918273
</para>
1827018274
<para>
1827118275
<literal>jsonb_path_query('"2023-08-15 12:34:56.789"', '$.timestamp(2)')</literal>
@@ -18294,7 +18298,7 @@ ERROR: jsonpath member accessor can only be applied to an object
1829418298
</para>
1829518299
<para>
1829618300
Timestamp with time zone value converted from a string, with fractional
18297-
seconds adjusted to the given precision.
18301+
seconds adjusted to the given precision
1829818302
</para>
1829918303
<para>
1830018304
<literal>jsonb_path_query('"2023-08-15 12:34:56.789 +05:30"', '$.timestamp_tz(2)')</literal>

‎src/test/regress/expected/horology.out

Lines changed: 7 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,13 +1,18 @@
11
--
22
-- HOROLOGY
33
--
4-
SET DateStyle = 'Postgres, MDY';
5-
SHOW TimeZone; -- Many of these tests depend on the prevailing setting
4+
SHOW TimeZone; -- Many of these tests depend on the prevailing settings
65
TimeZone
76
----------
87
PST8PDT
98
(1 row)
109

10+
SHOW DateStyle;
11+
DateStyle
12+
---------------
13+
Postgres, MDY
14+
(1 row)
15+
1116
--
1217
-- Test various input formats
1318
--

‎src/test/regress/expected/jsonb_jsonpath.out

Lines changed: 96 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -2636,6 +2636,18 @@ select jsonb_path_query('[2, true]', '$.string()');
26362636
"true"
26372637
(2 rows)
26382638

2639+
select jsonb_path_query_array('[1.23, "yes", false]', '$[*].string()');
2640+
jsonb_path_query_array
2641+
--------------------------
2642+
["1.23", "yes", "false"]
2643+
(1 row)
2644+
2645+
select jsonb_path_query_array('[1.23, "yes", false]', '$[*].string().type()');
2646+
jsonb_path_query_array
2647+
--------------------------------
2648+
["string", "string", "string"]
2649+
(1 row)
2650+
26392651
select jsonb_path_query('"2023-08-15 12:34:56 +5:30"', '$.timestamp().string()');
26402652
ERROR: cannot convert value from timestamptz to timestamp without time zone usage
26412653
HINT: Use *_tz() function for time zone support.
@@ -2645,18 +2657,95 @@ select jsonb_path_query_tz('"2023-08-15 12:34:56 +5:30"', '$.timestamp().string(
26452657
"Tue Aug 15 00:04:56 2023"
26462658
(1 row)
26472659

2648-
select jsonb_path_query_array('[1.23, "yes", false]', '$[*].string()');
2649-
jsonb_path_query_array
2650-
--------------------------
2651-
["1.23", "yes", "false"]
2660+
select jsonb_path_query('"2023-08-15 12:34:56"', '$.timestamp_tz().string()');
2661+
ERROR: cannot convert value from timestamp to timestamptz without time zone usage
2662+
HINT: Use *_tz() function for time zone support.
2663+
select jsonb_path_query_tz('"2023-08-15 12:34:56"', '$.timestamp_tz().string()'); -- should work
2664+
jsonb_path_query_tz
2665+
--------------------------------
2666+
"Tue Aug 15 12:34:56 2023 PDT"
26522667
(1 row)
26532668

2654-
select jsonb_path_query_array('[1.23, "yes", false]', '$[*].string().type()');
2655-
jsonb_path_query_array
2669+
select jsonb_path_query('"2023-08-15 12:34:56 +5:30"', '$.timestamp_tz().string()');
2670+
jsonb_path_query
26562671
--------------------------------
2657-
["string", "string", "string"]
2672+
"Tue Aug 15 00:04:56 2023 PDT"
2673+
(1 row)
2674+
2675+
select jsonb_path_query('"2023-08-15 12:34:56"', '$.timestamp().string()');
2676+
jsonb_path_query
2677+
----------------------------
2678+
"Tue Aug 15 12:34:56 2023"
2679+
(1 row)
2680+
2681+
select jsonb_path_query('"12:34:56 +5:30"', '$.time_tz().string()');
2682+
jsonb_path_query
2683+
------------------
2684+
"12:34:56+05:30"
2685+
(1 row)
2686+
2687+
select jsonb_path_query_tz('"12:34:56"', '$.time_tz().string()');
2688+
jsonb_path_query_tz
2689+
---------------------
2690+
"12:34:56-07"
2691+
(1 row)
2692+
2693+
select jsonb_path_query('"12:34:56"', '$.time().string()');
2694+
jsonb_path_query
2695+
------------------
2696+
"12:34:56"
2697+
(1 row)
2698+
2699+
select jsonb_path_query('"2023-08-15"', '$.date().string()');
2700+
jsonb_path_query
2701+
------------------
2702+
"08-15-2023"
2703+
(1 row)
2704+
2705+
set datestyle = 'ISO';
2706+
select jsonb_path_query_tz('"2023-08-15 12:34:56"', '$.timestamp_tz().string()');
2707+
jsonb_path_query_tz
2708+
--------------------------
2709+
"2023-08-15 12:34:56-07"
2710+
(1 row)
2711+
2712+
select jsonb_path_query('"2023-08-15 12:34:56 +5:30"', '$.timestamp_tz().string()');
2713+
jsonb_path_query
2714+
--------------------------
2715+
"2023-08-15 00:04:56-07"
2716+
(1 row)
2717+
2718+
select jsonb_path_query('"2023-08-15 12:34:56"', '$.timestamp().string()');
2719+
jsonb_path_query
2720+
-----------------------
2721+
"2023-08-15 12:34:56"
2722+
(1 row)
2723+
2724+
select jsonb_path_query('"12:34:56 +5:30"', '$.time_tz().string()');
2725+
jsonb_path_query
2726+
------------------
2727+
"12:34:56+05:30"
2728+
(1 row)
2729+
2730+
select jsonb_path_query_tz('"12:34:56"', '$.time_tz().string()');
2731+
jsonb_path_query_tz
2732+
---------------------
2733+
"12:34:56-07"
2734+
(1 row)
2735+
2736+
select jsonb_path_query('"12:34:56"', '$.time().string()');
2737+
jsonb_path_query
2738+
------------------
2739+
"12:34:56"
2740+
(1 row)
2741+
2742+
select jsonb_path_query('"2023-08-15"', '$.date().string()');
2743+
jsonb_path_query
2744+
------------------
2745+
"2023-08-15"
26582746
(1 row)
26592747

2748+
reset datestyle;
26602749
-- Test .time()
26612750
select jsonb_path_query('null', '$.time()');
26622751
ERROR: jsonpath item method .time() can only be applied to a string

‎src/test/regress/sql/horology.sql

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,9 +1,9 @@
11
--
22
-- HOROLOGY
33
--
4-
SET DateStyle = 'Postgres, MDY';
54

6-
SHOW TimeZone; -- Many of these tests depend on the prevailing setting
5+
SHOW TimeZone; -- Many of these tests depend on the prevailing settings
6+
SHOW DateStyle;
77

88
--
99
-- Test various input formats

‎src/test/regress/sql/jsonb_jsonpath.sql

Lines changed: 20 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -598,10 +598,28 @@ select jsonb_path_query('1234', '$.string()');
598598
select jsonb_path_query('true', '$.string()');
599599
select jsonb_path_query('1234', '$.string().type()');
600600
select jsonb_path_query('[2, true]', '$.string()');
601-
select jsonb_path_query('"2023-08-15 12:34:56 +5:30"', '$.timestamp().string()');
602-
select jsonb_path_query_tz('"2023-08-15 12:34:56 +5:30"', '$.timestamp().string()'); -- should work
603601
select jsonb_path_query_array('[1.23, "yes", false]', '$[*].string()');
604602
select jsonb_path_query_array('[1.23, "yes", false]', '$[*].string().type()');
603+
select jsonb_path_query('"2023-08-15 12:34:56 +5:30"', '$.timestamp().string()');
604+
select jsonb_path_query_tz('"2023-08-15 12:34:56 +5:30"', '$.timestamp().string()'); -- should work
605+
select jsonb_path_query('"2023-08-15 12:34:56"', '$.timestamp_tz().string()');
606+
select jsonb_path_query_tz('"2023-08-15 12:34:56"', '$.timestamp_tz().string()'); -- should work
607+
select jsonb_path_query('"2023-08-15 12:34:56 +5:30"', '$.timestamp_tz().string()');
608+
select jsonb_path_query('"2023-08-15 12:34:56"', '$.timestamp().string()');
609+
select jsonb_path_query('"12:34:56 +5:30"', '$.time_tz().string()');
610+
select jsonb_path_query_tz('"12:34:56"', '$.time_tz().string()');
611+
select jsonb_path_query('"12:34:56"', '$.time().string()');
612+
select jsonb_path_query('"2023-08-15"', '$.date().string()');
613+
614+
set datestyle = 'ISO';
615+
select jsonb_path_query_tz('"2023-08-15 12:34:56"', '$.timestamp_tz().string()');
616+
select jsonb_path_query('"2023-08-15 12:34:56 +5:30"', '$.timestamp_tz().string()');
617+
select jsonb_path_query('"2023-08-15 12:34:56"', '$.timestamp().string()');
618+
select jsonb_path_query('"12:34:56 +5:30"', '$.time_tz().string()');
619+
select jsonb_path_query_tz('"12:34:56"', '$.time_tz().string()');
620+
select jsonb_path_query('"12:34:56"', '$.time().string()');
621+
select jsonb_path_query('"2023-08-15"', '$.date().string()');
622+
reset datestyle;
605623

606624
-- Test .time()
607625
select jsonb_path_query('null', '$.time()');

0 commit comments

Comments
 (0)