Thread: two minor issues with date time types

Just two minor issues with timestamps:

Error message is currently:

create table test (ts timestamp);
-- insert an illegal date:
insert into test values ('20021131');
invalid input syntax for timestamp: "20021131"

SQL92 and 99 say it should be:

data exception-datetime field overflow

No big deal, and it doesn't matter to me if it gets changed or not really,
just FYI.



The other issue is that the ranges allowed by SQL spec for timezone are
-12:59 to +1300

but postgresql currently allows numbers outside that range.


create table test (tm time);
insert into test values ('12:00 +1359');
INSERT 17172 1
insert into test values ('12:00 +1360');
ERROR:  invalid input syntax for time: "12:00 +1360"
insert into test values ('12:00 -1359');
INSERT 17175 1
insert into test values ('12:00 -1400');
ERROR:  invalid input syntax for time: "12:00 -1400"

Is there a reason to allow +/-1359 (i.e. the international standards
changed after the SQL spec was written?) when the spec is pretty clear
it's -1259 to +1300?

On Thu, 14 Aug 2003, scott.marlowe wrote:

> Just two minor issues with timestamps:
>
> Error message is currently:
>
> create table test (ts timestamp);
> -- insert an illegal date:
> insert into test values ('20021131');
> invalid input syntax for timestamp: "20021131"
>
> SQL92 and 99 say it should be:
>
> data exception-datetime field overflow
>
> No big deal, and it doesn't matter to me if it gets changed or not really,
> just FYI.

I don't think that's intended to be the textual error message.
I believe that's supposed to indicate which SQLSTATE is generated.

Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> On Thu, 14 Aug 2003, scott.marlowe wrote:
>> insert into test values ('20021131');
>> invalid input syntax for timestamp: "20021131"
>>
>> SQL92 and 99 say it should be:
>> data exception-datetime field overflow

> I don't think that's intended to be the textual error message.
> I believe that's supposed to indicate which SQLSTATE is generated.

Right, but we're not generating that SQLSTATE --- it's 22007 which is
"invalid datetime format".  Because the datetime code is designed to
accept a rather wide variety of input formats, you could probably
quibble about which code is really more appropriate for this error and
related ones --- did the user really intend you to interpret that number
as yyyymmdd, or was his typo more basic?  (If there are additional
fields following that you haven't looked at yet, it's not exactly
cut-and-dried IMHO.)  Still, there are cases like '2002-11-31' where
"datetime field overflow" is clearly more correct.

The datetime code is set up in a way that doesn't really allow any easy
distinction to be made, though --- IIRC, DecodeDateTime returns -1 to
the guy who actually does the ereport(), and this would cover situations
including bad syntax, field out of range, and maybe other problems.

The simplest fix might be to make the parse/decode subroutines return
different negative numbers for the cases we need to distinguish (looks
like there are at least three relevant SQLSTATEs in the spec).  Anyone
feel like working on that?

            regards, tom lane