Re: to_timestamp docs - Mailing list pgsql-hackers

Hello,

On Wed, May 1, 2019 at 6:05 PM Bruce Momjian <bruce@momjian.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.

> 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.

-- 
Arthur Zakirov
Postgres Professional: http://www.postgrespro.com
Russian Postgres Company