Re: Comparing date strings with jsonpath expression - Mailing list pgsql-bugs
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”::timestamptzExample here of the issue, and a stackoverflow post with further discussion.
Thedatetime()anddatetime(methods use the same parsing rules as thetemplate)to_timestampSQL 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. Adatevalue can be cast totimestamportimestamptz,timestampcan be cast totimestamptz, andtimetotimetz. However, all but the first of these conversions depend on the current TimeZone setting, and thus can only be performed within timezone-awarejsonpathfunctions.select jsonb_path_query('"2023-05-22 03:09:37.825"', '$.datetime("yyyy-mm-dd HH24:MI:SS.MS")');
--ok
--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.Attachment
pgsql-bugs by date: