Thread: BUG #6231: weird to_timestamp behaviour with out of range values

The following bug has been logged online:

Bug reference:      6231
Logged by:          Henk Enting
Email address:      h.d.enting@mgrid.net
PostgreSQL version: 9.1.1
Operating system:   linux x86_64
Description:        weird to_timestamp behaviour with out of range values
Details:

I would expect the to_timestamp function to return an error when I feed it
out of range values, e.g. months > 13 and days > 31. Instead it seems to add
the surplus to the timestamp and then return it.

E.g. 21-21 becomes sept. 22th the next year.

psql output:

postgres=# select to_timestamp('2011-21-21', 'YYYY-MM-DD');
      to_timestamp
------------------------
 2012-09-22 00:00:00+02
(1 row)

postgres=# select to_timestamp('2011-21-42', 'YYYY-MM-DD');
      to_timestamp
------------------------
 2012-10-13 00:00:00+02
(1 row)

"Henk Enting" <h.d.enting@mgrid.net> writes:
> I would expect the to_timestamp function to return an error when I feed it
> out of range values, e.g. months > 13 and days > 31. Instead it seems to add
> the surplus to the timestamp and then return it.

What is your reason for using to_timestamp at all?  The timestamp input
converter is perfectly capable of dealing with standard formats like
yyyy-mm-dd, and it does what most people expect in the way of data
validation checks.

            regards, tom lane

On Wed, Sep 28, 2011 at 11:43 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Henk Enting" <h.d.enting@mgrid.net> writes:
>> I would expect the to_timestamp function to return an error when I feed =
it
>> out of range values, e.g. months > 13 and days > 31. Instead it seems to=
 add
>> the surplus to the timestamp and then return it.
>
> What is your reason for using to_timestamp at all? =A0The timestamp input
> converter is perfectly capable of dealing with standard formats like
> yyyy-mm-dd, and it does what most people expect in the way of data
> validation checks.

Well, you might want to insist that the input is in some particular
format, rather than just "whatever the input function will accept"...

--=20
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Thu, Sep 29, 2011 at 5:39 PM, Robert Haas <robertmhaas@gmail.com> wrote:

> On Wed, Sep 28, 2011 at 11:43 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > "Henk Enting" <h.d.enting@mgrid.net> writes:
> >> I would expect the to_timestamp function to return an error when I feed
> it
> >> out of range values, e.g. months > 13 and days > 31. Instead it seems to
> add
> >> the surplus to the timestamp and then return it.
> >
> > What is your reason for using to_timestamp at all?  The timestamp input
> > converter is perfectly capable of dealing with standard formats like
> > yyyy-mm-dd, and it does what most people expect in the way of data
> > validation checks.
>
> Well, you might want to insist that the input is in some particular
> format, rather than just "whatever the input function will accept"...
>
Exactly. But I probably can rely on the input function and set the datastyle
parameter to make sure I get the right dates (e.g. in our case 07-04-2011is
in april, not in july).
But still, I think the to_timestamp should throw an error if I put in
something like '21-21-2011'.

Best Regards,

Henk Enting

* Henk Enting (h.d.enting@mgrid.net) wrote:
> But still, I think the to_timestamp should throw an error if I put in
> something like '21-21-2011'.

I agree completely, this is a pretty big bug in my opinion.  We don't
accept invalid or garbage timestamps in the input function, I don't see
any reason why we should be allowing it in to_timestamp().  If the
values at the 'MM' location are outside of the valid range, we should be
throwing an error.

    Thanks,

        Stephen