Horton

Thread

Commits

  1. Accept fractional seconds in jsonpath's datetime() method.

  2. Support for ISO 8601 in the jsonpath .datetime() method

  1. Comparing date strings with jsonpath expression

    Tim Field <tim@mohiohio.com> — 2023-05-26T01:36:14Z

    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

  2. Re: Comparing date strings with jsonpath expression

    Tom Lane <tgl@sss.pgh.pa.us> — 2023-05-26T13:51:09Z

    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

  3. Re: Comparing date strings with jsonpath expression

    jian he <jian.universality@gmail.com> — 2023-05-29T00:00:00Z

    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.

  4. Re: Comparing date strings with jsonpath expression

    Tom Lane <tgl@sss.pgh.pa.us> — 2023-06-11T21:03:25Z

    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