Re: Support for jsonpath .datetime() method

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.

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