Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit5eaa05f

Browse files
committed
Accept fractional seconds in jsonpath's datetime() method.
Commit927d9ab purported to make datetime() accept any stringthat could be output for a datetime value by to_jsonb(). But itoverlooked 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 inexecuteDateTimeMethod(). (Note that while this is nominallymicroseconds, it'll do the right thing for fractions withfewer than six digits.)In passing, re-order the list to restore the datatype orderingspecified in its comment. The violation accidentally did notbreak anything; but the next edit might be less lucky, so addmore 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 parente0e6829 commit5eaa05f

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
@@ -1838,20 +1838,29 @@ executeDateTimeMethod(JsonPathExecContext *cxt, JsonPathItem *jsp,
18381838
* According to SQL/JSON standard enumerate ISO formats for: date,
18391839
* timetz, time, timestamptz, timestamp.
18401840
*
1841-
* We also support ISO 8601 for timestamps, because to_json[b]()
1842-
* functions use this format.
1841+
* We also support ISO 8601format (with "T")for timestamps, because
1842+
*to_json[b]()functions use this format.
18431843
*/
18441844
staticconstchar*fmt_str[]=
18451845
{
1846-
"yyyy-mm-dd",
1846+
"yyyy-mm-dd",/* date */
1847+
"HH24:MI:SS.USTZH:TZM",/* timetz */
1848+
"HH24:MI:SS.USTZH",
18471849
"HH24:MI:SSTZH:TZM",
18481850
"HH24:MI:SSTZH",
1851+
"HH24:MI:SS.US",/* time without tz */
18491852
"HH24:MI:SS",
1853+
"yyyy-mm-dd HH24:MI:SS.USTZH:TZM",/* timestamptz */
1854+
"yyyy-mm-dd HH24:MI:SS.USTZH",
18501855
"yyyy-mm-dd HH24:MI:SSTZH:TZM",
18511856
"yyyy-mm-dd HH24:MI:SSTZH",
1852-
"yyyy-mm-dd HH24:MI:SS",
1857+
"yyyy-mm-dd\"T\"HH24:MI:SS.USTZH:TZM",
1858+
"yyyy-mm-dd\"T\"HH24:MI:SS.USTZH",
18531859
"yyyy-mm-dd\"T\"HH24:MI:SSTZH:TZM",
18541860
"yyyy-mm-dd\"T\"HH24:MI:SSTZH",
1861+
"yyyy-mm-dd HH24:MI:SS.US",/* timestamp without tz */
1862+
"yyyy-mm-dd HH24:MI:SS",
1863+
"yyyy-mm-dd\"T\"HH24:MI:SS.US",
18551864
"yyyy-mm-dd\"T\"HH24:MI:SS"
18561865
};
18571866

‎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)
[8]ページ先頭
©2009-2025 Movatter.jp