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

Commit927d9ab

Browse files
committed
Support for ISO 8601 in the jsonpath .datetime() method
The SQL standard doesn't require jsonpath .datetime() method to support theISO 8601 format. But our to_json[b]() functions convert timestamps to text inthe ISO 8601 format in the sake of compatibility with javascript. So, we addsupport of the ISO 8601 to the jsonpath .datetime() in the sake compatibilitywith to_json[b]().The standard mode of datetime parsing currently supports just template patternsand separators in the format string. In order to implement ISO 8601, we have toadd support of the format string double quotes to the standard parsing mode.Discussion:https://postgr.es/m/94321be0-cc96-1a81-b6df-796f437f7c66%40postgrespro.ruAuthor: Nikita Glukhov, revised by meBackpatch-through: 13
1 parentc2aa562 commit927d9ab

File tree

4 files changed

+49
-4
lines changed

4 files changed

+49
-4
lines changed

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

Lines changed: 17 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1381,10 +1381,12 @@ parse_format(FormatNode *node, const char *str, const KeyWord *kw,
13811381
{
13821382
intchlen;
13831383

1384-
if (flags&STD_FLAG)
1384+
if ((flags&STD_FLAG)&&*str!='"')
13851385
{
13861386
/*
1387-
* Standard mode, allow only following separators: "-./,':; "
1387+
* Standard mode, allow only following separators: "-./,':; ".
1388+
* However, we support double quotes even in standard mode
1389+
* (see below). This is our extension of standard mode.
13881390
*/
13891391
if (strchr("-./,':; ",*str)==NULL)
13901392
ereport(ERROR,
@@ -3346,7 +3348,19 @@ DCH_from_char(FormatNode *node, const char *in, TmFromChar *out,
33463348
}
33473349
else
33483350
{
3349-
s+=pg_mblen(s);
3351+
intchlen=pg_mblen(s);
3352+
3353+
/*
3354+
* Standard mode requires strict match of format characters.
3355+
*/
3356+
if (std&&n->type==NODE_TYPE_CHAR&&
3357+
strncmp(s,n->character,chlen)!=0)
3358+
RETURN_ERROR(ereport(ERROR,
3359+
(errcode(ERRCODE_INVALID_DATETIME_FORMAT),
3360+
errmsg("unmatched format character \"%s\"",
3361+
n->character))));
3362+
3363+
s+=chlen;
33503364
}
33513365
continue;
33523366
}

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

Lines changed: 7 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1833,6 +1833,9 @@ executeDateTimeMethod(JsonPathExecContext *cxt, JsonPathItem *jsp,
18331833
/*
18341834
* According to SQL/JSON standard enumerate ISO formats for: date,
18351835
* timetz, time, timestamptz, timestamp.
1836+
*
1837+
* We also support ISO 8601 for timestamps, because to_json[b]()
1838+
* functions use this format.
18361839
*/
18371840
staticconstchar*fmt_str[]=
18381841
{
@@ -1842,7 +1845,10 @@ executeDateTimeMethod(JsonPathExecContext *cxt, JsonPathItem *jsp,
18421845
"HH24:MI:SS",
18431846
"yyyy-mm-dd HH24:MI:SSTZH:TZM",
18441847
"yyyy-mm-dd HH24:MI:SSTZH",
1845-
"yyyy-mm-dd HH24:MI:SS"
1848+
"yyyy-mm-dd HH24:MI:SS",
1849+
"yyyy-mm-dd\"T\"HH24:MI:SSTZH:TZM",
1850+
"yyyy-mm-dd\"T\"HH24:MI:SSTZH",
1851+
"yyyy-mm-dd\"T\"HH24:MI:SS"
18461852
};
18471853

18481854
/* cache for format texts */

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

Lines changed: 19 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1722,6 +1722,16 @@ select jsonb_path_query('"12:34:56 +05:20"', '$.datetime("HH24:MI:SS TZH:TZM").t
17221722
"time with time zone"
17231723
(1 row)
17241724

1725+
select jsonb_path_query('"10-03-2017T12:34:56"', '$.datetime("dd-mm-yyyy\"T\"HH24:MI:SS")');
1726+
jsonb_path_query
1727+
-----------------------
1728+
"2017-03-10T12:34:56"
1729+
(1 row)
1730+
1731+
select jsonb_path_query('"10-03-2017t12:34:56"', '$.datetime("dd-mm-yyyy\"T\"HH24:MI:SS")');
1732+
ERROR: unmatched format character "T"
1733+
select jsonb_path_query('"10-03-2017 12:34:56"', '$.datetime("dd-mm-yyyy\"T\"HH24:MI:SS")');
1734+
ERROR: unmatched format character "T"
17251735
set time zone '+00';
17261736
select jsonb_path_query('"10-03-2017 12:34"', '$.datetime("dd-mm-yyyy HH24:MI")');
17271737
jsonb_path_query
@@ -1901,6 +1911,15 @@ select jsonb_path_query('"2017-03-10 12:34:56+3:10"', '$.datetime()');
19011911
"2017-03-10T12:34:56+03:10"
19021912
(1 row)
19031913

1914+
select jsonb_path_query('"2017-03-10T12:34:56+3:10"', '$.datetime()');
1915+
jsonb_path_query
1916+
-----------------------------
1917+
"2017-03-10T12:34:56+03:10"
1918+
(1 row)
1919+
1920+
select jsonb_path_query('"2017-03-10t12:34:56+3:10"', '$.datetime()');
1921+
ERROR: datetime format is not recognized: "2017-03-10t12:34:56+3:10"
1922+
HINT: Use a datetime template argument to specify the input data format.
19041923
select jsonb_path_query('"12:34:56"', '$.datetime().type()');
19051924
jsonb_path_query
19061925
--------------------------

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

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -368,6 +368,10 @@ select jsonb_path_query('"10-03-2017 12:34 +05:20"', '$.datetime("dd-mm-yyyy HH2
368368
select jsonb_path_query('"12:34:56"','$.datetime("HH24:MI:SS").type()');
369369
select jsonb_path_query('"12:34:56 +05:20"','$.datetime("HH24:MI:SS TZH:TZM").type()');
370370

371+
select jsonb_path_query('"10-03-2017T12:34:56"','$.datetime("dd-mm-yyyy\"T\"HH24:MI:SS")');
372+
select jsonb_path_query('"10-03-2017t12:34:56"','$.datetime("dd-mm-yyyy\"T\"HH24:MI:SS")');
373+
select jsonb_path_query('"10-03-2017 12:34:56"','$.datetime("dd-mm-yyyy\"T\"HH24:MI:SS")');
374+
371375
settime zone'+00';
372376

373377
select jsonb_path_query('"10-03-2017 12:34"','$.datetime("dd-mm-yyyy HH24:MI")');
@@ -408,6 +412,8 @@ select jsonb_path_query('"2017-03-10 12:34:56+3"', '$.datetime().type()');
408412
select jsonb_path_query('"2017-03-10 12:34:56+3"','$.datetime()');
409413
select jsonb_path_query('"2017-03-10 12:34:56+3:10"','$.datetime().type()');
410414
select jsonb_path_query('"2017-03-10 12:34:56+3:10"','$.datetime()');
415+
select jsonb_path_query('"2017-03-10T12:34:56+3:10"','$.datetime()');
416+
select jsonb_path_query('"2017-03-10t12:34:56+3:10"','$.datetime()');
411417
select jsonb_path_query('"12:34:56"','$.datetime().type()');
412418
select jsonb_path_query('"12:34:56"','$.datetime()');
413419
select jsonb_path_query('"12:34:56+3"','$.datetime().type()');

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp