Horton

Thread

Commits

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

  2. Remove excess space from jsonpath .datetime() default format string

  1. Fix inconsistency in jsonpath .datetime()

    Nikita Glukhov <n.gluhov@postgrespro.ru> — 2020-09-19T23:23:21Z

    Hi!

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).

-- 
Nikita Glukhov
Postgres Professional:http://www.postgrespro.com
The Russian Postgres Company

  2. Re: Fix inconsistency in jsonpath .datetime()

    Alexander Korotkov <aekorotkov@gmail.com> — 2020-09-24T23:02:57Z

    On Sun, Sep 20, 2020 at 2:23 AM Nikita Glukhov <n.gluhov@postgrespro.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

  3. Re: Fix inconsistency in jsonpath .datetime()

    Alexander Korotkov <aekorotkov@gmail.com> — 2020-09-29T09:03:06Z

    On Fri, Sep 25, 2020 at 2:02 AM Alexander Korotkov <aekorotkov@gmail.com> wrote:
> Other opinions?

Given no other opinions yet, I've pushed the both patches.

------
Regards,
Alexander Korotkov