Comparing date strings with jsonpath expression

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

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&gt;
dbfiddle.uk <https://dbfiddle.uk/PzW0WOvV&gt;
<https://dbfiddle.uk/PzW0WOvV&gt; <https://dbfiddle.uk/PzW0WOvV&gt;

[image: apple-touch-icon@2.png]

Comparing date strings with jsonpath expression
<https://stackoverflow.com/questions/76319993/comparing-date-strings-with-jsonpath-expression&gt;
stackoverflow.com
<https://stackoverflow.com/questions/76319993/comparing-date-strings-with-jsonpath-expression&gt;

<https://stackoverflow.com/questions/76319993/comparing-date-strings-with-jsonpath-expression&gt;

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.

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