Thread: Re: to_timestamp error handling.

Re: to_timestamp error handling.

From
Dhaval Jaiswal
Date:
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

Re: to_timestamp error handling.

From
Greg Stark
Date:
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

Re: to_timestamp error handling.

From
Tom Lane
Date:
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

Re: to_timestamp error handling.

From
Dhaval Jaiswal
Date:
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

Re: to_timestamp error handling.

From
Peter Eisentraut
Date:
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?

Re: to_timestamp error handling.

From
tushar
Date:
-----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-----

Re: to_timestamp error handling.

From
Bruce Momjian
Date:
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. +

Re: to_timestamp error handling.

From
Tom Lane
Date:
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

Re: to_timestamp error handling.

From
Bruce Momjian
Date:
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. +

Re: to_timestamp error handling.

From
Tom Lane
Date:
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

Re: to_timestamp error handling.

From
Bruce Momjian
Date:
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. +