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