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
