Thread: 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=3D# select to_timestamp('20096010','YYYYMMDD');
>
> =A0=A0=A0=A0=A0=A0 to_timestamp
> ---------------------------
> =A02013-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.
--=20
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: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
>
--
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.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. 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. +