Horton

Re: Support for jsonpath .datetime() method

Alexander Korotkov <a.korotkov@postgrespro.ru>

View thread View on postgr.es
From: Alexander Korotkov <a.korotkov@postgrespro.ru>
To: Thomas Munro <thomas.munro@gmail.com>
Cc: Andrew Dunstan <andrew.dunstan@2ndquadrant.com>, Nikita Glukhov <n.gluhov@postgrespro.ru>, Peter Eisentraut <peter.eisentraut@2ndquadrant.com>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>, Liudmila Mantrova <l.mantrova@postgrespro.ru>, Anastasia Lubennikova <lubennikovaav@gmail.com>
Date: 2019-08-27T02:19:00Z
Lists: pgsql-hackers

Attachments

On Mon, Aug 19, 2019 at 1:29 AM Alexander Korotkov
<a.korotkov@postgrespro.ru> wrote:
> If no objections, Nikita and me will work on revised patchset based on
> this proposal.

Revised patchset is attached.  It still requires some polishing.  But
the most doubtful part is handling of RR, YYY, YY and Y.

Standard requires us to complete YYY, YY and Y with high digits from
current year.  So, if YY matches 99, then year should be 2099, not
1999.

For RR, standard requirements are relaxed.  Implementation may choose
matching year from range [current_year - 100; current_year + 100].  It
looks reasonable to handle RR in the same way we currently handle YY:
select appropriate year in [1970; 2069] range.  It seems like we
select this range to start in the same point as unix timestamp.  But
nowadays it still looks reasonable: it's about +- 50 from current
year.  So, years close to the current one are likely completed
correctly.  In Oracle RR returns year in [1950; 1949] range.  So, it
seems to be designed near 2000 :). I don't think we need to copy this
behavior.

Handling YYY and YY in standard way seems quite easy.  We can complete
them as 2YYY and 20YY.  This should be standard conforming till 2100.

But handling Y looks problematic.  Immutable way of handling this
would work only for decade.  Current code completes Y as 200Y and it
looks pretty "outdated" now in 2019.  Using current real year would
make conversion timestamp-dependent.  This property doesn't look favor
for to_date()/to_timestamp() and unacceptable for immutable jsonpath
functions (but we can forbid using Y pattern there).  Current patch
complete Y as 202Y assuming v13 will be released in 2020.  But I'm not
sure what is better solution here.  The bright side is that I haven't
seen anybody use Y patten in real life :)





------
Alexander Korotkov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

Commits

  1. Allow datetime values in JsonbValue

  2. Error suppression support for upcoming jsonpath .datetime() method

  3. Implement jsonpath .datetime() method

  4. Implement parse_datetime() function

  5. Implement standard datetime parsing mode

  6. Support for SSSSS datetime format pattern

  7. Support for FF1-FF6 datetime format patterns