pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedComparing date strings with jsonpath expression
4+ messages / 3 participants
[nested] [flat]
Commits from this thread:
2023-06-12 6f23b5f74f bd590d1fea 5eaa05f637 7398e27224 Accept fractional seconds in jsonpath's datetime() method.
* Comparing date strings with jsonpath expression
@ 2023-05-26 01:36 Tim Field <tim@mohiohio.com>
0 siblings, 3 replies; 4+ messages in thread
From: Tim Field @ 2023-05-26 01:36 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org
When using .datetime() in jsonpath expressions JSON encoded dates are not parsed, this is coupled with the fact that an error isn’t reported.
I would expect that any date encoded via JSON.stringify() would be parseable in these JSON path functions as that is after all 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 mircosends and timezone indicator, yet its possible to convert that with
"2023-05-22T03:09:37.825Z”::timestamptz
Example here of the issue, and a stackoverflow post with further discussion.
https://dbfiddle.uk/PzW0WOvV
https://stackoverflow.com/questions/76319993/comparing-date-strings-with-jsonpath-expression
Comparing date strings with jsonpath expression
stackoverflow.com=
Attachments:
apple-touch-icon@2.png (image/png, 6.4K) download
view image
* Re: Comparing date strings with jsonpath expression
@ 2023-05-26 13:51 Tom Lane <tgl@sss.pgh.pa.us>
parent: Tim Field <tim@mohiohio.com>
2 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2023-05-26 13:51 UTC (permalink / raw)
To: Tim Field <tim@mohiohio.com>; +Cc: pgsql-bugs@lists.postgresql.org
Tim Field <tim@mohiohio.com> writes:
> Example here of the issue, and a stackoverflow post with further discussion.
We generally ask that bug reports be self-contained, not pointers to
ephemeral web pages. This thread will still be in the Postgres mail
archives long after those pages are gone.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Comparing date strings with jsonpath expression
@ 2023-05-29 00:00 jian he <jian.universality@gmail.com>
parent: Tim Field <tim@mohiohio.com>
2 siblings, 0 replies; 4+ messages in thread
From: jian he @ 2023-05-29 00:00 UTC (permalink / raw)
To: Tim Field <tim@mohiohio.com>; +Cc: pgsql-bugs@lists.postgresql.org
On Fri, May 26, 2023 at 7:55 PM Tim Field <tim@mohiohio.com> wrote:
> When using .datetime() in jsonpath expressions JSON encoded dates are not
> parsed, this is coupled with the fact that an error isn’t reported.
>
> I would expect that any date encoded via JSON.stringify() would be
> parseable in these JSON path functions as that is after all 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 mircosends and timezone
> indicator, yet its possible to convert that with
> "2023-05-22T03:09:37.825Z”::timestamptz
>
> Example here of the issue, and a stackoverflow post with further
> discussion.
>
>
> Postgres 15 <https://dbfiddle.uk/PzW0WOvV;
> dbfiddle.uk <https://dbfiddle.uk/PzW0WOvV;
> <https://dbfiddle.uk/PzW0WOvV; <https://dbfiddle.uk/PzW0WOvV;
>
> [image: apple-touch-icon@2.png]
>
> Comparing date strings with jsonpath expression
> <https://stackoverflow.com/questions/76319993/comparing-date-strings-with-jsonpath-expression;
> stackoverflow.com
> <https://stackoverflow.com/questions/76319993/comparing-date-strings-with-jsonpath-expression;
>
> <https://stackoverflow.com/questions/76319993/comparing-date-strings-with-jsonpath-expression;
>
The datetime() and datetime(*template*) methods use the same parsing rules
> as the to_timestamp SQL function does (see Section 9.8), with three
> exceptions. *First*, these methods don't allow unmatched template
> patterns. *Second*, only the following separators are allowed in the
> template string: minus sign, period, solidus (slash), comma, apostrophe,
> semicolon, colon and space. *Third*, separators in the template string
> must exactly match the input string.
> If different date/time types need to be compared, an implicit cast is
> applied. A date value can be cast to timestamp or timestamptz, timestamp can
> be cast to timestamptz, and time to timetz. However, all but the first of
> these conversions depend on the current TimeZone setting, and thus can
> only be performed within timezone-aware jsonpath functions.select
> jsonb_path_query('"2023-05-22 03:09:37.825"', '$.datetime("yyyy-mm-dd
> HH24:MI:SS.MS")');
--ok
select jsonb_path_query('"2023-05-22 03:09:37.825 +1"',
'$.datetime("yyyy-mm-dd HH24:MI:SS.MS TZH")');
--not ok
select jsonb_path_query('"2023-05-22 03:09:37.825 Z"',
'$.datetime("yyyy-mm-dd HH24:MI:SS.MS TZH")');
ERROR: invalid value "Z" for "TZH"
DETAIL: Value must be an integer.
Attachments:
apple-touch-icon@2.png (image/png, 6.4K) download
view image
* Re: Comparing date strings with jsonpath expression
@ 2023-06-11 21:03 Tom Lane <tgl@sss.pgh.pa.us>
parent: Tim Field <tim@mohiohio.com>
2 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2023-06-11 21:03 UTC (permalink / raw)
To: Tim Field <tim@mohiohio.com>; +Cc: pgsql-bugs@lists.postgresql.org, Alexander Korotkov <aekorotkov@gmail.com>; Nikita Glukhov <n.gluhov@postgrespro.ru>
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 after all 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 mircosends and 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
Attachments:
0001-fix-json-datetime-for-fractional-secs.patch (text/x-diff, 3.6K) download
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()');
end of thread, other threads:[~2023-06-11 21:03 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-05-26 01:36 Comparing date strings with jsonpath expression Tim Field <tim@mohiohio.com>
2023-05-26 13:51 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-05-29 00:00 ` jian he <jian.universality@gmail.com>
2023-06-11 21:03 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox