Timestamp with a value of "2008-07-07 16:41:60"

UDA
N/A

Timestamp with a value of "2008-07-07 16:41:60"

Hi All,

I built a table that contained a field defined as timestamp(0). I loaded the table in 17 batches, and for each batch I used the value:

cast((date (format "YYYY-MM-DD")) as char(10))||' '||time

When I group this field, I get 17 different values back, but one of them has an invalid timestamp of 2008-07-07 16:41:60.

Any idea how this could happen?

Thanks.
5 REPLIES
N/A

Re: Timestamp with a value of "2008-07-07 16:41:60"

Maybe it should be '2008-07-07 10:42:00'
SN
N/A

Re: Timestamp with a value of "2008-07-07 16:41:60"

hi,

Unless hard-coded I dont think the system will supply such a time.

thx
rgs
N/A

Re: Timestamp with a value of "2008-07-07 16:41:60"

The timestamp seconds field may be from 00.000000 to 61.999999 seconds. This is part of the SQL ANSI Standard. Why you ask? To quote Jim Melton: “It is simply to deal with the phenomenon know as leap seconds: occasionally, the earth’s official timekeepers will add one or two seconds to a minute to keep clocks synchronized with sidereal time.”

So the “seconds” value is valid as far as the database is concerned, but maybe not valid for you.

Re: Timestamp with a value of "2008-07-07 16:41:60"

when u use "time" in the select clause, it can return time with seconds ranging from 0-60.
I have tested it in my system.

N/A

Re: Timestamp with a value of "2008-07-07 16:41:60"

The built-in function TIME is a FLOAT which is just formatted to look like a time. As a float it's following rounding rules, thus a value greater than xx:xx:59.50 will be rounded to xx:xx:60
It's not recommended anymore to use TIME.

You wouldn't get that stange result with CURRENT_TIME, which is datatype TIME.

But why don't you simply use CURRENT_TIMESTAMP(0) for an insert into a TIMESTAMP column?

Dieter