Lists: | pgsql-bugs |
---|
From: | Dhaval Jaiswal <dhaval(dot)jaiswal(at)enterprisedb(dot)com> |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: to_timestamp error handling. |
Date: | 2009-06-10 14:23:46 |
Message-ID: | c5a53f7b0906100723s7da73249v82140e152eef9ce@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
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
From: | Greg Stark <stark(at)enterprisedb(dot)com> |
---|---|
To: | Dhaval Jaiswal <dhaval(dot)jaiswal(at)enterprisedb(dot)com> |
Cc: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: to_timestamp error handling. |
Date: | 2009-06-10 14:29:55 |
Message-ID: | 4136ffa0906100729q77b03872gd1541fdedd897c34@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
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
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Greg Stark <stark(at)enterprisedb(dot)com> |
Cc: | Dhaval Jaiswal <dhaval(dot)jaiswal(at)enterprisedb(dot)com>, pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: to_timestamp error handling. |
Date: | 2009-06-10 14:42:02 |
Message-ID: | 788.1244644922@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
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
From: | Dhaval Jaiswal <dhaval(dot)jaiswal(at)enterprisedb(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Greg Stark <stark(at)enterprisedb(dot)com>, pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: to_timestamp error handling. |
Date: | 2009-06-10 15:02:45 |
Message-ID: | c5a53f7b0906100802k2ad4a804na9003982d49ab587@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
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
From: | Peter Eisentraut <peter_e(at)gmx(dot)net> |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Cc: | Dhaval Jaiswal <dhaval(dot)jaiswal(at)enterprisedb(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Greg Stark <stark(at)enterprisedb(dot)com> |
Subject: | Re: to_timestamp error handling. |
Date: | 2009-06-10 21:05:49 |
Message-ID: | 200906110005.50810.peter_e@gmx.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
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?
From: | tushar <tushar(dot)ahuja(at)enterprisedb(dot)com> |
---|---|
To: | Peter Eisentraut <peter_e(at)gmx(dot)net> |
Cc: | pgsql-bugs(at)postgresql(dot)org, Dhaval Jaiswal <dhaval(dot)jaiswal(at)enterprisedb(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Greg Stark <stark(at)enterprisedb(dot)com> |
Subject: | Re: to_timestamp error handling. |
Date: | 2009-06-11 05:37:30 |
Message-ID: | 4A30981A.3040102@enterprisedb.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
-----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-----
From: | Bruce Momjian <bruce(at)momjian(dot)us> |
---|---|
To: | Dhaval Jaiswal <dhaval(dot)jaiswal(at)enterprisedb(dot)com> |
Cc: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: to_timestamp error handling. |
Date: | 2010-02-24 19:45:34 |
Message-ID: | 201002241945.o1OJjYI11898@momjian.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
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. +
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Bruce Momjian <bruce(at)momjian(dot)us> |
Cc: | Dhaval Jaiswal <dhaval(dot)jaiswal(at)enterprisedb(dot)com>, pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: to_timestamp error handling. |
Date: | 2010-02-24 20:00:50 |
Message-ID: | 28413.1267041650@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
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
From: | Bruce Momjian <bruce(at)momjian(dot)us> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Dhaval Jaiswal <dhaval(dot)jaiswal(at)enterprisedb(dot)com>, pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: to_timestamp error handling. |
Date: | 2010-02-24 20:44:37 |
Message-ID: | 201002242044.o1OKibO21412@momjian.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
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. +
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Bruce Momjian <bruce(at)momjian(dot)us> |
Cc: | Dhaval Jaiswal <dhaval(dot)jaiswal(at)enterprisedb(dot)com>, pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: to_timestamp error handling. |
Date: | 2010-02-24 21:03:10 |
Message-ID: | 29569.1267045390@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
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
From: | Bruce Momjian <bruce(at)momjian(dot)us> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Dhaval Jaiswal <dhaval(dot)jaiswal(at)enterprisedb(dot)com>, pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: to_timestamp error handling. |
Date: | 2010-02-25 18:32:31 |
Message-ID: | 201002251832.o1PIWVC21892@momjian.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
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. +