Hi! Thread [1] about support for .datetime() jsonpath method raises a question about standard-conforming parising for Y, YY, YYY and RR datetime template patterns.
According to standard YYY, YY and Y should get higher digits from current year. Our current implementation gets higher digits so that the result is closest to 2020. We currently don't support RR. According to standard RR behavior is implementation-defined and should select marching 4-digit year in the interval [CY - 100; CY + 100], where CY is current year. So, our current implementation of YY is more like RR according to standard. The open question are: 1) Do we like to make our datetime parsing to depend on current timestamp? I guess no. But how to parse one-digit year? If we hardcode constant it would outdate in decade. Thankfully, no one in the right mind wouldn't use Y pattern, but still. 2) How do we like to parse RR? Standard lives us a lot of freedom here. Do we like to parse it as do we parse YY now? It looks reasonable to select a closest matching year. Since PG 13 is going to be released in 2020, our algorithm would be perfect fit at release time. 3) Do we like to change behavior to_date()/to_timestamp()? Or just jsonpath .datetime() and future CAST(... AS ... FORMAT ...) defined in SQL 2016? Attached patch solve the questions above as following. YYY, YY and Y patterns get higher digits from 2020. So, results for Y would become inconsistent since 2030. RR select matching year closest to 2020 as YY does for now. It changes behavior for both to_date()/to_timestamp() and jsonpath .datetime(). Any thoughts? Links 1. https://www.postgresql.org/message-id/CAPpHfdsZgYEra_PeCLGNoXOWYx6iU-S3wF8aX0ObQUcZU%2B4XTw%40mail.gmail.com ------ Alexander Korotkov Postgres Professional: http://www.postgrespro.com The Russian Postgres Company
0001-datetime-years-parsing.patch
Description: Binary data
