Re: [HACKERS] Bug in to_timestamp().

On Wed, Sep 19, 2018 at 1:38 PM amul sul <sulamul(at)gmail(dot)com> wrote:
> On Wed, Sep 19, 2018 at 3:51 PM amul sul <sulamul(at)gmail(dot)com> wrote:
> >
> > On Wed, Sep 19, 2018 at 2:57 PM Alexander Korotkov
> [...]
> >
> > With this patch, to_date and to_timestamp behaving differently, see this:
> >
> > edb=# SELECT to_date('18 12 2011', 'xDDxMMxYYYY');
> > to_date
> > --------------------
> > 18-DEC-11 00:00:00
> > (1 row)
> >
> > edb=# SELECT to_timestamp('18 12 2011', 'xDDxMMxYYYY');
> > to_timestamp
> > ---------------------------
> > 08-DEC-11 00:00:00 -05:00 <=========== Incorrect output.
> > (1 row)
> >
> Sorry, this was wrong info -- with this patch, I had some mine trial changes.
>
> Both to_date and to_timestamp behaving same with your patch -- the
> wrong output, we are expecting that?
>
> postgres =# SELECT to_date('18 12 2011', 'xDDxMMxYYYY');
> to_date
> ------------
> 2011-12-08
> (1 row)
>ma
> postgres =# SELECT to_timestamp('18 12 2011', 'xDDxMMxYYYY');
> to_timestamp
> ------------------------
> 2011-12-08 00:00:00-05
> (1 row)

It's hard to understand whether it was expected, because it wasn't
properly documented. More important that it's the same behavior we
have before cf984672, and purpose of cf984672 was not to change this.

But from the code comments, it's intentional. If you put digits or
text characters into format string, you can skip non-separator input
string characters. For instance you may do.

# SELECT to_date('y18y12y2011', 'xDDxMMxYYYY');
to_date
------------
2011-12-18
(1 row)

It's even more interesting that letters and digits are handled in
different manner.

# SELECT to_date('01801202011', 'xDDxMMxYYYY');
ERROR: date/time field value out of range: "01801202011"
Time: 0,453 ms

# SELECT to_date('01801202011', '9DD9MM9YYYY');
to_date
------------
2011-12-18
(1 row)

So, letters in format string doesn't allow you to extract fields at
particular positions of digit sequence, but digits in format string
allows you to. That's rather undocumented, but from the code you can
get that it's intentional. Thus, I think it would be nice to improve
the documentation here. But I still propose to commit the patch I
propose to bring back unintentional behavior change in cf984672.

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