Re: Support for jsonpath .datetime() method

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.

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