diff options
| -rw-r--r-- | src/backend/utils/adt/jsonpath_exec.c | 17 | ||||
| -rw-r--r-- | src/test/regress/expected/jsonb_jsonpath.out | 15 | ||||
| -rw-r--r-- | src/test/regress/sql/jsonb_jsonpath.sql | 3 |
3 files changed, 31 insertions, 4 deletions
diff --git a/src/backend/utils/adt/jsonpath_exec.c b/src/backend/utils/adt/jsonpath_exec.c index 41430bab7ed..2d0599b4aaa 100644 --- a/src/backend/utils/adt/jsonpath_exec.c +++ b/src/backend/utils/adt/jsonpath_exec.c @@ -1840,20 +1840,29 @@ executeDateTimeMethod(JsonPathExecContext *cxt, JsonPathItem *jsp, * According to SQL/JSON standard enumerate ISO formats for: date, * timetz, time, timestamptz, timestamp. * - * We also support ISO 8601 for timestamps, because to_json[b]() - * functions use this format. + * We also support ISO 8601 format (with "T") for timestamps, because + * to_json[b]() functions use this format. */ static const char *fmt_str[] = { - "yyyy-mm-dd", + "yyyy-mm-dd", /* date */ + "HH24:MI:SS.USTZH:TZM", /* timetz */ + "HH24:MI:SS.USTZH", "HH24:MI:SSTZH:TZM", "HH24:MI:SSTZH", + "HH24:MI:SS.US", /* time without tz */ "HH24:MI:SS", + "yyyy-mm-dd HH24:MI:SS.USTZH:TZM", /* timestamptz */ + "yyyy-mm-dd HH24:MI:SS.USTZH", "yyyy-mm-dd HH24:MI:SSTZH:TZM", "yyyy-mm-dd HH24:MI:SSTZH", - "yyyy-mm-dd HH24:MI:SS", + "yyyy-mm-dd\"T\"HH24:MI:SS.USTZH:TZM", + "yyyy-mm-dd\"T\"HH24:MI:SS.USTZH", "yyyy-mm-dd\"T\"HH24:MI:SSTZH:TZM", "yyyy-mm-dd\"T\"HH24:MI:SSTZH", + "yyyy-mm-dd HH24:MI:SS.US", /* timestamp without tz */ + "yyyy-mm-dd HH24:MI:SS", + "yyyy-mm-dd\"T\"HH24:MI:SS.US", "yyyy-mm-dd\"T\"HH24:MI:SS" }; diff --git a/src/test/regress/expected/jsonb_jsonpath.out b/src/test/regress/expected/jsonb_jsonpath.out index 328a6b39199..6659bc9091a 100644 --- a/src/test/regress/expected/jsonb_jsonpath.out +++ b/src/test/regress/expected/jsonb_jsonpath.out @@ -1920,6 +1920,21 @@ select jsonb_path_query('"2017-03-10T12:34:56+3:10"', '$.datetime()'); select jsonb_path_query('"2017-03-10t12:34:56+3:10"', '$.datetime()'); ERROR: datetime format is not recognized: "2017-03-10t12:34:56+3:10" HINT: Use a datetime template argument to specify the input data format. +select jsonb_path_query('"2017-03-10 12:34:56.789+3:10"', '$.datetime()'); + jsonb_path_query +--------------------------------- + "2017-03-10T12:34:56.789+03:10" +(1 row) + +select jsonb_path_query('"2017-03-10T12:34:56.789+3:10"', '$.datetime()'); + jsonb_path_query +--------------------------------- + "2017-03-10T12:34:56.789+03:10" +(1 row) + +select jsonb_path_query('"2017-03-10t12:34:56.789+3:10"', '$.datetime()'); +ERROR: datetime format is not recognized: "2017-03-10t12:34:56.789+3:10" +HINT: Use a datetime template argument to specify the input data format. select jsonb_path_query('"12:34:56"', '$.datetime().type()'); jsonb_path_query -------------------------- diff --git a/src/test/regress/sql/jsonb_jsonpath.sql b/src/test/regress/sql/jsonb_jsonpath.sql index bd025077d52..e0ce509264a 100644 --- a/src/test/regress/sql/jsonb_jsonpath.sql +++ b/src/test/regress/sql/jsonb_jsonpath.sql @@ -414,6 +414,9 @@ select jsonb_path_query('"2017-03-10 12:34:56+3:10"', '$.datetime().type()'); select jsonb_path_query('"2017-03-10 12:34:56+3:10"', '$.datetime()'); select jsonb_path_query('"2017-03-10T12:34:56+3:10"', '$.datetime()'); select jsonb_path_query('"2017-03-10t12:34:56+3:10"', '$.datetime()'); +select jsonb_path_query('"2017-03-10 12:34:56.789+3:10"', '$.datetime()'); +select jsonb_path_query('"2017-03-10T12:34:56.789+3:10"', '$.datetime()'); +select jsonb_path_query('"2017-03-10t12:34:56.789+3:10"', '$.datetime()'); select jsonb_path_query('"12:34:56"', '$.datetime().type()'); select jsonb_path_query('"12:34:56"', '$.datetime()'); select jsonb_path_query('"12:34:56+3"', '$.datetime().type()'); |