Re: Comparing date strings with jsonpath expression

Tim Field <tim(at)mohiohio(dot)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