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(dot)jaiswal(at)enterprisedb(dot)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(at)enterprisedb(dot)com> writes:
> On Wed, Jun 10, 2009 at 3:23 PM, Dhaval
> Jaiswal<dhaval(dot)jaiswal(at)enterprisedb(dot)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(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Greg Stark <stark(at)enterprisedb(dot)com> writes:
> > On Wed, Jun 10, 2009 at 3:23 PM, Dhaval
> > Jaiswal<dhaval(dot)jaiswal(at)enterprisedb(dot)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(at)momjian(dot)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(at)momjian(dot)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(at)momjian(dot)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(at)momjian(dot)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(at)momjian(dot)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(at)momjian(dot)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(at)momjian(dot)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. +