All Forums UDA
l0ki99 1 post Joined 02/05
11 Jul 2008
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))||' '||timeWhen 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.

ywjcjj 36 posts Joined 06/08
22 Jul 2008

Maybe it should be '2008-07-07 10:42:00'

SN 77 posts Joined 01/07
22 Jul 2008

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

rgs 106 posts Joined 02/07
22 Jul 2008

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.

mihir_ray07 10 posts Joined 10/08
14 Oct 2008

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.

dnoeth 4628 posts Joined 11/04
14 Oct 2008

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:60It'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


You must sign in to leave a comment.