Re: Support for jsonpath .datetime() method

On Mon, Sep 16, 2019 at 10:05 PM Alexander Korotkov
<a.korotkov@postgrespro.ru> wrote:
> On Sat, Sep 14, 2019 at 10:18 PM Alexander Korotkov
> <a.korotkov@postgrespro.ru> wrote:
> > On Tue, Aug 27, 2019 at 5:19 AM Alexander Korotkov
> > <a.korotkov@postgrespro.ru> wrote:
> > > Revised patchset is attached.  It still requires some polishing.  But
> > > the most doubtful part is handling of RR, YYY, YY and Y.
> > >
> > > Standard requires us to complete YYY, YY and Y with high digits from
> > > current year.  So, if YY matches 99, then year should be 2099, not
> > > 1999.
> > >
> > > For RR, standard requirements are relaxed.  Implementation may choose
> > > matching year from range [current_year - 100; current_year + 100].  It
> > > looks reasonable to handle RR in the same way we currently handle YY:
> > > select appropriate year in [1970; 2069] range.  It seems like we
> > > select this range to start in the same point as unix timestamp.  But
> > > nowadays it still looks reasonable: it's about +- 50 from current
> > > year.  So, years close to the current one are likely completed
> > > correctly.  In Oracle RR returns year in [1950; 1949] range.  So, it
> > > seems to be designed near 2000 :). I don't think we need to copy this
> > > behavior.
> > >
> > > Handling YYY and YY in standard way seems quite easy.  We can complete
> > > them as 2YYY and 20YY.  This should be standard conforming till 2100.
> > >
> > > But handling Y looks problematic.  Immutable way of handling this
> > > would work only for decade.  Current code completes Y as 200Y and it
> > > looks pretty "outdated" now in 2019.  Using current real year would
> > > make conversion timestamp-dependent.  This property doesn't look favor
> > > for to_date()/to_timestamp() and unacceptable for immutable jsonpath
> > > functions (but we can forbid using Y pattern there).  Current patch
> > > complete Y as 202Y assuming v13 will be released in 2020.  But I'm not
> > > sure what is better solution here.  The bright side is that I haven't
> > > seen anybody use Y patten in real life :)
> >
> > Revised patchset is attached.  It adds and adjusts commit messages,
> > comments and does other cosmetic improvements.
> >
> > I think 0001 and 0002 are well reviewed already.  And these patches
> > are usable not only for jsonpath .datetime(), but contain improvements
> > for existing to_date()/to_timestamp() SQL functions.  I'm going to
> > push these two if no objections.
>
> Those two patches are pushed.  Just before commit I've renamed
> deciseconds to "tenths of seconds", sentiseconds to "hundredths of
> seconds" as discussed before [1].
>
> The rest of patchset is attached.

I've reordered the patchset.  I moved the most debatable patch, which
introduces RRRR and RR and changes parsing of YYY, YY and Y to the
end.  I think we have enough of time in this release cycle to decide
whether we want this.

Patches 0001-0005 looks quite mature for me.  I'm going to push this
if no objections.  After pushing them, I'm going to start discussion
related to RR, YY and friends in separate thread.

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