Received: from malur.postgresql.org ([217.196.149.56])
	by arkaria.postgresql.org with esmtps
 (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256)
	(Exim 4.92)
	(envelope-from <pgsql-hackers-owner+archive@lists.postgresql.org>)
	id 1kJmCa-0006NV-Bn
	for pgsql-hackers@arkaria.postgresql.org; Sat, 19 Sep 2020 23:23:33 +0000
Received: from localhost ([127.0.0.1] helo=malur.postgresql.org)
	by malur.postgresql.org with esmtp (Exim 4.92)
	(envelope-from <pgsql-hackers-owner+archive@lists.postgresql.org>)
	id 1kJmCZ-0002um-7c
	for pgsql-hackers@arkaria.postgresql.org; Sat, 19 Sep 2020 23:23:31 +0000
Received: from makus.postgresql.org ([2001:4800:3e1:1::229])
	by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256)
	(Exim 4.92)
	(envelope-from <n.gluhov@postgrespro.ru>)
	id 1kJmCY-0002uD-F3
	for pgsql-hackers@lists.postgresql.org; Sat, 19 Sep 2020 23:23:31 +0000
Received: from mail.postgrespro.ru ([93.174.131.139])
	by makus.postgresql.org with esmtp (Exim 4.92)
	(envelope-from <n.gluhov@postgrespro.ru>)
	id 1kJmCT-0006C8-S9
	for pgsql-hackers@lists.postgresql.org; Sat, 19 Sep 2020 23:23:29 +0000
Received: from [192.168.0.105] (unknown [188.235.32.227])
	(using TLSv1.2 with cipher ECDHE-RSA-AES128-GCM-SHA256 (128/128 bits))
	(Client did not present a certificate)
	by mail.postgrespro.ru (Postfix) with ESMTPSA id 4429521C0585;
	Sun, 20 Sep 2020 02:23:22 +0300 (MSK)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=postgrespro.ru;
	s=mail; t=1600557802;
	bh=3SxZw2R6eLuPhbmWoNJzjRcVCgrpM6EoGxMpwXNLIWQ=;
	h=To:From:Subject:Date;
	b=nGiHz3ai29Mq4LSlzew69lMN983K6QCr2hsoW4Kf1BSPTshsCElpcaS1s4ZUl0/Su
	 QU6DXnIX5zj0tSHlRztOB3UC5TJWynKQgkdMgWvsVNK0rwZ50CJHTsF1BLJMvd2q6g
	 rOsltALd4avFfUxFGz7nGLxk4SFEJ/p9fMwgfqoY=
To: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>,
 Alexander Korotkov <aekorotkov@gmail.com>
From: Nikita Glukhov <n.gluhov@postgrespro.ru>
Organization: Postgres Professional
Subject: Fix inconsistency in jsonpath .datetime()
Message-ID: <94321be0-cc96-1a81-b6df-796f437f7c66@postgrespro.ru>
Date: Sun, 20 Sep 2020 02:23:21 +0300
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:68.0) Gecko/20100101
 Thunderbird/68.10.0
MIME-Version: 1.0
Content-Type: multipart/mixed;
 boundary="------------6606F6FF0EAC9D6AC4266614"
Content-Language: en-US
List-Id: <pgsql-hackers.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-hackers@lists.postgresql.org>
List-Owner: <mailto:pgsql-hackers-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-hackers>
Precedence: bulk

This is a multi-part message in MIME format.
--------------6606F6FF0EAC9D6AC4266614
Content-Type: multipart/alternative;
 boundary="------------C13E8494E9EDEDB4978B9EA1"


--------------C13E8494E9EDEDB4978B9EA1
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit

Hi!

The beta-tester of PG13 reported a inconsistency in our current jsonpath
datetime() method implementation.  By the standard format strings in datetime()
allows only characters "-./,':; " to be used as separators in format strings.
But our to_json[b]() serializes timestamps into XSD format with "T" separator
between date and time, so the serialized data cannot be parsed back by jsonpath
and it looks inconsistent:

=# SELECT to_jsonb('2020-09-19 23:45:06'::timestamp);
        to_jsonb
-----------------------
  "2020-09-19T23:45:06"
(1 row)

=# SELECT jsonb_path_query(to_jsonb('2020-09-19 23:45:06'::timestamp),
                            '$.datetime()');
ERROR:  datetime format is not recognized: "2020-09-19T23:45:06"
HINT:  Use a datetime template argument to specify the input data format.

=# SELECT jsonb_path_query(to_jsonb('2020-09-19 23:45:06'::timestamp),
                            '$.datetime("yyyy-mm-dd HH:MI:SS")');
ERROR:  unmatched format separator " "

=# SELECT jsonb_path_query(to_jsonb('2020-09-19 23:45:06'::timestamp),
                            '$.datetime("yyyy-mm-dd\"T\"HH:MI:SS")');
ERROR:  invalid datetime format separator: """



Excerpt from SQL-2916 standard (5.3 <literal>, page 197):

<unquoted timestamp string> ::=
   <unquoted date string> <space> <unquoted time string>

<unquoted time string> ::=
   <time value> [ <time zone interval> ]

<time zone interval> ::=
   <sign> <hours value> <colon> <minutes value>



Attached patch #2 tries to fix this problem by enabling escaped characters in
standard mode.  I'm not sure is it better to enable the whole set of text
separators or only the problematic "T" character, allow only quoted text
separators or not.

Patch #1 is a more simple fix (so it comes first) removing excess space between
time and timezone fields in built-in format strings used for datetime type
recognition.  (It seemed to work as expected with extra space in earlier
version of the patch in which standard mode has not yet been introduced).

-- 
Nikita Glukhov
Postgres Professional:http://www.postgrespro.com
The Russian Postgres Company


--------------C13E8494E9EDEDB4978B9EA1
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: 7bit

<html>
  <head>

    <meta http-equiv="content-type" content="text/html; charset=UTF-8">
  </head>
  <body>
    <pre>Hi!

The beta-tester of PG13 reported a inconsistency in our current jsonpath
datetime() method implementation.  By the standard format strings in datetime()
allows only characters "-./,':; " to be used as separators in format strings.
But our to_json[b]() serializes timestamps into XSD format with "T" separator
between date and time, so the serialized data cannot be parsed back by jsonpath
and it looks inconsistent:

=# SELECT to_jsonb('2020-09-19 23:45:06'::timestamp);
       to_jsonb        
-----------------------
 "2020-09-19T23:45:06"
(1 row)

=# SELECT jsonb_path_query(to_jsonb('2020-09-19 23:45:06'::timestamp),
                           '$.datetime()');
ERROR:  datetime format is not recognized: "2020-09-19T23:45:06"
HINT:  Use a datetime template argument to specify the input data format.

=# SELECT jsonb_path_query(to_jsonb('2020-09-19 23:45:06'::timestamp),
                           '$.datetime("yyyy-mm-dd HH:MI:SS")');
ERROR:  unmatched format separator " "

=# SELECT jsonb_path_query(to_jsonb('2020-09-19 23:45:06'::timestamp),
                           '$.datetime("yyyy-mm-dd\"T\"HH:MI:SS")');
ERROR:  invalid datetime format separator: """



Excerpt from SQL-2916 standard (5.3 &lt;literal&gt;, page 197):

&lt;unquoted timestamp string&gt; ::=
  &lt;unquoted date string&gt; &lt;space&gt; &lt;unquoted time string&gt;

&lt;unquoted time string&gt; ::=
  &lt;time value&gt; [ &lt;time zone interval&gt; ]

&lt;time zone interval&gt; ::=
  &lt;sign&gt; &lt;hours value&gt; &lt;colon&gt; &lt;minutes value&gt;



Attached patch #2 tries to fix this problem by enabling escaped characters in
standard mode.  I'm not sure is it better to enable the whole set of text
separators or only the problematic "T" character, allow only quoted text 
separators or not.

Patch #1 is a more simple fix (so it comes first) removing excess space between
time and timezone fields in built-in format strings used for datetime type
recognition.  (It seemed to work as expected with extra space in earlier
version of the patch in which standard mode has not yet been introduced).

</pre>
    <div class="moz-signature">
      <pre>-- 
Nikita Glukhov
Postgres Professional: <a href="http://www.postgrespro.com">http://www.postgrespro.com</a>
The Russian Postgres Company
</pre>
    </div>
  </body>
</html>

--------------C13E8494E9EDEDB4978B9EA1--

--------------6606F6FF0EAC9D6AC4266614
Content-Type: text/x-patch; charset=UTF-8;
 name="0001-Fix-excess-space-in-built-in-format-strings-of-jsonpath-datetime-v1.patch"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
 filename*0="0001-Fix-excess-space-in-built-in-format-strings-of-jsonpath";
 filename*1="-datetime-v1.patch"

From e867111f4f3525b4d9e3710b7d0db530602793ef Mon Sep 17 00:00:00 2001
From: Nikita Glukhov <n.gluhov@postgrespro.ru>
Date: Sat, 19 Sep 2020 22:01:51 +0300
Subject: [PATCH 1/2] Fix excess space in built-in format strings of jsonpath
 .datetime()

---
 src/backend/utils/adt/jsonpath_exec.c        |  8 +--
 src/test/regress/expected/jsonb_jsonpath.out | 76 ++++++++++++++--------------
 src/test/regress/sql/jsonb_jsonpath.sql      | 76 ++++++++++++++--------------
 3 files changed, 80 insertions(+), 80 deletions(-)

diff --git a/src/backend/utils/adt/jsonpath_exec.c b/src/backend/utils/adt/jsonpath_exec.c
index 2c0b362..d3c7fe3 100644
--- a/src/backend/utils/adt/jsonpath_exec.c
+++ b/src/backend/utils/adt/jsonpath_exec.c
@@ -1837,11 +1837,11 @@ executeDateTimeMethod(JsonPathExecContext *cxt, JsonPathItem *jsp,
 		static const char *fmt_str[] =
 		{
 			"yyyy-mm-dd",
-			"HH24:MI:SS TZH:TZM",
-			"HH24:MI:SS TZH",
+			"HH24:MI:SSTZH:TZM",
+			"HH24:MI:SSTZH",
 			"HH24:MI:SS",
-			"yyyy-mm-dd HH24:MI:SS TZH:TZM",
-			"yyyy-mm-dd HH24:MI:SS TZH",
+			"yyyy-mm-dd HH24:MI:SSTZH:TZM",
+			"yyyy-mm-dd HH24:MI:SSTZH",
 			"yyyy-mm-dd HH24:MI:SS"
 		};
 
diff --git a/src/test/regress/expected/jsonb_jsonpath.out b/src/test/regress/expected/jsonb_jsonpath.out
index c870b7f..31d6f05 100644
--- a/src/test/regress/expected/jsonb_jsonpath.out
+++ b/src/test/regress/expected/jsonb_jsonpath.out
@@ -1877,25 +1877,25 @@ select jsonb_path_query('"2017-03-10 12:34:56"', '$.datetime()');
  "2017-03-10T12:34:56"
 (1 row)
 
-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().type()');
       jsonb_path_query      
 ----------------------------
  "timestamp with time zone"
 (1 row)
 
-select jsonb_path_query('"2017-03-10 12:34:56 +3"', '$.datetime()');
+select jsonb_path_query('"2017-03-10 12:34:56+3"', '$.datetime()');
       jsonb_path_query       
 -----------------------------
  "2017-03-10T12:34:56+03:00"
 (1 row)
 
-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().type()');
       jsonb_path_query      
 ----------------------------
  "timestamp with time zone"
 (1 row)
 
-select jsonb_path_query('"2017-03-10 12:34:56 +3:10"', '$.datetime()');
+select jsonb_path_query('"2017-03-10 12:34:56+3:10"', '$.datetime()');
       jsonb_path_query       
 -----------------------------
  "2017-03-10T12:34:56+03:10"
@@ -1913,25 +1913,25 @@ select jsonb_path_query('"12:34:56"', '$.datetime()');
  "12:34:56"
 (1 row)
 
-select jsonb_path_query('"12:34:56 +3"', '$.datetime().type()');
+select jsonb_path_query('"12:34:56+3"', '$.datetime().type()');
    jsonb_path_query    
 -----------------------
  "time with time zone"
 (1 row)
 
-select jsonb_path_query('"12:34:56 +3"', '$.datetime()');
+select jsonb_path_query('"12:34:56+3"', '$.datetime()');
  jsonb_path_query 
 ------------------
  "12:34:56+03:00"
 (1 row)
 
-select jsonb_path_query('"12:34:56 +3:10"', '$.datetime().type()');
+select jsonb_path_query('"12:34:56+3:10"', '$.datetime().type()');
    jsonb_path_query    
 -----------------------
  "time with time zone"
 (1 row)
 
-select jsonb_path_query('"12:34:56 +3:10"', '$.datetime()');
+select jsonb_path_query('"12:34:56+3:10"', '$.datetime()');
  jsonb_path_query 
 ------------------
  "12:34:56+03:10"
@@ -1940,22 +1940,22 @@ select jsonb_path_query('"12:34:56 +3:10"', '$.datetime()');
 set time zone '+00';
 -- date comparison
 select jsonb_path_query(
-	'["2017-03-10", "2017-03-11", "2017-03-09", "12:34:56", "01:02:03 +04", "2017-03-10 00:00:00", "2017-03-10 12:34:56", "2017-03-10 01:02:03 +04", "2017-03-10 03:00:00 +03"]',
+	'["2017-03-10", "2017-03-11", "2017-03-09", "12:34:56", "01:02:03+04", "2017-03-10 00:00:00", "2017-03-10 12:34:56", "2017-03-10 01:02:03+04", "2017-03-10 03:00:00+03"]',
 	'$[*].datetime() ? (@ == "10.03.2017".datetime("dd.mm.yyyy"))');
 ERROR:  cannot convert value from date to timestamptz without time zone usage
 HINT:  Use *_tz() function for time zone support.
 select jsonb_path_query(
-	'["2017-03-10", "2017-03-11", "2017-03-09", "12:34:56", "01:02:03 +04", "2017-03-10 00:00:00", "2017-03-10 12:34:56", "2017-03-10 01:02:03 +04", "2017-03-10 03:00:00 +03"]',
+	'["2017-03-10", "2017-03-11", "2017-03-09", "12:34:56", "01:02:03+04", "2017-03-10 00:00:00", "2017-03-10 12:34:56", "2017-03-10 01:02:03+04", "2017-03-10 03:00:00+03"]',
 	'$[*].datetime() ? (@ >= "10.03.2017".datetime("dd.mm.yyyy"))');
 ERROR:  cannot convert value from date to timestamptz without time zone usage
 HINT:  Use *_tz() function for time zone support.
 select jsonb_path_query(
-	'["2017-03-10", "2017-03-11", "2017-03-09", "12:34:56", "01:02:03 +04", "2017-03-10 00:00:00", "2017-03-10 12:34:56", "2017-03-10 01:02:03 +04", "2017-03-10 03:00:00 +03"]',
+	'["2017-03-10", "2017-03-11", "2017-03-09", "12:34:56", "01:02:03+04", "2017-03-10 00:00:00", "2017-03-10 12:34:56", "2017-03-10 01:02:03+04", "2017-03-10 03:00:00+03"]',
 	'$[*].datetime() ? (@ <  "10.03.2017".datetime("dd.mm.yyyy"))');
 ERROR:  cannot convert value from date to timestamptz without time zone usage
 HINT:  Use *_tz() function for time zone support.
 select jsonb_path_query_tz(
-	'["2017-03-10", "2017-03-11", "2017-03-09", "12:34:56", "01:02:03 +04", "2017-03-10 00:00:00", "2017-03-10 12:34:56", "2017-03-10 01:02:03 +04", "2017-03-10 03:00:00 +03"]',
+	'["2017-03-10", "2017-03-11", "2017-03-09", "12:34:56", "01:02:03+04", "2017-03-10 00:00:00", "2017-03-10 12:34:56", "2017-03-10 01:02:03+04", "2017-03-10 03:00:00+03"]',
 	'$[*].datetime() ? (@ == "10.03.2017".datetime("dd.mm.yyyy"))');
      jsonb_path_query_tz     
 -----------------------------
@@ -1965,7 +1965,7 @@ select jsonb_path_query_tz(
 (3 rows)
 
 select jsonb_path_query_tz(
-	'["2017-03-10", "2017-03-11", "2017-03-09", "12:34:56", "01:02:03 +04", "2017-03-10 00:00:00", "2017-03-10 12:34:56", "2017-03-10 01:02:03 +04", "2017-03-10 03:00:00 +03"]',
+	'["2017-03-10", "2017-03-11", "2017-03-09", "12:34:56", "01:02:03+04", "2017-03-10 00:00:00", "2017-03-10 12:34:56", "2017-03-10 01:02:03+04", "2017-03-10 03:00:00+03"]',
 	'$[*].datetime() ? (@ >= "10.03.2017".datetime("dd.mm.yyyy"))');
      jsonb_path_query_tz     
 -----------------------------
@@ -1977,7 +1977,7 @@ select jsonb_path_query_tz(
 (5 rows)
 
 select jsonb_path_query_tz(
-	'["2017-03-10", "2017-03-11", "2017-03-09", "12:34:56", "01:02:03 +04", "2017-03-10 00:00:00", "2017-03-10 12:34:56", "2017-03-10 01:02:03 +04", "2017-03-10 03:00:00 +03"]',
+	'["2017-03-10", "2017-03-11", "2017-03-09", "12:34:56", "01:02:03+04", "2017-03-10 00:00:00", "2017-03-10 12:34:56", "2017-03-10 01:02:03+04", "2017-03-10 03:00:00+03"]',
 	'$[*].datetime() ? (@ <  "10.03.2017".datetime("dd.mm.yyyy"))');
      jsonb_path_query_tz     
 -----------------------------
@@ -1987,22 +1987,22 @@ select jsonb_path_query_tz(
 
 -- time comparison
 select jsonb_path_query(
-	'["12:34:00", "12:35:00", "12:36:00", "12:35:00 +00", "12:35:00 +01", "13:35:00 +01", "2017-03-10", "2017-03-10 12:35:00", "2017-03-10 12:35:00 +01"]',
+	'["12:34:00", "12:35:00", "12:36:00", "12:35:00+00", "12:35:00+01", "13:35:00+01", "2017-03-10", "2017-03-10 12:35:00", "2017-03-10 12:35:00+01"]',
 	'$[*].datetime() ? (@ == "12:35".datetime("HH24:MI"))');
 ERROR:  cannot convert value from time to timetz without time zone usage
 HINT:  Use *_tz() function for time zone support.
 select jsonb_path_query(
-	'["12:34:00", "12:35:00", "12:36:00", "12:35:00 +00", "12:35:00 +01", "13:35:00 +01", "2017-03-10", "2017-03-10 12:35:00", "2017-03-10 12:35:00 +01"]',
+	'["12:34:00", "12:35:00", "12:36:00", "12:35:00+00", "12:35:00+01", "13:35:00+01", "2017-03-10", "2017-03-10 12:35:00", "2017-03-10 12:35:00+01"]',
 	'$[*].datetime() ? (@ >= "12:35".datetime("HH24:MI"))');
 ERROR:  cannot convert value from time to timetz without time zone usage
 HINT:  Use *_tz() function for time zone support.
 select jsonb_path_query(
-	'["12:34:00", "12:35:00", "12:36:00", "12:35:00 +00", "12:35:00 +01", "13:35:00 +01", "2017-03-10", "2017-03-10 12:35:00", "2017-03-10 12:35:00 +01"]',
+	'["12:34:00", "12:35:00", "12:36:00", "12:35:00+00", "12:35:00+01", "13:35:00+01", "2017-03-10", "2017-03-10 12:35:00", "2017-03-10 12:35:00+01"]',
 	'$[*].datetime() ? (@ <  "12:35".datetime("HH24:MI"))');
 ERROR:  cannot convert value from time to timetz without time zone usage
 HINT:  Use *_tz() function for time zone support.
 select jsonb_path_query_tz(
-	'["12:34:00", "12:35:00", "12:36:00", "12:35:00 +00", "12:35:00 +01", "13:35:00 +01", "2017-03-10", "2017-03-10 12:35:00", "2017-03-10 12:35:00 +01"]',
+	'["12:34:00", "12:35:00", "12:36:00", "12:35:00+00", "12:35:00+01", "13:35:00+01", "2017-03-10", "2017-03-10 12:35:00", "2017-03-10 12:35:00+01"]',
 	'$[*].datetime() ? (@ == "12:35".datetime("HH24:MI"))');
  jsonb_path_query_tz 
 ---------------------
@@ -2011,7 +2011,7 @@ select jsonb_path_query_tz(
 (2 rows)
 
 select jsonb_path_query_tz(
-	'["12:34:00", "12:35:00", "12:36:00", "12:35:00 +00", "12:35:00 +01", "13:35:00 +01", "2017-03-10", "2017-03-10 12:35:00", "2017-03-10 12:35:00 +01"]',
+	'["12:34:00", "12:35:00", "12:36:00", "12:35:00+00", "12:35:00+01", "13:35:00+01", "2017-03-10", "2017-03-10 12:35:00", "2017-03-10 12:35:00+01"]',
 	'$[*].datetime() ? (@ >= "12:35".datetime("HH24:MI"))');
  jsonb_path_query_tz 
 ---------------------
@@ -2021,7 +2021,7 @@ select jsonb_path_query_tz(
 (3 rows)
 
 select jsonb_path_query_tz(
-	'["12:34:00", "12:35:00", "12:36:00", "12:35:00 +00", "12:35:00 +01", "13:35:00 +01", "2017-03-10", "2017-03-10 12:35:00", "2017-03-10 12:35:00 +01"]',
+	'["12:34:00", "12:35:00", "12:36:00", "12:35:00+00", "12:35:00+01", "13:35:00+01", "2017-03-10", "2017-03-10 12:35:00", "2017-03-10 12:35:00+01"]',
 	'$[*].datetime() ? (@ <  "12:35".datetime("HH24:MI"))');
  jsonb_path_query_tz 
 ---------------------
@@ -2032,22 +2032,22 @@ select jsonb_path_query_tz(
 
 -- timetz comparison
 select jsonb_path_query(
-	'["12:34:00 +01", "12:35:00 +01", "12:36:00 +01", "12:35:00 +02", "12:35:00 -02", "10:35:00", "11:35:00", "12:35:00", "2017-03-10", "2017-03-10 12:35:00", "2017-03-10 12:35:00 +1"]',
+	'["12:34:00+01", "12:35:00+01", "12:36:00+01", "12:35:00+02", "12:35:00-02", "10:35:00", "11:35:00", "12:35:00", "2017-03-10", "2017-03-10 12:35:00", "2017-03-10 12:35:00 +1"]',
 	'$[*].datetime() ? (@ == "12:35 +1".datetime("HH24:MI TZH"))');
 ERROR:  cannot convert value from time to timetz without time zone usage
 HINT:  Use *_tz() function for time zone support.
 select jsonb_path_query(
-	'["12:34:00 +01", "12:35:00 +01", "12:36:00 +01", "12:35:00 +02", "12:35:00 -02", "10:35:00", "11:35:00", "12:35:00", "2017-03-10", "2017-03-10 12:35:00", "2017-03-10 12:35:00 +1"]',
+	'["12:34:00+01", "12:35:00+01", "12:36:00+01", "12:35:00+02", "12:35:00-02", "10:35:00", "11:35:00", "12:35:00", "2017-03-10", "2017-03-10 12:35:00", "2017-03-10 12:35:00 +1"]',
 	'$[*].datetime() ? (@ >= "12:35 +1".datetime("HH24:MI TZH"))');
 ERROR:  cannot convert value from time to timetz without time zone usage
 HINT:  Use *_tz() function for time zone support.
 select jsonb_path_query(
-	'["12:34:00 +01", "12:35:00 +01", "12:36:00 +01", "12:35:00 +02", "12:35:00 -02", "10:35:00", "11:35:00", "12:35:00", "2017-03-10", "2017-03-10 12:35:00", "2017-03-10 12:35:00 +1"]',
+	'["12:34:00+01", "12:35:00+01", "12:36:00+01", "12:35:00+02", "12:35:00-02", "10:35:00", "11:35:00", "12:35:00", "2017-03-10", "2017-03-10 12:35:00", "2017-03-10 12:35:00 +1"]',
 	'$[*].datetime() ? (@ <  "12:35 +1".datetime("HH24:MI TZH"))');
 ERROR:  cannot convert value from time to timetz without time zone usage
 HINT:  Use *_tz() function for time zone support.
 select jsonb_path_query_tz(
-	'["12:34:00 +01", "12:35:00 +01", "12:36:00 +01", "12:35:00 +02", "12:35:00 -02", "10:35:00", "11:35:00", "12:35:00", "2017-03-10", "2017-03-10 12:35:00", "2017-03-10 12:35:00 +1"]',
+	'["12:34:00+01", "12:35:00+01", "12:36:00+01", "12:35:00+02", "12:35:00-02", "10:35:00", "11:35:00", "12:35:00", "2017-03-10", "2017-03-10 12:35:00", "2017-03-10 12:35:00 +1"]',
 	'$[*].datetime() ? (@ == "12:35 +1".datetime("HH24:MI TZH"))');
  jsonb_path_query_tz 
 ---------------------
@@ -2055,7 +2055,7 @@ select jsonb_path_query_tz(
 (1 row)
 
 select jsonb_path_query_tz(
-	'["12:34:00 +01", "12:35:00 +01", "12:36:00 +01", "12:35:00 +02", "12:35:00 -02", "10:35:00", "11:35:00", "12:35:00", "2017-03-10", "2017-03-10 12:35:00", "2017-03-10 12:35:00 +1"]',
+	'["12:34:00+01", "12:35:00+01", "12:36:00+01", "12:35:00+02", "12:35:00-02", "10:35:00", "11:35:00", "12:35:00", "2017-03-10", "2017-03-10 12:35:00", "2017-03-10 12:35:00 +1"]',
 	'$[*].datetime() ? (@ >= "12:35 +1".datetime("HH24:MI TZH"))');
  jsonb_path_query_tz 
 ---------------------
@@ -2067,7 +2067,7 @@ select jsonb_path_query_tz(
 (5 rows)
 
 select jsonb_path_query_tz(
-	'["12:34:00 +01", "12:35:00 +01", "12:36:00 +01", "12:35:00 +02", "12:35:00 -02", "10:35:00", "11:35:00", "12:35:00", "2017-03-10", "2017-03-10 12:35:00", "2017-03-10 12:35:00 +1"]',
+	'["12:34:00+01", "12:35:00+01", "12:36:00+01", "12:35:00+02", "12:35:00-02", "10:35:00", "11:35:00", "12:35:00", "2017-03-10", "2017-03-10 12:35:00", "2017-03-10 12:35:00 +1"]',
 	'$[*].datetime() ? (@ <  "12:35 +1".datetime("HH24:MI TZH"))');
  jsonb_path_query_tz 
 ---------------------
@@ -2078,22 +2078,22 @@ select jsonb_path_query_tz(
 
 -- timestamp comparison
 select jsonb_path_query(
-	'["2017-03-10 12:34:00", "2017-03-10 12:35:00", "2017-03-10 12:36:00", "2017-03-10 12:35:00 +01", "2017-03-10 13:35:00 +01", "2017-03-10 12:35:00 -01", "2017-03-10", "2017-03-11", "12:34:56", "12:34:56 +01"]',
+	'["2017-03-10 12:34:00", "2017-03-10 12:35:00", "2017-03-10 12:36:00", "2017-03-10 12:35:00+01", "2017-03-10 13:35:00+01", "2017-03-10 12:35:00-01", "2017-03-10", "2017-03-11", "12:34:56", "12:34:56+01"]',
 	'$[*].datetime() ? (@ == "10.03.2017 12:35".datetime("dd.mm.yyyy HH24:MI"))');
 ERROR:  cannot convert value from timestamp to timestamptz without time zone usage
 HINT:  Use *_tz() function for time zone support.
 select jsonb_path_query(
-	'["2017-03-10 12:34:00", "2017-03-10 12:35:00", "2017-03-10 12:36:00", "2017-03-10 12:35:00 +01", "2017-03-10 13:35:00 +01", "2017-03-10 12:35:00 -01", "2017-03-10", "2017-03-11", "12:34:56", "12:34:56 +01"]',
+	'["2017-03-10 12:34:00", "2017-03-10 12:35:00", "2017-03-10 12:36:00", "2017-03-10 12:35:00+01", "2017-03-10 13:35:00+01", "2017-03-10 12:35:00-01", "2017-03-10", "2017-03-11", "12:34:56", "12:34:56+01"]',
 	'$[*].datetime() ? (@ >= "10.03.2017 12:35".datetime("dd.mm.yyyy HH24:MI"))');
 ERROR:  cannot convert value from timestamp to timestamptz without time zone usage
 HINT:  Use *_tz() function for time zone support.
 select jsonb_path_query(
-	'["2017-03-10 12:34:00", "2017-03-10 12:35:00", "2017-03-10 12:36:00", "2017-03-10 12:35:00 +01", "2017-03-10 13:35:00 +01", "2017-03-10 12:35:00 -01", "2017-03-10", "2017-03-11", "12:34:56", "12:34:56 +01"]',
+	'["2017-03-10 12:34:00", "2017-03-10 12:35:00", "2017-03-10 12:36:00", "2017-03-10 12:35:00+01", "2017-03-10 13:35:00+01", "2017-03-10 12:35:00-01", "2017-03-10", "2017-03-11", "12:34:56", "12:34:56+01"]',
 	'$[*].datetime() ? (@ < "10.03.2017 12:35".datetime("dd.mm.yyyy HH24:MI"))');
 ERROR:  cannot convert value from timestamp to timestamptz without time zone usage
 HINT:  Use *_tz() function for time zone support.
 select jsonb_path_query_tz(
-	'["2017-03-10 12:34:00", "2017-03-10 12:35:00", "2017-03-10 12:36:00", "2017-03-10 12:35:00 +01", "2017-03-10 13:35:00 +01", "2017-03-10 12:35:00 -01", "2017-03-10", "2017-03-11", "12:34:56", "12:34:56 +01"]',
+	'["2017-03-10 12:34:00", "2017-03-10 12:35:00", "2017-03-10 12:36:00", "2017-03-10 12:35:00+01", "2017-03-10 13:35:00+01", "2017-03-10 12:35:00-01", "2017-03-10", "2017-03-11", "12:34:56", "12:34:56+01"]',
 	'$[*].datetime() ? (@ == "10.03.2017 12:35".datetime("dd.mm.yyyy HH24:MI"))');
      jsonb_path_query_tz     
 -----------------------------
@@ -2102,7 +2102,7 @@ select jsonb_path_query_tz(
 (2 rows)
 
 select jsonb_path_query_tz(
-	'["2017-03-10 12:34:00", "2017-03-10 12:35:00", "2017-03-10 12:36:00", "2017-03-10 12:35:00 +01", "2017-03-10 13:35:00 +01", "2017-03-10 12:35:00 -01", "2017-03-10", "2017-03-11", "12:34:56", "12:34:56 +01"]',
+	'["2017-03-10 12:34:00", "2017-03-10 12:35:00", "2017-03-10 12:36:00", "2017-03-10 12:35:00+01", "2017-03-10 13:35:00+01", "2017-03-10 12:35:00-01", "2017-03-10", "2017-03-11", "12:34:56", "12:34:56+01"]',
 	'$[*].datetime() ? (@ >= "10.03.2017 12:35".datetime("dd.mm.yyyy HH24:MI"))');
      jsonb_path_query_tz     
 -----------------------------
@@ -2114,7 +2114,7 @@ select jsonb_path_query_tz(
 (5 rows)
 
 select jsonb_path_query_tz(
-	'["2017-03-10 12:34:00", "2017-03-10 12:35:00", "2017-03-10 12:36:00", "2017-03-10 12:35:00 +01", "2017-03-10 13:35:00 +01", "2017-03-10 12:35:00 -01", "2017-03-10", "2017-03-11", "12:34:56", "12:34:56 +01"]',
+	'["2017-03-10 12:34:00", "2017-03-10 12:35:00", "2017-03-10 12:36:00", "2017-03-10 12:35:00+01", "2017-03-10 13:35:00+01", "2017-03-10 12:35:00-01", "2017-03-10", "2017-03-11", "12:34:56", "12:34:56+01"]',
 	'$[*].datetime() ? (@ < "10.03.2017 12:35".datetime("dd.mm.yyyy HH24:MI"))');
      jsonb_path_query_tz     
 -----------------------------
@@ -2125,22 +2125,22 @@ select jsonb_path_query_tz(
 
 -- timestamptz comparison
 select jsonb_path_query(
-	'["2017-03-10 12:34:00 +01", "2017-03-10 12:35:00 +01", "2017-03-10 12:36:00 +01", "2017-03-10 12:35:00 +02", "2017-03-10 12:35:00 -02", "2017-03-10 10:35:00", "2017-03-10 11:35:00", "2017-03-10 12:35:00", "2017-03-10", "2017-03-11", "12:34:56", "12:34:56 +01"]',
+	'["2017-03-10 12:34:00+01", "2017-03-10 12:35:00+01", "2017-03-10 12:36:00+01", "2017-03-10 12:35:00+02", "2017-03-10 12:35:00-02", "2017-03-10 10:35:00", "2017-03-10 11:35:00", "2017-03-10 12:35:00", "2017-03-10", "2017-03-11", "12:34:56", "12:34:56+01"]',
 	'$[*].datetime() ? (@ == "10.03.2017 12:35 +1".datetime("dd.mm.yyyy HH24:MI TZH"))');
 ERROR:  cannot convert value from timestamp to timestamptz without time zone usage
 HINT:  Use *_tz() function for time zone support.
 select jsonb_path_query(
-	'["2017-03-10 12:34:00 +01", "2017-03-10 12:35:00 +01", "2017-03-10 12:36:00 +01", "2017-03-10 12:35:00 +02", "2017-03-10 12:35:00 -02", "2017-03-10 10:35:00", "2017-03-10 11:35:00", "2017-03-10 12:35:00", "2017-03-10", "2017-03-11", "12:34:56", "12:34:56 +01"]',
+	'["2017-03-10 12:34:00+01", "2017-03-10 12:35:00+01", "2017-03-10 12:36:00+01", "2017-03-10 12:35:00+02", "2017-03-10 12:35:00-02", "2017-03-10 10:35:00", "2017-03-10 11:35:00", "2017-03-10 12:35:00", "2017-03-10", "2017-03-11", "12:34:56", "12:34:56+01"]',
 	'$[*].datetime() ? (@ >= "10.03.2017 12:35 +1".datetime("dd.mm.yyyy HH24:MI TZH"))');
 ERROR:  cannot convert value from timestamp to timestamptz without time zone usage
 HINT:  Use *_tz() function for time zone support.
 select jsonb_path_query(
-	'["2017-03-10 12:34:00 +01", "2017-03-10 12:35:00 +01", "2017-03-10 12:36:00 +01", "2017-03-10 12:35:00 +02", "2017-03-10 12:35:00 -02", "2017-03-10 10:35:00", "2017-03-10 11:35:00", "2017-03-10 12:35:00", "2017-03-10", "2017-03-11", "12:34:56", "12:34:56 +01"]',
+	'["2017-03-10 12:34:00+01", "2017-03-10 12:35:00+01", "2017-03-10 12:36:00+01", "2017-03-10 12:35:00+02", "2017-03-10 12:35:00-02", "2017-03-10 10:35:00", "2017-03-10 11:35:00", "2017-03-10 12:35:00", "2017-03-10", "2017-03-11", "12:34:56", "12:34:56+01"]',
 	'$[*].datetime() ? (@ < "10.03.2017 12:35 +1".datetime("dd.mm.yyyy HH24:MI TZH"))');
 ERROR:  cannot convert value from timestamp to timestamptz without time zone usage
 HINT:  Use *_tz() function for time zone support.
 select jsonb_path_query_tz(
-	'["2017-03-10 12:34:00 +01", "2017-03-10 12:35:00 +01", "2017-03-10 12:36:00 +01", "2017-03-10 12:35:00 +02", "2017-03-10 12:35:00 -02", "2017-03-10 10:35:00", "2017-03-10 11:35:00", "2017-03-10 12:35:00", "2017-03-10", "2017-03-11", "12:34:56", "12:34:56 +01"]',
+	'["2017-03-10 12:34:00+01", "2017-03-10 12:35:00+01", "2017-03-10 12:36:00+01", "2017-03-10 12:35:00+02", "2017-03-10 12:35:00-02", "2017-03-10 10:35:00", "2017-03-10 11:35:00", "2017-03-10 12:35:00", "2017-03-10", "2017-03-11", "12:34:56", "12:34:56+01"]',
 	'$[*].datetime() ? (@ == "10.03.2017 12:35 +1".datetime("dd.mm.yyyy HH24:MI TZH"))');
      jsonb_path_query_tz     
 -----------------------------
@@ -2149,7 +2149,7 @@ select jsonb_path_query_tz(
 (2 rows)
 
 select jsonb_path_query_tz(
-	'["2017-03-10 12:34:00 +01", "2017-03-10 12:35:00 +01", "2017-03-10 12:36:00 +01", "2017-03-10 12:35:00 +02", "2017-03-10 12:35:00 -02", "2017-03-10 10:35:00", "2017-03-10 11:35:00", "2017-03-10 12:35:00", "2017-03-10", "2017-03-11", "12:34:56", "12:34:56 +01"]',
+	'["2017-03-10 12:34:00+01", "2017-03-10 12:35:00+01", "2017-03-10 12:36:00+01", "2017-03-10 12:35:00+02", "2017-03-10 12:35:00-02", "2017-03-10 10:35:00", "2017-03-10 11:35:00", "2017-03-10 12:35:00", "2017-03-10", "2017-03-11", "12:34:56", "12:34:56+01"]',
 	'$[*].datetime() ? (@ >= "10.03.2017 12:35 +1".datetime("dd.mm.yyyy HH24:MI TZH"))');
      jsonb_path_query_tz     
 -----------------------------
@@ -2162,7 +2162,7 @@ select jsonb_path_query_tz(
 (6 rows)
 
 select jsonb_path_query_tz(
-	'["2017-03-10 12:34:00 +01", "2017-03-10 12:35:00 +01", "2017-03-10 12:36:00 +01", "2017-03-10 12:35:00 +02", "2017-03-10 12:35:00 -02", "2017-03-10 10:35:00", "2017-03-10 11:35:00", "2017-03-10 12:35:00", "2017-03-10", "2017-03-11", "12:34:56", "12:34:56 +01"]',
+	'["2017-03-10 12:34:00+01", "2017-03-10 12:35:00+01", "2017-03-10 12:36:00+01", "2017-03-10 12:35:00+02", "2017-03-10 12:35:00-02", "2017-03-10 10:35:00", "2017-03-10 11:35:00", "2017-03-10 12:35:00", "2017-03-10", "2017-03-11", "12:34:56", "12:34:56+01"]',
 	'$[*].datetime() ? (@ < "10.03.2017 12:35 +1".datetime("dd.mm.yyyy HH24:MI TZH"))');
      jsonb_path_query_tz     
 -----------------------------
diff --git a/src/test/regress/sql/jsonb_jsonpath.sql b/src/test/regress/sql/jsonb_jsonpath.sql
index a50abed..dc25ceb 100644
--- a/src/test/regress/sql/jsonb_jsonpath.sql
+++ b/src/test/regress/sql/jsonb_jsonpath.sql
@@ -404,117 +404,117 @@ select jsonb_path_query('"2017-03-10"', '$.datetime().type()');
 select jsonb_path_query('"2017-03-10"', '$.datetime()');
 select jsonb_path_query('"2017-03-10 12:34:56"', '$.datetime().type()');
 select jsonb_path_query('"2017-03-10 12:34:56"', '$.datetime()');
-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-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('"12:34:56"', '$.datetime().type()');
 select jsonb_path_query('"12:34:56"', '$.datetime()');
-select jsonb_path_query('"12:34:56 +3"', '$.datetime().type()');
-select jsonb_path_query('"12:34:56 +3"', '$.datetime()');
-select jsonb_path_query('"12:34:56 +3:10"', '$.datetime().type()');
-select jsonb_path_query('"12:34:56 +3:10"', '$.datetime()');
+select jsonb_path_query('"12:34:56+3"', '$.datetime().type()');
+select jsonb_path_query('"12:34:56+3"', '$.datetime()');
+select jsonb_path_query('"12:34:56+3:10"', '$.datetime().type()');
+select jsonb_path_query('"12:34:56+3:10"', '$.datetime()');
 
 set time zone '+00';
 
 -- date comparison
 select jsonb_path_query(
-	'["2017-03-10", "2017-03-11", "2017-03-09", "12:34:56", "01:02:03 +04", "2017-03-10 00:00:00", "2017-03-10 12:34:56", "2017-03-10 01:02:03 +04", "2017-03-10 03:00:00 +03"]',
+	'["2017-03-10", "2017-03-11", "2017-03-09", "12:34:56", "01:02:03+04", "2017-03-10 00:00:00", "2017-03-10 12:34:56", "2017-03-10 01:02:03+04", "2017-03-10 03:00:00+03"]',
 	'$[*].datetime() ? (@ == "10.03.2017".datetime("dd.mm.yyyy"))');
 select jsonb_path_query(
-	'["2017-03-10", "2017-03-11", "2017-03-09", "12:34:56", "01:02:03 +04", "2017-03-10 00:00:00", "2017-03-10 12:34:56", "2017-03-10 01:02:03 +04", "2017-03-10 03:00:00 +03"]',
+	'["2017-03-10", "2017-03-11", "2017-03-09", "12:34:56", "01:02:03+04", "2017-03-10 00:00:00", "2017-03-10 12:34:56", "2017-03-10 01:02:03+04", "2017-03-10 03:00:00+03"]',
 	'$[*].datetime() ? (@ >= "10.03.2017".datetime("dd.mm.yyyy"))');
 select jsonb_path_query(
-	'["2017-03-10", "2017-03-11", "2017-03-09", "12:34:56", "01:02:03 +04", "2017-03-10 00:00:00", "2017-03-10 12:34:56", "2017-03-10 01:02:03 +04", "2017-03-10 03:00:00 +03"]',
+	'["2017-03-10", "2017-03-11", "2017-03-09", "12:34:56", "01:02:03+04", "2017-03-10 00:00:00", "2017-03-10 12:34:56", "2017-03-10 01:02:03+04", "2017-03-10 03:00:00+03"]',
 	'$[*].datetime() ? (@ <  "10.03.2017".datetime("dd.mm.yyyy"))');
 select jsonb_path_query_tz(
-	'["2017-03-10", "2017-03-11", "2017-03-09", "12:34:56", "01:02:03 +04", "2017-03-10 00:00:00", "2017-03-10 12:34:56", "2017-03-10 01:02:03 +04", "2017-03-10 03:00:00 +03"]',
+	'["2017-03-10", "2017-03-11", "2017-03-09", "12:34:56", "01:02:03+04", "2017-03-10 00:00:00", "2017-03-10 12:34:56", "2017-03-10 01:02:03+04", "2017-03-10 03:00:00+03"]',
 	'$[*].datetime() ? (@ == "10.03.2017".datetime("dd.mm.yyyy"))');
 select jsonb_path_query_tz(
-	'["2017-03-10", "2017-03-11", "2017-03-09", "12:34:56", "01:02:03 +04", "2017-03-10 00:00:00", "2017-03-10 12:34:56", "2017-03-10 01:02:03 +04", "2017-03-10 03:00:00 +03"]',
+	'["2017-03-10", "2017-03-11", "2017-03-09", "12:34:56", "01:02:03+04", "2017-03-10 00:00:00", "2017-03-10 12:34:56", "2017-03-10 01:02:03+04", "2017-03-10 03:00:00+03"]',
 	'$[*].datetime() ? (@ >= "10.03.2017".datetime("dd.mm.yyyy"))');
 select jsonb_path_query_tz(
-	'["2017-03-10", "2017-03-11", "2017-03-09", "12:34:56", "01:02:03 +04", "2017-03-10 00:00:00", "2017-03-10 12:34:56", "2017-03-10 01:02:03 +04", "2017-03-10 03:00:00 +03"]',
+	'["2017-03-10", "2017-03-11", "2017-03-09", "12:34:56", "01:02:03+04", "2017-03-10 00:00:00", "2017-03-10 12:34:56", "2017-03-10 01:02:03+04", "2017-03-10 03:00:00+03"]',
 	'$[*].datetime() ? (@ <  "10.03.2017".datetime("dd.mm.yyyy"))');
 
 -- time comparison
 select jsonb_path_query(
-	'["12:34:00", "12:35:00", "12:36:00", "12:35:00 +00", "12:35:00 +01", "13:35:00 +01", "2017-03-10", "2017-03-10 12:35:00", "2017-03-10 12:35:00 +01"]',
+	'["12:34:00", "12:35:00", "12:36:00", "12:35:00+00", "12:35:00+01", "13:35:00+01", "2017-03-10", "2017-03-10 12:35:00", "2017-03-10 12:35:00+01"]',
 	'$[*].datetime() ? (@ == "12:35".datetime("HH24:MI"))');
 select jsonb_path_query(
-	'["12:34:00", "12:35:00", "12:36:00", "12:35:00 +00", "12:35:00 +01", "13:35:00 +01", "2017-03-10", "2017-03-10 12:35:00", "2017-03-10 12:35:00 +01"]',
+	'["12:34:00", "12:35:00", "12:36:00", "12:35:00+00", "12:35:00+01", "13:35:00+01", "2017-03-10", "2017-03-10 12:35:00", "2017-03-10 12:35:00+01"]',
 	'$[*].datetime() ? (@ >= "12:35".datetime("HH24:MI"))');
 select jsonb_path_query(
-	'["12:34:00", "12:35:00", "12:36:00", "12:35:00 +00", "12:35:00 +01", "13:35:00 +01", "2017-03-10", "2017-03-10 12:35:00", "2017-03-10 12:35:00 +01"]',
+	'["12:34:00", "12:35:00", "12:36:00", "12:35:00+00", "12:35:00+01", "13:35:00+01", "2017-03-10", "2017-03-10 12:35:00", "2017-03-10 12:35:00+01"]',
 	'$[*].datetime() ? (@ <  "12:35".datetime("HH24:MI"))');
 select jsonb_path_query_tz(
-	'["12:34:00", "12:35:00", "12:36:00", "12:35:00 +00", "12:35:00 +01", "13:35:00 +01", "2017-03-10", "2017-03-10 12:35:00", "2017-03-10 12:35:00 +01"]',
+	'["12:34:00", "12:35:00", "12:36:00", "12:35:00+00", "12:35:00+01", "13:35:00+01", "2017-03-10", "2017-03-10 12:35:00", "2017-03-10 12:35:00+01"]',
 	'$[*].datetime() ? (@ == "12:35".datetime("HH24:MI"))');
 select jsonb_path_query_tz(
-	'["12:34:00", "12:35:00", "12:36:00", "12:35:00 +00", "12:35:00 +01", "13:35:00 +01", "2017-03-10", "2017-03-10 12:35:00", "2017-03-10 12:35:00 +01"]',
+	'["12:34:00", "12:35:00", "12:36:00", "12:35:00+00", "12:35:00+01", "13:35:00+01", "2017-03-10", "2017-03-10 12:35:00", "2017-03-10 12:35:00+01"]',
 	'$[*].datetime() ? (@ >= "12:35".datetime("HH24:MI"))');
 select jsonb_path_query_tz(
-	'["12:34:00", "12:35:00", "12:36:00", "12:35:00 +00", "12:35:00 +01", "13:35:00 +01", "2017-03-10", "2017-03-10 12:35:00", "2017-03-10 12:35:00 +01"]',
+	'["12:34:00", "12:35:00", "12:36:00", "12:35:00+00", "12:35:00+01", "13:35:00+01", "2017-03-10", "2017-03-10 12:35:00", "2017-03-10 12:35:00+01"]',
 	'$[*].datetime() ? (@ <  "12:35".datetime("HH24:MI"))');
 
 -- timetz comparison
 select jsonb_path_query(
-	'["12:34:00 +01", "12:35:00 +01", "12:36:00 +01", "12:35:00 +02", "12:35:00 -02", "10:35:00", "11:35:00", "12:35:00", "2017-03-10", "2017-03-10 12:35:00", "2017-03-10 12:35:00 +1"]',
+	'["12:34:00+01", "12:35:00+01", "12:36:00+01", "12:35:00+02", "12:35:00-02", "10:35:00", "11:35:00", "12:35:00", "2017-03-10", "2017-03-10 12:35:00", "2017-03-10 12:35:00 +1"]',
 	'$[*].datetime() ? (@ == "12:35 +1".datetime("HH24:MI TZH"))');
 select jsonb_path_query(
-	'["12:34:00 +01", "12:35:00 +01", "12:36:00 +01", "12:35:00 +02", "12:35:00 -02", "10:35:00", "11:35:00", "12:35:00", "2017-03-10", "2017-03-10 12:35:00", "2017-03-10 12:35:00 +1"]',
+	'["12:34:00+01", "12:35:00+01", "12:36:00+01", "12:35:00+02", "12:35:00-02", "10:35:00", "11:35:00", "12:35:00", "2017-03-10", "2017-03-10 12:35:00", "2017-03-10 12:35:00 +1"]',
 	'$[*].datetime() ? (@ >= "12:35 +1".datetime("HH24:MI TZH"))');
 select jsonb_path_query(
-	'["12:34:00 +01", "12:35:00 +01", "12:36:00 +01", "12:35:00 +02", "12:35:00 -02", "10:35:00", "11:35:00", "12:35:00", "2017-03-10", "2017-03-10 12:35:00", "2017-03-10 12:35:00 +1"]',
+	'["12:34:00+01", "12:35:00+01", "12:36:00+01", "12:35:00+02", "12:35:00-02", "10:35:00", "11:35:00", "12:35:00", "2017-03-10", "2017-03-10 12:35:00", "2017-03-10 12:35:00 +1"]',
 	'$[*].datetime() ? (@ <  "12:35 +1".datetime("HH24:MI TZH"))');
 select jsonb_path_query_tz(
-	'["12:34:00 +01", "12:35:00 +01", "12:36:00 +01", "12:35:00 +02", "12:35:00 -02", "10:35:00", "11:35:00", "12:35:00", "2017-03-10", "2017-03-10 12:35:00", "2017-03-10 12:35:00 +1"]',
+	'["12:34:00+01", "12:35:00+01", "12:36:00+01", "12:35:00+02", "12:35:00-02", "10:35:00", "11:35:00", "12:35:00", "2017-03-10", "2017-03-10 12:35:00", "2017-03-10 12:35:00 +1"]',
 	'$[*].datetime() ? (@ == "12:35 +1".datetime("HH24:MI TZH"))');
 select jsonb_path_query_tz(
-	'["12:34:00 +01", "12:35:00 +01", "12:36:00 +01", "12:35:00 +02", "12:35:00 -02", "10:35:00", "11:35:00", "12:35:00", "2017-03-10", "2017-03-10 12:35:00", "2017-03-10 12:35:00 +1"]',
+	'["12:34:00+01", "12:35:00+01", "12:36:00+01", "12:35:00+02", "12:35:00-02", "10:35:00", "11:35:00", "12:35:00", "2017-03-10", "2017-03-10 12:35:00", "2017-03-10 12:35:00 +1"]',
 	'$[*].datetime() ? (@ >= "12:35 +1".datetime("HH24:MI TZH"))');
 select jsonb_path_query_tz(
-	'["12:34:00 +01", "12:35:00 +01", "12:36:00 +01", "12:35:00 +02", "12:35:00 -02", "10:35:00", "11:35:00", "12:35:00", "2017-03-10", "2017-03-10 12:35:00", "2017-03-10 12:35:00 +1"]',
+	'["12:34:00+01", "12:35:00+01", "12:36:00+01", "12:35:00+02", "12:35:00-02", "10:35:00", "11:35:00", "12:35:00", "2017-03-10", "2017-03-10 12:35:00", "2017-03-10 12:35:00 +1"]',
 	'$[*].datetime() ? (@ <  "12:35 +1".datetime("HH24:MI TZH"))');
 
 -- timestamp comparison
 select jsonb_path_query(
-	'["2017-03-10 12:34:00", "2017-03-10 12:35:00", "2017-03-10 12:36:00", "2017-03-10 12:35:00 +01", "2017-03-10 13:35:00 +01", "2017-03-10 12:35:00 -01", "2017-03-10", "2017-03-11", "12:34:56", "12:34:56 +01"]',
+	'["2017-03-10 12:34:00", "2017-03-10 12:35:00", "2017-03-10 12:36:00", "2017-03-10 12:35:00+01", "2017-03-10 13:35:00+01", "2017-03-10 12:35:00-01", "2017-03-10", "2017-03-11", "12:34:56", "12:34:56+01"]',
 	'$[*].datetime() ? (@ == "10.03.2017 12:35".datetime("dd.mm.yyyy HH24:MI"))');
 select jsonb_path_query(
-	'["2017-03-10 12:34:00", "2017-03-10 12:35:00", "2017-03-10 12:36:00", "2017-03-10 12:35:00 +01", "2017-03-10 13:35:00 +01", "2017-03-10 12:35:00 -01", "2017-03-10", "2017-03-11", "12:34:56", "12:34:56 +01"]',
+	'["2017-03-10 12:34:00", "2017-03-10 12:35:00", "2017-03-10 12:36:00", "2017-03-10 12:35:00+01", "2017-03-10 13:35:00+01", "2017-03-10 12:35:00-01", "2017-03-10", "2017-03-11", "12:34:56", "12:34:56+01"]',
 	'$[*].datetime() ? (@ >= "10.03.2017 12:35".datetime("dd.mm.yyyy HH24:MI"))');
 select jsonb_path_query(
-	'["2017-03-10 12:34:00", "2017-03-10 12:35:00", "2017-03-10 12:36:00", "2017-03-10 12:35:00 +01", "2017-03-10 13:35:00 +01", "2017-03-10 12:35:00 -01", "2017-03-10", "2017-03-11", "12:34:56", "12:34:56 +01"]',
+	'["2017-03-10 12:34:00", "2017-03-10 12:35:00", "2017-03-10 12:36:00", "2017-03-10 12:35:00+01", "2017-03-10 13:35:00+01", "2017-03-10 12:35:00-01", "2017-03-10", "2017-03-11", "12:34:56", "12:34:56+01"]',
 	'$[*].datetime() ? (@ < "10.03.2017 12:35".datetime("dd.mm.yyyy HH24:MI"))');
 select jsonb_path_query_tz(
-	'["2017-03-10 12:34:00", "2017-03-10 12:35:00", "2017-03-10 12:36:00", "2017-03-10 12:35:00 +01", "2017-03-10 13:35:00 +01", "2017-03-10 12:35:00 -01", "2017-03-10", "2017-03-11", "12:34:56", "12:34:56 +01"]',
+	'["2017-03-10 12:34:00", "2017-03-10 12:35:00", "2017-03-10 12:36:00", "2017-03-10 12:35:00+01", "2017-03-10 13:35:00+01", "2017-03-10 12:35:00-01", "2017-03-10", "2017-03-11", "12:34:56", "12:34:56+01"]',
 	'$[*].datetime() ? (@ == "10.03.2017 12:35".datetime("dd.mm.yyyy HH24:MI"))');
 select jsonb_path_query_tz(
-	'["2017-03-10 12:34:00", "2017-03-10 12:35:00", "2017-03-10 12:36:00", "2017-03-10 12:35:00 +01", "2017-03-10 13:35:00 +01", "2017-03-10 12:35:00 -01", "2017-03-10", "2017-03-11", "12:34:56", "12:34:56 +01"]',
+	'["2017-03-10 12:34:00", "2017-03-10 12:35:00", "2017-03-10 12:36:00", "2017-03-10 12:35:00+01", "2017-03-10 13:35:00+01", "2017-03-10 12:35:00-01", "2017-03-10", "2017-03-11", "12:34:56", "12:34:56+01"]',
 	'$[*].datetime() ? (@ >= "10.03.2017 12:35".datetime("dd.mm.yyyy HH24:MI"))');
 select jsonb_path_query_tz(
-	'["2017-03-10 12:34:00", "2017-03-10 12:35:00", "2017-03-10 12:36:00", "2017-03-10 12:35:00 +01", "2017-03-10 13:35:00 +01", "2017-03-10 12:35:00 -01", "2017-03-10", "2017-03-11", "12:34:56", "12:34:56 +01"]',
+	'["2017-03-10 12:34:00", "2017-03-10 12:35:00", "2017-03-10 12:36:00", "2017-03-10 12:35:00+01", "2017-03-10 13:35:00+01", "2017-03-10 12:35:00-01", "2017-03-10", "2017-03-11", "12:34:56", "12:34:56+01"]',
 	'$[*].datetime() ? (@ < "10.03.2017 12:35".datetime("dd.mm.yyyy HH24:MI"))');
 
 -- timestamptz comparison
 select jsonb_path_query(
-	'["2017-03-10 12:34:00 +01", "2017-03-10 12:35:00 +01", "2017-03-10 12:36:00 +01", "2017-03-10 12:35:00 +02", "2017-03-10 12:35:00 -02", "2017-03-10 10:35:00", "2017-03-10 11:35:00", "2017-03-10 12:35:00", "2017-03-10", "2017-03-11", "12:34:56", "12:34:56 +01"]',
+	'["2017-03-10 12:34:00+01", "2017-03-10 12:35:00+01", "2017-03-10 12:36:00+01", "2017-03-10 12:35:00+02", "2017-03-10 12:35:00-02", "2017-03-10 10:35:00", "2017-03-10 11:35:00", "2017-03-10 12:35:00", "2017-03-10", "2017-03-11", "12:34:56", "12:34:56+01"]',
 	'$[*].datetime() ? (@ == "10.03.2017 12:35 +1".datetime("dd.mm.yyyy HH24:MI TZH"))');
 select jsonb_path_query(
-	'["2017-03-10 12:34:00 +01", "2017-03-10 12:35:00 +01", "2017-03-10 12:36:00 +01", "2017-03-10 12:35:00 +02", "2017-03-10 12:35:00 -02", "2017-03-10 10:35:00", "2017-03-10 11:35:00", "2017-03-10 12:35:00", "2017-03-10", "2017-03-11", "12:34:56", "12:34:56 +01"]',
+	'["2017-03-10 12:34:00+01", "2017-03-10 12:35:00+01", "2017-03-10 12:36:00+01", "2017-03-10 12:35:00+02", "2017-03-10 12:35:00-02", "2017-03-10 10:35:00", "2017-03-10 11:35:00", "2017-03-10 12:35:00", "2017-03-10", "2017-03-11", "12:34:56", "12:34:56+01"]',
 	'$[*].datetime() ? (@ >= "10.03.2017 12:35 +1".datetime("dd.mm.yyyy HH24:MI TZH"))');
 select jsonb_path_query(
-	'["2017-03-10 12:34:00 +01", "2017-03-10 12:35:00 +01", "2017-03-10 12:36:00 +01", "2017-03-10 12:35:00 +02", "2017-03-10 12:35:00 -02", "2017-03-10 10:35:00", "2017-03-10 11:35:00", "2017-03-10 12:35:00", "2017-03-10", "2017-03-11", "12:34:56", "12:34:56 +01"]',
+	'["2017-03-10 12:34:00+01", "2017-03-10 12:35:00+01", "2017-03-10 12:36:00+01", "2017-03-10 12:35:00+02", "2017-03-10 12:35:00-02", "2017-03-10 10:35:00", "2017-03-10 11:35:00", "2017-03-10 12:35:00", "2017-03-10", "2017-03-11", "12:34:56", "12:34:56+01"]',
 	'$[*].datetime() ? (@ < "10.03.2017 12:35 +1".datetime("dd.mm.yyyy HH24:MI TZH"))');
 select jsonb_path_query_tz(
-	'["2017-03-10 12:34:00 +01", "2017-03-10 12:35:00 +01", "2017-03-10 12:36:00 +01", "2017-03-10 12:35:00 +02", "2017-03-10 12:35:00 -02", "2017-03-10 10:35:00", "2017-03-10 11:35:00", "2017-03-10 12:35:00", "2017-03-10", "2017-03-11", "12:34:56", "12:34:56 +01"]',
+	'["2017-03-10 12:34:00+01", "2017-03-10 12:35:00+01", "2017-03-10 12:36:00+01", "2017-03-10 12:35:00+02", "2017-03-10 12:35:00-02", "2017-03-10 10:35:00", "2017-03-10 11:35:00", "2017-03-10 12:35:00", "2017-03-10", "2017-03-11", "12:34:56", "12:34:56+01"]',
 	'$[*].datetime() ? (@ == "10.03.2017 12:35 +1".datetime("dd.mm.yyyy HH24:MI TZH"))');
 select jsonb_path_query_tz(
-	'["2017-03-10 12:34:00 +01", "2017-03-10 12:35:00 +01", "2017-03-10 12:36:00 +01", "2017-03-10 12:35:00 +02", "2017-03-10 12:35:00 -02", "2017-03-10 10:35:00", "2017-03-10 11:35:00", "2017-03-10 12:35:00", "2017-03-10", "2017-03-11", "12:34:56", "12:34:56 +01"]',
+	'["2017-03-10 12:34:00+01", "2017-03-10 12:35:00+01", "2017-03-10 12:36:00+01", "2017-03-10 12:35:00+02", "2017-03-10 12:35:00-02", "2017-03-10 10:35:00", "2017-03-10 11:35:00", "2017-03-10 12:35:00", "2017-03-10", "2017-03-11", "12:34:56", "12:34:56+01"]',
 	'$[*].datetime() ? (@ >= "10.03.2017 12:35 +1".datetime("dd.mm.yyyy HH24:MI TZH"))');
 select jsonb_path_query_tz(
-	'["2017-03-10 12:34:00 +01", "2017-03-10 12:35:00 +01", "2017-03-10 12:36:00 +01", "2017-03-10 12:35:00 +02", "2017-03-10 12:35:00 -02", "2017-03-10 10:35:00", "2017-03-10 11:35:00", "2017-03-10 12:35:00", "2017-03-10", "2017-03-11", "12:34:56", "12:34:56 +01"]',
+	'["2017-03-10 12:34:00+01", "2017-03-10 12:35:00+01", "2017-03-10 12:36:00+01", "2017-03-10 12:35:00+02", "2017-03-10 12:35:00-02", "2017-03-10 10:35:00", "2017-03-10 11:35:00", "2017-03-10 12:35:00", "2017-03-10", "2017-03-11", "12:34:56", "12:34:56+01"]',
 	'$[*].datetime() ? (@ < "10.03.2017 12:35 +1".datetime("dd.mm.yyyy HH24:MI TZH"))');
 
 -- overflow during comparison
-- 
2.7.4


--------------6606F6FF0EAC9D6AC4266614
Content-Type: text/x-patch; charset=UTF-8;
 name="0002-Allow-text-characters-in-jsonpath-.datetime-format-strings-v1.patch"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
 filename*0="0002-Allow-text-characters-in-jsonpath-.datetime-format-stri";
 filename*1="ngs-v1.patch"

From 67af761060e2f250b40dc338de99ea5152acd4e6 Mon Sep 17 00:00:00 2001
From: Nikita Glukhov <n.gluhov@postgrespro.ru>
Date: Sun, 20 Sep 2020 01:13:01 +0300
Subject: [PATCH 2/2] Allow text characters in jsonpath .datetime() format
 strings

---
 src/backend/utils/adt/formatting.c           | 18 ++++++++++++++++--
 src/backend/utils/adt/jsonpath_exec.c        |  6 +++---
 src/test/regress/expected/jsonb_jsonpath.out | 23 +++++++++++++++++++++++
 src/test/regress/sql/jsonb_jsonpath.sql      |  6 ++++++
 4 files changed, 48 insertions(+), 5 deletions(-)

diff --git a/src/backend/utils/adt/formatting.c b/src/backend/utils/adt/formatting.c
index f9aa968..ebd96dc 100644
--- a/src/backend/utils/adt/formatting.c
+++ b/src/backend/utils/adt/formatting.c
@@ -1381,7 +1381,7 @@ parse_format(FormatNode *node, const char *str, const KeyWord *kw,
 		{
 			int			chlen;
 
-			if (flags & STD_FLAG)
+			if ((flags & STD_FLAG) && *str != '"')
 			{
 				/*
 				 * Standard mode, allow only following separators: "-./,':; "
@@ -3346,7 +3346,21 @@ DCH_from_char(FormatNode *node, const char *in, TmFromChar *out,
 			}
 			else
 			{
-				s += pg_mblen(s);
+				int			chlen = pg_mblen(s);
+
+				/*
+				 * In standard mode format string characters are strictly
+				 * matched or matched to spaces.
+				 */
+				if (std && n->type == NODE_TYPE_CHAR &&
+					(strncmp(s, n->character, chlen) &&
+					 !isspace((unsigned char) *s)))
+					RETURN_ERROR(ereport(ERROR,
+										 (errcode(ERRCODE_INVALID_DATETIME_FORMAT),
+										  errmsg("unmatched format character \"%s\"",
+												 n->character))));
+
+				s += chlen;
 			}
 			continue;
 		}
diff --git a/src/backend/utils/adt/jsonpath_exec.c b/src/backend/utils/adt/jsonpath_exec.c
index d3c7fe3..36dffb9 100644
--- a/src/backend/utils/adt/jsonpath_exec.c
+++ b/src/backend/utils/adt/jsonpath_exec.c
@@ -1840,9 +1840,9 @@ executeDateTimeMethod(JsonPathExecContext *cxt, JsonPathItem *jsp,
 			"HH24:MI:SSTZH:TZM",
 			"HH24:MI:SSTZH",
 			"HH24:MI:SS",
-			"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:SSTZH:TZM",
+			"yyyy-mm-dd\"T\"HH24:MI:SSTZH",
+			"yyyy-mm-dd\"T\"HH24:MI:SS"
 		};
 
 		/* cache for format texts */
diff --git a/src/test/regress/expected/jsonb_jsonpath.out b/src/test/regress/expected/jsonb_jsonpath.out
index 31d6f05..7c93129 100644
--- a/src/test/regress/expected/jsonb_jsonpath.out
+++ b/src/test/regress/expected/jsonb_jsonpath.out
@@ -1722,6 +1722,20 @@ select jsonb_path_query('"12:34:56 +05:20"', '$.datetime("HH24:MI:SS TZH:TZM").t
  "time with time zone"
 (1 row)
 
+select jsonb_path_query('"10-03-2017T12:34:56"', '$.datetime("dd-mm-yyyy\"T\"HH24:MI:SS")');
+   jsonb_path_query    
+-----------------------
+ "2017-03-10T12:34:56"
+(1 row)
+
+select jsonb_path_query('"10-03-2017t12:34:56"', '$.datetime("dd-mm-yyyy\"T\"HH24:MI:SS")');
+ERROR:  unmatched format character "T"
+select jsonb_path_query('"10-03-2017 12:34:56"', '$.datetime("dd-mm-yyyy\"T\"HH24:MI:SS")');
+   jsonb_path_query    
+-----------------------
+ "2017-03-10T12:34:56"
+(1 row)
+
 set time zone '+00';
 select jsonb_path_query('"10-03-2017 12:34"', '$.datetime("dd-mm-yyyy HH24:MI")');
    jsonb_path_query    
@@ -1901,6 +1915,15 @@ select jsonb_path_query('"2017-03-10 12:34:56+3:10"', '$.datetime()');
  "2017-03-10T12:34:56+03:10"
 (1 row)
 
+select jsonb_path_query('"2017-03-10T12:34:56+3:10"', '$.datetime()');
+      jsonb_path_query       
+-----------------------------
+ "2017-03-10T12:34:56+03:10"
+(1 row)
+
+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('"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 dc25ceb..60f73cb 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()');
-- 
2.7.4


--------------6606F6FF0EAC9D6AC4266614--