Re: to_timestamp error handling.
Hi All,
postgres=# select to_timestamp('20096010','YYYYMMDD');
to_timestamp
---------------------------
2013-12-18 00:00:00+05:30
(1 row)
The month is 60 in my case and it is giving some random value, whereas I am
expecting some error message like date is not valid.
Is it an expected behaviour?
--
Thanks & Regards,
Dhaval Jaiswal
EnterpriseDB
Contact: 732-331-1300 Ext- 2022
+91-20-30589 516 / 494
web: www.enterprisedb.com
On Wed, Jun 10, 2009 at 3:23 PM, Dhaval
Jaiswal<dhaval.jaiswal@enterprisedb.com> wrote:
postgres=# select to_timestamp('20096010','YYYYMMDD');
to_timestamp
---------------------------
2013-12-18 00:00:00+05:30(1 row)
The month is 60 in my case and it is giving some random value, whereas I am
expecting some error message like date is not valid.
I suspect you'll find that the 60th month after the start of 2009 is
in fact december 2013.
--
Gregory Stark
http://mit.edu/~gsstark/resume.pdf
Greg Stark <stark@enterprisedb.com> writes:
On Wed, Jun 10, 2009 at 3:23 PM, Dhaval
Jaiswal<dhaval.jaiswal@enterprisedb.com> wrote:postgres=# select to_timestamp('20096010','YYYYMMDD');
---------------------------
�2013-12-18 00:00:00+05:30
I suspect you'll find that the 60th month after the start of 2009 is
in fact december 2013.
Yeah. I was kind of surprised that CVS HEAD doesn't complain about this
--- I thought we'd tightened up the error checking in to_timestamp.I think it's been occasionally seen as a feature that something like
'2009-02-29' will be read as '2009-03-01', but it's hard to imagine a
real use case for month outside 1-12.
regards, tom lane
Yes, there isn't a use case for a month value outside 1-12, i found this
due a typo.
On Wed, Jun 10, 2009 at 8:12 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Greg Stark <stark@enterprisedb.com> writes:
On Wed, Jun 10, 2009 at 3:23 PM, Dhaval
Jaiswal<dhaval.jaiswal@enterprisedb.com> wrote:postgres=# select to_timestamp('20096010','YYYYMMDD');
---------------------------
2013-12-18 00:00:00+05:30I suspect you'll find that the 60th month after the start of 2009 is
in fact december 2013.Yeah. I was kind of surprised that CVS HEAD doesn't complain about this --- I thought we'd tightened up the error checking in to_timestamp.I think it's been occasionally seen as a feature that something like
'2009-02-29' will be read as '2009-03-01', but it's hard to imagine a
real use case for month outside 1-12.regards, tom lane
--
Thanks & Regards,
Dhaval Jaiswal
EnterpriseDB
Contact: 732-331-1300 Ext- 2022
+91-20-30589 516 / 494
web: www.enterprisedb.com
On Wednesday 10 June 2009 18:02:45 Dhaval Jaiswal wrote:
Yes, there isn't a use case for a month value outside 1-12, i found this
due a typo.
What Would Oracle Do?
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Peter Eisentraut wrote:
On Wednesday 10 June 2009 18:02:45 Dhaval Jaiswal wrote:
Yes, there isn't a use case for a month value outside 1-12, i found this
due a typo.What Would Oracle Do?
Oracle is throwing an error.
--
regards,tushar
http://webeatoracle.com
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.2 (GNU/Linux)
iD8DBQFKMJgafQNodY2PIRoRAu0OAJ9kHpsbKXkYDZ/oqauksfkT8hQvkgCgg0yD
FeL4ixcbWYOHaTZIvgVUArw=
=26y9
-----END PGP SIGNATURE-----
FYI, this behavior now returns:
test=> select to_timestamp('20096010','YYYYMMDD');
to_timestamp
------------------------
2013-12-18 00:00:00-05
(1 row)
which doesn't have the :30 but is still odd. I don't see any value
checking in to_timestamp.
---------------------------------------------------------------------------
Dhaval Jaiswal wrote:
Hi All,
postgres=# select to_timestamp('20096010','YYYYMMDD');
to_timestamp
---------------------------
2013-12-18 00:00:00+05:30
(1 row)
The month is 60 in my case and it is giving some random value, whereas I am
expecting some error message like date is not valid.Is it an expected behaviour?
--
Thanks & Regards,
Dhaval Jaiswal
EnterpriseDB
Contact: 732-331-1300 Ext- 2022
+91-20-30589 516 / 494
web: www.enterprisedb.com
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do
+ If your life is a hard drive, Christ can be your backup. +Bruce Momjian <bruce@momjian.us> writes:
FYI, this behavior now returns:
test=> select to_timestamp('20096010','YYYYMMDD');
to_timestamp
------------------------
2013-12-18 00:00:00-05
(1 row)
which doesn't have the :30 but is still odd.
I don't think the behavior has changed, you're merely checking it in
a different timezone from the OP.
The real question is whether we should throw error for out-of-range
MM (or other fields). I think there are actual use cases for certain
"invalid" inputs, like adding one to the day field without worrying
about end of month. Perhaps there is not a use case for a month value
as far out of range as this, but where would we draw the line?
Anybody know what Oracle's to_timestamp does?
regards, tom lane
Tom Lane wrote:
Bruce Momjian <bruce@momjian.us> writes:
FYI, this behavior now returns:
test=> select to_timestamp('20096010','YYYYMMDD');
to_timestamp
------------------------
2013-12-18 00:00:00-05
(1 row)which doesn't have the :30 but is still odd.
I don't think the behavior has changed, you're merely checking it in
a different timezone from the OP.The real question is whether we should throw error for out-of-range
MM (or other fields). I think there are actual use cases for certain
"invalid" inputs, like adding one to the day field without worrying
about end of month. Perhaps there is not a use case for a month value
as far out of range as this, but where would we draw the line?Anybody know what Oracle's to_timestamp does?
The old thread reported Oracle returned an error;
http://archives.postgresql.org/pgsql-bugs/2009-06/msg00100.php
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do
+ If your life is a hard drive, Christ can be your backup. +Bruce Momjian <bruce@momjian.us> writes:
Tom Lane wrote:
Anybody know what Oracle's to_timestamp does?
The old thread reported Oracle returned an error;
http://archives.postgresql.org/pgsql-bugs/2009-06/msg00100.php
Well, nothing's likely to get done about it for 9.0. Maybe we should
add a TODO item for further tightening of the function's error checking.
regards, tom lane
Tom Lane wrote:
Bruce Momjian <bruce@momjian.us> writes:
Tom Lane wrote:
Anybody know what Oracle's to_timestamp does?
The old thread reported Oracle returned an error;
http://archives.postgresql.org/pgsql-bugs/2009-06/msg00100.phpWell, nothing's likely to get done about it for 9.0. Maybe we should
add a TODO item for further tightening of the function's error checking.
There doesn't seem to be any error checking:
test=> select to_timestamp('20090140','YYYYMMDD');
to_timestamp
------------------------
2009-02-09 00:00:00-05
(1 row)
The odd thing is we seems to do something reasonable for some definition
of reasonable so maybe we just leave it unchanged.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do
+ If your life is a hard drive, Christ can be your backup. +