Waiting for PostgreSQL 13 – Support for FF1-FF6 and SSSS datetime format patterns

On 16th of September 2019, Alexander Korotkov committed two patches:

First patch:

Support for SSSSS datetime format pattern
 
SQL Standard 2016 defines SSSSS format pattern for seconds past midnight in
jsonpath .datetime() method and CAST (... FORMAT ...) SQL clause.  In our
datetime parsing engine we currently support it with SSSS name.
 
This commit adds SSSSS as an alias for SSSS.  Alias is added in favor of
upcoming jsonpath .datetime() method.  But it's also supported in to_date()/
to_timestamp() as positive side effect.
 
Discussion: https://postgr.es/m/CAPpHfdsZgYEra_PeCLGNoXOWYx6iU-S3wF8aX0ObQUcZU%2B4XTw%40mail.gmail.com
Author: Nikita Glukhov, Alexander Korotkov

and second one:

Support for FF1-FF6 datetime format patterns
 
SQL Standard 2016 defines FF1-FF9 format patters for fractions of seconds in
jsonpath .datetime() method and CAST (... FORMAT ...) SQL clause.  Parsing
engine of upcoming .datetime() method will be shared with to_date()/
to_timestamp().
 
This patch implements FF1-FF6 format patterns for upcoming jsonpath .datetime()
method.  to_date()/to_timestamp() functions will also get support of this
format patterns as positive side effect.  FF7-FF9 are not supported due to
lack of precision in our internal timestamp representation.
 
Extracted from original patch by Nikita Glukhov, Teodor Sigaev, Oleg Bartunov.
Heavily revised by me.
 
Discussion: https://postgr.es/m/-b497-f39a-923d-%402ndQuadrant.com
Discussion: https://postgr.es/m/CAPpHfdsZgYEra_PeCLGNoXOWYx6iU-S3wF8aX0ObQUcZU%2B4XTw%40mail.gmail.com
Author: Nikita Glukhov, Teodor Sigaev, Oleg Bartunov, Alexander Korotkov

Figured I'll write them both at the same time, as they touch the same thing.

First one allows us to write:

$ SELECT now(), to_char(now(), 'SSSSS');
              now              │ to_char 
───────────────────────────────┼─────────
 2019-09-17 16:35:00.539404+02 │ 59700
(1 ROW)

In case you can't immediately see it, 59700 is number of seconds that passed from 00:00:00 today, in my time zone.

Previously we had the same functionality available using SSSS format (one less S), but new way is for standards compliance.

Second patch adds more stuff:

=$ SELECT
    now(),
    to_char(now(), 'FF1') AS ff1,
    to_char(now(), 'FF2') AS ff2,
    to_char(now(), 'FF3') AS ff3,
    to_char(now(), 'FF4') AS ff4,
    to_char(now(), 'FF5') AS ff5,
    to_char(now(), 'FF6') AS ff6;
              now              | ff1 | ff2 | ff3 | ff4  |  ff5  |  ff6   
-------------------------------+-----+-----+-----+------+-------+--------
 2019-09-17 16:38:05.369302+02 | 3   | 36  | 369 | 3693 | 36930 | 369302
(1 ROW)

as you can see FFX return subsecond part of the time, truncated to X digits.

We had previously MS and US:

=$ SELECT
    now(),
    to_char(now(), 'MS') AS ms,
    to_char(now(), 'US') AS us;
              now              | ms  |   us   
-------------------------------+-----+--------
 2019-09-17 16:40:12.336965+02 | 336 | 336965
(1 ROW)

which are, as far as I can tell the same as FF3 (MS) and FF6 (US).

It's nice that we have more options, and having Pg even more standards compliant.

Thanks to all involved.