Re: SQL/JSON in PostgreSQL

On Wed, Mar 8, 2017 at 12:43 AM, Sven R. Kunze <srkunze(at)mail(dot)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 <left paren> [ <JSON datetime template> ] <right paren>
| keyvalue <left paren> <right paren>

<JSON datetime template> ::=
<JSON path string literal>

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.

>
> Regards,
> Sven
>