Re: Fix inconsistency in jsonpath .datetime()

On Sun, Sep 20, 2020 at 2:23 AM Nikita Glukhov <n(dot)gluhov(at)postgrespro(dot)ru> wrote:
> The beta-tester of PG13 reported a inconsistency in our current jsonpath
> datetime() method implementation. By the standard format strings in datetime()
> allows only characters "-./,':; " to be used as separators in format strings.
> But our to_json[b]() serializes timestamps into XSD format with "T" separator
> between date and time, so the serialized data cannot be parsed back by jsonpath
> and it looks inconsistent:
>
> =# SELECT to_jsonb('2020-09-19 23:45:06'::timestamp);
> to_jsonb
> -----------------------
> "2020-09-19T23:45:06"
> (1 row)
>
> =# SELECT jsonb_path_query(to_jsonb('2020-09-19 23:45:06'::timestamp),
> '$.datetime()');
> ERROR: datetime format is not recognized: "2020-09-19T23:45:06"
> HINT: Use a datetime template argument to specify the input data format.
>
> =# SELECT jsonb_path_query(to_jsonb('2020-09-19 23:45:06'::timestamp),
> '$.datetime("yyyy-mm-dd HH:MI:SS")');
> ERROR: unmatched format separator " "
>
> =# SELECT jsonb_path_query(to_jsonb('2020-09-19 23:45:06'::timestamp),
> '$.datetime("yyyy-mm-dd\"T\"HH:MI:SS")');
> ERROR: invalid datetime format separator: """
>
>
>
> Excerpt from SQL-2916 standard (5.3 <literal>, page 197):
>
> <unquoted timestamp string> ::=
> <unquoted date string> <space> <unquoted time string>
>
> <unquoted time string> ::=
> <time value> [ <time zone interval> ]
>
> <time zone interval> ::=
> <sign> <hours value> <colon> <minutes value>
>
>
>
> Attached patch #2 tries to fix this problem by enabling escaped characters in
> standard mode. I'm not sure is it better to enable the whole set of text
> separators or only the problematic "T" character, allow only quoted text
> separators or not.
>
> Patch #1 is a more simple fix (so it comes first) removing excess space between
> time and timezone fields in built-in format strings used for datetime type
> recognition. (It seemed to work as expected with extra space in earlier
> version of the patch in which standard mode has not yet been introduced).

Jsonpath .datetime() was developed as an implementation of
corresponding parts of SQL Standard. Patch #1 fixes inconsistency
between our implementation and Standard. I'm going to backpatch it to
v13.

There is also inconsistency among to_json[b]() and jsonpath
.datetime(). In this case, I wouldn't say the problem is on the
jsonpath side. to_json[b]() makes special exceptions for datetime
types and converts them not using standard output function, but using
javascript-compatible format (see f30015b6d7). Luckily, our input
function for timestamp[tz] datatypes doesn't use strict format
parsing, so it can work with output of to_json[b](). But according to
SQL Standard, jsonpath .datetime() implements strict format parsing,
so it can't work with output of to_json[b](). So, I wouldn't say in
this case it's an inconsistency in the jsonpath .datetime() method.
But, given now it's not an appropriate time for redesigning
to_json[b](), we should probably improve jsonpath .datetime() method
to understand more formats.

So, patch #2 is probably acceptable, and even might be backpatched
v13. One thing I don't particularly like is "In standard mode format
string characters are strictly matched or matched to spaces."
Instead, I would like to just strictly match characters and just add
more options to fmt_str[].

Other opinions?

------
Regards,
Alexander Korotkov