From 927d9abb6538e441aa97bb3eea7a15d4cda70715 Mon Sep 17 00:00:00 2001 From: Alexander Korotkov Date: Tue, 29 Sep 2020 11:41:46 +0300 Subject: Support for ISO 8601 in the jsonpath .datetime() method The SQL standard doesn't require jsonpath .datetime() method to support the ISO 8601 format. But our to_json[b]() functions convert timestamps to text in the ISO 8601 format in the sake of compatibility with javascript. So, we add support of the ISO 8601 to the jsonpath .datetime() in the sake compatibility with to_json[b](). The standard mode of datetime parsing currently supports just template patterns and separators in the format string. In order to implement ISO 8601, we have to add support of the format string double quotes to the standard parsing mode. Discussion: https://postgr.es/m/94321be0-cc96-1a81-b6df-796f437f7c66%40postgrespro.ru Author: Nikita Glukhov, revised by me Backpatch-through: 13 --- src/test/regress/sql/jsonb_jsonpath.sql | 6 ++++++ 1 file changed, 6 insertions(+) (limited to 'src/test/regress/sql/jsonb_jsonpath.sql') diff --git a/src/test/regress/sql/jsonb_jsonpath.sql b/src/test/regress/sql/jsonb_jsonpath.sql index dc25ceb283d..60f73cb0590 100644 --- a/src/test/regress/sql/jsonb_jsonpath.sql +++ b/src/test/regress/sql/jsonb_jsonpath.sql @@ -368,6 +368,10 @@ select jsonb_path_query('"10-03-2017 12:34 +05:20"', '$.datetime("dd-mm-yyyy HH2 select jsonb_path_query('"12:34:56"', '$.datetime("HH24:MI:SS").type()'); select jsonb_path_query('"12:34:56 +05:20"', '$.datetime("HH24:MI:SS TZH:TZM").type()'); +select jsonb_path_query('"10-03-2017T12:34:56"', '$.datetime("dd-mm-yyyy\"T\"HH24:MI:SS")'); +select jsonb_path_query('"10-03-2017t12:34:56"', '$.datetime("dd-mm-yyyy\"T\"HH24:MI:SS")'); +select jsonb_path_query('"10-03-2017 12:34:56"', '$.datetime("dd-mm-yyyy\"T\"HH24:MI:SS")'); + set time zone '+00'; 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()'); select jsonb_path_query('"2017-03-10 12:34:56+3"', '$.datetime()'); 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('"12:34:56"', '$.datetime().type()'); select jsonb_path_query('"12:34:56"', '$.datetime()'); select jsonb_path_query('"12:34:56+3"', '$.datetime().type()'); -- cgit v1.2.3