Re: Support for jsonpath .datetime() method

On Thu, Aug 1, 2019 at 1:31 PM Thomas Munro <thomas.munro@gmail.com> wrote:
> On Sat, Jul 27, 2019 at 2:43 AM Andrew Dunstan
> <andrew.dunstan@2ndquadrant.com> wrote:
> > On 7/23/19 6:48 PM, Nikita Glukhov wrote:
> > > Some concrete pieces of review:
> > >> +       <row>
> > >> +        <entry><literal>FF1</literal></entry>
> > >> +        <entry>decisecond (0-9)</entry>
> > >> +       </row>
> > >>
> > >> Let's not use such weird terms as "deciseconds".  We could say
> > >> "fractional seconds, 1 digit" etc. or something like that.
> > > And what about "tenths of seconds", "hundredths of seconds"?
> >
> > Yes, those are much better.
>
> I've moved this to the September CF, still in "Waiting on Author" state.

I'd like to summarize differences between standard datetime parsing
and our to_timestamp()/to_date().

1) Standard defines much less datetime template parts.  Namely it defines:
YYYY | YYY | YY | Y
RRRR | RR
MM
DD
DDD
HH | HH12
HH24
MI
SS
SSSSS
FF1 | FF2 | FF3 | FF4 | FF5 | FF6 | FF7 | FF8 | FF9
A.M. | P.M.
TZH
TZM

We support majority of them and much more.  Incompatibilities are:
 * SSSS (our name is SSSSS),
 * We don't support RRRR | RR,
 * Our handling of YYYY | YYY | YY | Y is different.  What we have
here is more like RRRR | RR in standard (Nikita explained that
upthread [1]),
 * We don't support FF[1-9].  FF[1-6] are implemented in patch.  We
can't support FF[7-9], because our binary representation of timestamp
datatype don't have enough of precision.

2) Standard defines only following delimiters: <minus sign>, <period>,
<solidus>, <comma>, <apostrophe>, <semicolon>, <colon>, <space>.  And
it requires strict matching of separators between template and input
strings.  We don't do so either in FX or non-FX mode.

For instance, we allow both to_date('2019/12/31', 'YYYY-MM-DD') and
to_date('2019/12/31', 'FXYYYY-MM-DD').  But according to standard this
date should be written only as '2019-12-31' to match given template
string.

3) Standard prescribes recognition of digits according to \p{Nd}
regex.  \p{Nd} matches to "a digit zero through nine in any script
except ideographic scripts".  As far as I remember, we currently do
recognize only ASCII digits.

4) For non-delimited template parts standard requires matching to
digit sequences of lengths between 1 and maximum number of characters
of that template part.  We don't always do so.  For instance, we allow
more than 4 digits to correspond to YYYY, more than 3 digits to
correspond to YYY and so on.

# select to_date('2019-12-31', 'YYY-MM-DD');
  to_date
------------
 2019-12-31
(1 row)

Links.

1. https://www.postgresql.org/message-id/d6efab15-f3a4-40d6-8ddb-6fd8f64cbc08%40postgrespro.ru

------
Alexander Korotkov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company