Re: Support for jsonpath .datetime() method

On 7/24/19 4:25 PM, Peter Eisentraut wrote:
> 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().

Agreed. It's too hard to maintain otherwise.

> 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.

My instict wouyld be to move as close as possible to the standard,
especially if the current behaviour isn't documented.

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

Agreed.

cheers

andrew

--
Andrew Dunstan https://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services