Re: Comparing date strings with jsonpath expression

On Fri, May 26, 2023 at 7:55 PM Tim Field <tim(at)mohiohio(dot)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(at)2(dot)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.