Re: Comparing date strings with jsonpath expression - Mailing list pgsql-bugs

Tim Field <tim@mohiohio.com> writes:
> I would expect that any date encoded via JSON.stringify() would be parseable in these JSON path functions as that is
afterall the format that dates are very likely to be in. 
> If I JSON encode a date I get a value such as "2023-05-22T03:09:37.825Z” .datetime() fails to parse this due to the
mircosendsand timezone indicator, yet its possible to convert that with  
> "2023-05-22T03:09:37.825Z”::timestamptz

Hm.  I agree that failure to support fractional seconds is a bad thing.
It seems like an oversight in commit 927d9abb6, which explicitly claimed
to be adding support for what to_json[b] produces for timestamps, yet
that very possibly includes fractional seconds:

regression=# select to_jsonb(now());
              to_jsonb
------------------------------------
 "2023-06-11T16:41:08.281715-04:00"
(1 row)

and datetime() still chokes on those:

regression=# select jsonb_path_query(to_jsonb(now()), '$.datetime()');
ERROR:  datetime format is not recognized: "2023-06-11T16:41:09.633513-04:00"
HINT:  Use a datetime template argument to specify the input data format.

So I think we need something more or less as attached.  (I also
rearranged the order of the existing entries to make them agree with
the comment at the top of the table.  The existing misordering seems
harmless, because the timestamp case "yyyy-mm-dd HH24:MI:SS" can't
match input that matches any of the later timestamptz cases.  But the
next person to edit this table could very possibly screw things up if
we don't make the required ordering clearer.)

Dealing with "Z" is harder, because the underlying to_timestamp()
code hasn't solved that either.  It's difficult to handle generic
timestamp names there because it's hard to tell how much the TZ format
code ought to swallow.  For example "EST", "EST5", and "EST5EDT" are
all legal timezone names to Postgres.  We could possibly make it
accept only timezone abbreviations, which is somewhat sensible since
to_char understands "TZ" to mean that.  But that feels like a new
feature not a bug fix.

The attached, however, does seem like a bug fix so I propose applying
it back to v13.

            regards, tom lane

diff --git a/src/backend/utils/adt/jsonpath_exec.c b/src/backend/utils/adt/jsonpath_exec.c
index 41430bab7e..2d0599b4aa 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 328a6b3919..6659bc9091 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 bd025077d5..e0ce509264 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()');