On Wed, Mar 8, 2017 at 12:43 AM, Sven R. Kunze <srkunze@mail.de> wrote:
Hi,
about the datetime issue: as far as I know, JSON does not define a serialization format for dates and timestamps.
On the other hand, YAML (as a superset of JSON) already supports a language-independent date(time) serialization format (http://yaml.org/type/timestamp.html).
I haven't had a glance into the SQL/JSON standard yet and a quick search didn't reveal anything. However, reading your test case here https://github.com/postgrespro/sqljson/blob/5a8a241/src/test/regress/sql/sql_json.sql#L411 it seems as if you intend to parse all strings in the form of "YYYY-MM-DD" as dates. This is problematic in case a string happens to look like this but is not intended to be a date.
SQL/JSON defines methods in jsonpath, in particularly,
datetime template is also specified in the standard (very rich)
<datetime template> ::= { <datetime template part> }... <datetime template part> ::= <datetime template field> | <datetime template delimiter> <datetime template field> ::= <datetime template year> | <datetime template rounded year> | <datetime template month> | <datetime template day of month> | <datetime template day of year> | <datetime template 12-hour> | <datetime template 24-hour> | <datetime template minute> | <datetime template second of minute> | <datetime template second of day> | <datetime template fraction> | <datetime template am/pm> | <datetime template time zone hour> | <datetime template time zone minute> <datetime template delimiter> ::= <minus sign> | <period> | <solidus> | <comma> | <apostrophe> | <semicolon> | <colon> | <space> <datetime template year> ::= YYYY | YYY | YY | Y <datetime template rounded year> ::= RRRR | RR <datetime template month> ::= MM <datetime template day of month> ::= DD <datetime template day of year> ::= DDD <datetime template 12-hour> ::= HH | HH12 <datetime template 24-hour> ::= HH24 <datetime template minute> ::= MI <datetime template second of minute> ::= SS <datetime template second of day> ::= SSSSS <datetime template fraction> ::= FF1 | FF2 | FF3 | FF4 | FF5 | FF6 | FF7 | FF8 | FF9 <datetime template am/pm> ::= A.M. | P.M. <datetime template time zone hour> ::= TZH <datetime template time zone minute> ::= TZM
Just for the sake of completeness: YAML solves this issue by omitting the quotation marks around the date string (just as JSON integers have no quotations marks around them).
interesting idea, but need to dig the standard first.