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