Обсуждение: jsonpath Time and Timestamp Special Cases

Hello hackers,

I noticed that the jsonpath date/time functions (.time() and timestamp(), et al.) don’t support some valid but
special-casePostgreSQL values, notably `infinity`, `-infinity`, and, for times, '24:00:00`: 

❯ psql
psql (17devel)
Type "help" for help.

david=# select jsonb_path_query(to_jsonb('24:00:00'::time), '$.time()');
ERROR:  time format is not recognized: "24:00:00"

david=# select jsonb_path_query(to_jsonb('infinity'::timestamptz), '$.timestamp_tz()');
ERROR:  timestamp_tz format is not recognized: "infinity"

I assume this is because the standard doesn’t support these, or references JavaScript-only values or some such. Is that
right?

Best,

David

On Apr 29, 2024, at 20:45, David E. Wheeler <david@justatheory.com> wrote:

> I noticed that the jsonpath date/time functions (.time() and timestamp(), et al.) don’t support some valid but
special-casePostgreSQL values, notably `infinity`, `-infinity`, and, for times, '24:00:00`: 

Looking at ECMA-404[1], “The JSON data interchange syntax”, it says, of numbers:

> Numeric values that cannot be represented as sequences of digits (such as Infinity and NaN) are not
permitted.

So it makes sense that the JSON path standard would be the same, since such JSON explicitly cannot represent these
valuesas numbers. 

Still not sure about `24:00:00` as a time, though. I presume the jsonpath standard disallows it.

Best,

David

[1]: https://ecma-international.org/wp-content/uploads/ECMA-404_2nd_edition_december_2017.pdf

On 06/20/24 10:54, David E. Wheeler wrote:
> Still not sure about `24:00:00` as a time, though. I presume the jsonpath standard disallows it.

In 9075-2 9.46 "SQL/JSON path language: syntax and semantics", the behavior
of the .time() and .time_tz() and similar item methods defers to the
behavior of SQL's CAST.

For example, .time(PS) (where PS is the optional precision spec) expects
to be applied to a character string X from the JSON source, and its
success/failure and result are the same as for CAST(X AS TIME PS).

The fact that our CAST(X AS TIME) will succeed for '24:00:00' might be
its own extension (or violation) of the spec (I haven't checked that),
but given that it does, we could debate whether it violates the jsonpath
spec for our jsonpath .time() to behave the same way.

The same argument may also apply for ±infinity.

Regards,
-Chap