On Thu, Aug 1, 2019 at 1:31 PM Thomas Munro <thomas.mu...@gmail.com> wrote:
> On Sat, Jul 27, 2019 at 2:43 AM Andrew Dunstan
> <andrew.duns...@2ndquadrant.com> wrote:
> > On 7/23/19 6:48 PM, Nikita Glukhov wrote:
> > > Some concrete pieces of review:
> > >> + <row>
> > >> + <entry><literal>FF1</literal></entry>
> > >> + <entry>decisecond (0-9)</entry>
> > >> + </row>
> > >>
> > >> Let's not use such weird terms as "deciseconds". We could say
> > >> "fractional seconds, 1 digit" etc. or something like that.
> > > And what about "tenths of seconds", "hundredths of seconds"?
> >
> > Yes, those are much better.
>
> I've moved this to the September CF, still in "Waiting on Author" state.
I'd like to summarize differences between standard datetime parsing
and our to_timestamp()/to_date().
1) Standard defines much less datetime template parts. Namely it defines:
YYYY | YYY | YY | Y
RRRR | RR
MM
DD
DDD
HH | HH12
HH24
MI
SS
SSSSS
FF1 | FF2 | FF3 | FF4 | FF5 | FF6 | FF7 | FF8 | FF9
A.M. | P.M.
TZH
TZM
We support majority of them and much more. Incompatibilities are:
* SSSS (our name is SSSSS),
* We don't support RRRR | RR,
* Our handling of YYYY | YYY | YY | Y is different. What we have
here is more like RRRR | RR in standard (Nikita explained that
upthread [1]),
* We don't support FF[1-9]. FF[1-6] are implemented in patch. We
can't support FF[7-9], because our binary representation of timestamp
datatype don't have enough of precision.
2) Standard defines only following delimiters: <minus sign>, <period>,
<solidus>, <comma>, <apostrophe>, <semicolon>, <colon>, <space>. And
it requires strict matching of separators between template and input
strings. We don't do so either in FX or non-FX mode.
For instance, we allow both to_date('2019/12/31', 'YYYY-MM-DD') and
to_date('2019/12/31', 'FXYYYY-MM-DD'). But according to standard this
date should be written only as '2019-12-31' to match given template
string.
3) Standard prescribes recognition of digits according to \p{Nd}
regex. \p{Nd} matches to "a digit zero through nine in any script
except ideographic scripts". As far as I remember, we currently do
recognize only ASCII digits.
4) For non-delimited template parts standard requires matching to
digit sequences of lengths between 1 and maximum number of characters
of that template part. We don't always do so. For instance, we allow
more than 4 digits to correspond to YYYY, more than 3 digits to
correspond to YYY and so on.
# select to_date('2019-12-31', 'YYY-MM-DD');
to_date
------------
2019-12-31
(1 row)
Links.
1.
https://www.postgresql.org/message-id/d6efab15-f3a4-40d6-8ddb-6fd8f64cbc08%40postgrespro.ru
------
Alexander Korotkov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
