Re: Support for jsonpath .datetime() method

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.

Links
1. https://www.postgresql.org/message-id/0409fb42-18d3-bdb7-37ab-d742d5313a40%402ndQuadrant.com

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