Re: to_timestamp docs

On Wed, May 1, 2019 at 11:20 PM Arthur Zakirov <a(dot)zakirov(at)postgrespro(dot)ru> wrote:
> Hello,
>
> On Wed, May 1, 2019 at 6:05 PM Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> > Thanks. I think I see the sentence you are thinking of:
> >
> > <function>to_timestamp</function> and <function>to_date</function>
> > skip multiple blank spaces at the beginning of the input string
> > and around date and time values unless the <literal>FX</literal>
> > option is used.
> >
> > However, first, it is unclear what 'skip' means here, i.e., does it mean
> > multiple blank spaces become a single space, or they are ignored.
>
> I worked at to_timestamp some time ago. In this case multiple bank spaces at
> the beginning should be ignored.
>
> > Second, I see inconsistent behaviour around the use of FX for various
> > patterns, e.g.:
> >
> > SELECT to_timestamp('5 1976','FXDD_FXYYYY');
> > to_timestamp
> > ------------------------
> > 1976-01-05 00:00:00-05
>
> Hm, I think strspace_len() is partly to blame here, which is called by
> from_char_parse_int_len():
>
> /*
> * Skip any whitespace before parsing the integer.
> */
> *src += strspace_len(*src);
>
> But even if you remove this line of code then strtol() will eat
> survived whitespaces:
>
> result = strtol(init, src, 10);
>
> Not sure if we need some additional checks here if FX is set.

I'd like to add that this behavior is not new in 12. It was the same before.

> > It seems DD and YYYY (as numerics?) in FX mode eat trailing whitespace,
> > while MON does not? Also, I used these queries to determine it is
> > "trailing" whitespace that "FXMON" controls:
> >
> > SELECT to_timestamp('JUL JUL JUL','MON_FXMON_MON');
> > to_timestamp
> > ---------------------------------
> > 0001-07-01 00:00:00-04:56:02 BC
> >
> > SELECT to_timestamp('JUL JUL JUL','MON_FXMON_MON');
> > ERROR: invalid value " J" for "MON"
> > DETAIL: The given value did not match any of the allowed values for this field.
>
> The problem here is that you need to specify FX only once and at beginning of
> the format string. It is stated in the documentation:
>
> "FX must be specified as the first item in the template."
>
> It works globally (but only for remaining string if you don't put it
> at the beginning)
> and you can set it only once. For example:
>
> =# SELECT to_timestamp('JUL JUL JUL','FXMON_MON_MON');
> ERROR: invalid value " J" for "MON"
> DETAIL: The given value did not match any of the allowed values for this field.

Actually, FX takes effect on subsequent format patterns. This is not
documented, but it copycats Oracle behavior. Sure, normally FX should
be specified as the first item. We could document current behavior or
restrict specifying FX not as first item. This is also not new in 12,
so documenting current behavior is better for compatibility.

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