Fix inconsistency in jsonpath .datetime()
От | Nikita Glukhov |
---|---|
Тема | Fix inconsistency in jsonpath .datetime() |
Дата | |
Msg-id | 94321be0-cc96-1a81-b6df-796f437f7c66@postgrespro.ru обсуждение исходный текст |
Ответы |
Re: Fix inconsistency in jsonpath .datetime()
|
Список | pgsql-hackers |
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
Вложения
В списке pgsql-hackers по дате отправления: