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