Re: Support for jsonpath .datetime() method

On 2019-07-24 00:48, Nikita Glukhov wrote:
> It seems that our YY works like RR should:
>
> SELECT to_date('69', 'YY');
> to_date
> ------------
> 2069-01-01
> (1 row)
>
> SELECT to_date('70', 'YY');
> to_date
> ------------
> 1970-01-01
> (1 row)
>
> But by the standard first two digits of current year should be used in YY.

Is this behavior even documented anywhere in our documentation? I
couldn't find it. What's the exact specification of what it does in
these cases?

> So it's unclear what we should do:
> - implement YY and RR strictly following the standard only in .datetime()
> - fix YY implementation in to_date()/to_timestamp() and implement RR
> - use our non-standard templates in .datetime()

I think we definitely should try to use the same template system in both
the general functions and in .datetime(). This might involve some
compromises between existing behavior, Oracle behavior, SQL standard.
So far I'm not worried: If you're using two-digit years like above,
you're playing with fire anyway. Also some of the other cases like
dealing with trailing spaces are probably acceptable as slight
incompatibilities or extensions.

We should collect a list of test cases that illustrate the differences
and then work out how to deal with them.

--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services