Skip to content

Commit 7398e27

Browse files
tglsfdctglsfdc
committed
Accept fractional seconds in jsonpath's datetime() method.
Commit 927d9ab purported to make datetime() accept any string that could be output for a datetime value by to_jsonb(). But it overlooked the possibility of fractional seconds being present, so that cases as simple as to_jsonb(now()) would defeat it. Fix by adding formats that include ".US" to the list in executeDateTimeMethod(). (Note that while this is nominally microseconds, it'll do the right thing for fractions with fewer than six digits.) In passing, re-order the list to restore the datatype ordering specified in its comment. The violation accidentally did not break anything; but the next edit might be less lucky, so add more comments. Per report from Tim Field. Back-patch to v13 where datetime() was added, like the previous patch. Discussion: https://postgr.es/m/014A028B-5CE6-4FDF-AC24-426CA6FC9CEE@mohiohio.com
1 parent 0c52437 commit 7398e27

File tree

3 files changed

+31
-4
lines changed

3 files changed

+31
-4
lines changed

‎src/backend/utils/adt/jsonpath_exec.c

Lines changed: 13 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -1840,20 +1840,29 @@ executeDateTimeMethod(JsonPathExecContext *cxt, JsonPathItem *jsp,
18401840
* According to SQL/JSON standard enumerate ISO formats for: date,
18411841
* timetz, time, timestamptz, timestamp.
18421842
*
1843-
* We also support ISO 8601 for timestamps, because to_json[b]()
1844-
* functions use this format.
1843+
* We also support ISO 8601 format (with "T") for timestamps, because
1844+
* to_json[b]() functions use this format.
18451845
*/
18461846
static const char *fmt_str[] =
18471847
{
1848-
"yyyy-mm-dd",
1848+
"yyyy-mm-dd", /* date */
1849+
"HH24:MI:SS.USTZH:TZM", /* timetz */
1850+
"HH24:MI:SS.USTZH",
18491851
"HH24:MI:SSTZH:TZM",
18501852
"HH24:MI:SSTZH",
1853+
"HH24:MI:SS.US", /* time without tz */
18511854
"HH24:MI:SS",
1855+
"yyyy-mm-dd HH24:MI:SS.USTZH:TZM", /* timestamptz */
1856+
"yyyy-mm-dd HH24:MI:SS.USTZH",
18521857
"yyyy-mm-dd HH24:MI:SSTZH:TZM",
18531858
"yyyy-mm-dd HH24:MI:SSTZH",
1854-
"yyyy-mm-dd HH24:MI:SS",
1859+
"yyyy-mm-dd\"T\"HH24:MI:SS.USTZH:TZM",
1860+
"yyyy-mm-dd\"T\"HH24:MI:SS.USTZH",
18551861
"yyyy-mm-dd\"T\"HH24:MI:SSTZH:TZM",
18561862
"yyyy-mm-dd\"T\"HH24:MI:SSTZH",
1863+
"yyyy-mm-dd HH24:MI:SS.US", /* timestamp without tz */
1864+
"yyyy-mm-dd HH24:MI:SS",
1865+
"yyyy-mm-dd\"T\"HH24:MI:SS.US",
18571866
"yyyy-mm-dd\"T\"HH24:MI:SS"
18581867
};
18591868

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

Lines changed: 15 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1920,6 +1920,21 @@ select jsonb_path_query('"2017-03-10T12:34:56+3:10"', '$.datetime()');
19201920
select jsonb_path_query('"2017-03-10t12:34:56+3:10"', '$.datetime()');
19211921
ERROR: datetime format is not recognized: "2017-03-10t12:34:56+3:10"
19221922
HINT: Use a datetime template argument to specify the input data format.
1923+
select jsonb_path_query('"2017-03-10 12:34:56.789+3:10"', '$.datetime()');
1924+
jsonb_path_query
1925+
---------------------------------
1926+
"2017-03-10T12:34:56.789+03:10"
1927+
(1 row)
1928+
1929+
select jsonb_path_query('"2017-03-10T12:34:56.789+3:10"', '$.datetime()');
1930+
jsonb_path_query
1931+
---------------------------------
1932+
"2017-03-10T12:34:56.789+03:10"
1933+
(1 row)
1934+
1935+
select jsonb_path_query('"2017-03-10t12:34:56.789+3:10"', '$.datetime()');
1936+
ERROR: datetime format is not recognized: "2017-03-10t12:34:56.789+3:10"
1937+
HINT: Use a datetime template argument to specify the input data format.
19231938
select jsonb_path_query('"12:34:56"', '$.datetime().type()');
19241939
jsonb_path_query
19251940
--------------------------

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

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -414,6 +414,9 @@ select jsonb_path_query('"2017-03-10 12:34:56+3:10"', '$.datetime().type()');
414414
select jsonb_path_query('"2017-03-10 12:34:56+3:10"', '$.datetime()');
415415
select jsonb_path_query('"2017-03-10T12:34:56+3:10"', '$.datetime()');
416416
select jsonb_path_query('"2017-03-10t12:34:56+3:10"', '$.datetime()');
417+
select jsonb_path_query('"2017-03-10 12:34:56.789+3:10"', '$.datetime()');
418+
select jsonb_path_query('"2017-03-10T12:34:56.789+3:10"', '$.datetime()');
419+
select jsonb_path_query('"2017-03-10t12:34:56.789+3:10"', '$.datetime()');
417420
select jsonb_path_query('"12:34:56"', '$.datetime().type()');
418421
select jsonb_path_query('"12:34:56"', '$.datetime()');
419422
select jsonb_path_query('"12:34:56+3"', '$.datetime().type()');

0 commit comments

Comments
 (0)