Re: [HACKERS] Bug in to_timestamp().

On Thu, Sep 20, 2018, 3:22 AM Alexander Korotkov <a.korotkov@postgrespro.ru>
wrote:

> On Wed, Sep 19, 2018 at 1:38 PM amul sul <sulamul@gmail.com> wrote:
> > On Wed, Sep 19, 2018 at 3:51 PM amul sul <sulamul@gmail.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.
>

Agreed, thanks for working on this.

Regards,
Amul

>