Re: Support for jsonpath .datetime() method

On Sat, Sep 14, 2019 at 10:18 PM Alexander Korotkov
<a(dot)korotkov(at)postgrespro(dot)ru> wrote:
> On Tue, Aug 27, 2019 at 5:19 AM Alexander Korotkov
> <a(dot)korotkov(at)postgrespro(dot)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