Re: [HACKERS] Bug in to_timestamp().

On Thu, Sep 20, 2018, 3:22 AM Alexander Korotkov <a(dot)korotkov(at)postgrespro(dot)ru>
wrote:

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

Agreed, thanks for working on this.

Regards,
Amul

>