Why can I store 24:00:00 in a TIME field?
Frank Hauptlorenz
Why can I store 24:00:00 in a Time field. Is this not the same as 0:0:0?
Why is this possible?
Grüße
Frank
Knut Stolze
> Hello!
>
> Why can I store 24:00:00 in a Time field. Is this not the same as 0:0:0?
> Why is this possible?
Because 24:00:00 is a valid time (at least in Germany). 24:00 is the end of
the day and 00:00 is the beginning of the day. Internally, DB2 treats both
time values as one would expect:
$ db2 "values time('24:00:00') - time('00:00:00')"
1
--------
240000.
1 record(s) selected.
$ db2 "values time('00:00:00') - time('12:00:00')"
1
--------
-120000.
1 record(s) selected.
$ db2 "values time('24:00:00') - time('12:00:00')"
1
--------
120000.
1 record(s) selected.
--
Knut Stolze
Information Integration
IBM Germany / University of Jena
Anton Versteeg
If you substract a second and then add a second it becomes 0:0:0
Frank Hauptlorenz wrote:
--
Anton Versteeg
IBM Netherlands
Frank Hauptlorenz
I don't believe this. I thought 24:00:00 is only spoken but does not exist
in reality. I've checked various
timefields and no one does allow 24:00:00 (Delphi TTime Datatype, MS
Acess...)
Grüße nach Jena,
F.Hauptlorenz
"Knut Stolze" <sto...@de.ibm.com> schrieb im Newsbeitrag
news:b5er96$jjb$1...@fsuj29.rz.uni-jena.de...
Knut Stolze
> Hello Mr. Stolze,
>
> I don't believe this. I thought 24:00:00 is only spoken but does not exist
> in reality. I've checked various
> timefields and no one does allow 24:00:00 (Delphi TTime Datatype, MS
> Acess...)
DB2 treats 24:00 the way I described:
db2 "values timestamp('2002-01-01 24:00:00') + 1 day"
1
--------------------------
2002-01-03-00.00.00.000000
1 record(s) selected.
db2 "values timestamp('2002-01-01 24:00:00') + 0 day"
1
--------------------------
2002-01-02-00.00.00.000000
1 record(s) selected.
I really don't see a problem with that. To further clarify how DB2 treats
24:00:00, have a look at the SQL Reference, section "datetime values:
Time
A time is a three-part value (hour, minute, and second) designating a time of
day under a 24-hour clock. The range of the hour part is 0 to 24. The range
of the other parts is 0 to 59. If the hour is 24, the minute and second
specifications are zero.
The internal representation of a time is a string of 3 bytes. Each byte
consists of 2 packed decimal digits. The first byte represents the hour, the
second byte the minute, and the last byte the second.
The length of a TIME column, as described in the SQLDA, is 8 bytes, which is
the appropriate length for a character string representation of the value.
--CELKO--
It does not exist on the clock. The ISO-8601 Standards start a day at
00:00:00 Hrs, but it is an open interval. Because time is a continuum
you can only approach the end of a duration, but not get there --
remember limits from Calculus?
>> Is this not the same as 00:00:00? <<
(Use two digits for each part of a time.)
No. Try truncating a timestamp to the nearest day. One rule was that
24:00 hour today was 00:00 tomorrow:
'2003-03-21 00:00:00' becomes '2003-03-21'
'2003-03-22 24:00:00' becomes '2003-03-22' <== ????
Various countries disagree on how to do that, and the math was a mess.
Leo Flores
According to Knut Stolze's previous response:
db2 "values timestamp('2002-01-01 24:00:00') + 1 day"
1
--------------------------
2002-01-03-00.00.00.000000
1 record(s) selected.
So, on your example below,
should '2003-03-22
24:00:00' becomes '2003-03-22' <== ????
really be '2003-03-21 24:00:00'
becomes '2003-03-22' <== ????
Hope you understand my question.
Leo Flores
--CELKO--
So, on your example below,
should '2003-03-22 24:00:00' becomes '2003-03-22' <== ????
really be '2003-03-21 24:00:00' becomes '2003-03-22' <== ????
Hope you understand my question. <<
Are you British or American? We did it one way, they did it the
other. If you use truncation, you have things like "24:01 Hrs" which
give you a 25 hour day! If you always round up to the next day, you
do not need 24:00 Hrs on the clock.
Those are just some of MANY reasons there is no 24:00 Hrs.
Frank Hauptlorenz
I did want to know if this is a feature or a bug. I know many apps
where 24:00:00 is not allowed.
Thank you for your thoughts.
Frank
"Knut Stolze" <sto...@de.ibm.com> schrieb im Newsbeitrag
news:b5f1hi$knh$1...@fsuj29.rz.uni-jena.de...
--CELKO--
Time
A time is a three-part value (hour, minute, and second) designating a
time of
day under a 24-hour clock. The range of the hour part is 0 to 24. The
range
of the other parts is 0 to 59. If the hour is 24, the minute and
second specifications are zero. <<
Amazing! They have almost everything about ISO Standard time wrong.
Hours range from 00 to 23; minutes range from 00 to 59 and seconds
range from 00 to 61. The 61 second minute is for leap-seconds when
Atomic clock and UTC are adjusted twice a year. They missed the whoel
idea of the openj interval temporal model.
Leo Flores
I am an American(West Covina California).
I ran the following 7 SQL's DB2 7.1.
Looks like when a Day is added to
24:00, even a zero Day, the 24:00 is
treated as the next Day.
But if a Day is not added to 24:00 the
24:00 is part of the same Day.
So, I guess, here in America, 24:00 is
part of the same Day,
Unless you Add or Subtract a Day,
which makes 24:00 part of the Next Day
or Previous Day.
Hope I figured this one out correctly. But even if I have, I'll forget it.
DB2CLP C:\Program Files\SQLLIB\bin>db2 "values timestamp('2002-01-01 24:00:00') + 1 day"
1
--------------------------
2002-01-03-00.00.00.000000
1 record(s) selected.
DB2CLP C:\Program Files\SQLLIB\bin>db2 "values timestamp('2002-01-01 24:00:00') + 0 day"
1
--------------------------
2002-01-02-00.00.00.000000
1 record(s) selected.
DB2CLP C:\Program Files\SQLLIB\bin>db2 "values timestamp('2002-01-01 24:00:00') - 0 day"
1
--------------------------
2002-01-02-00.00.00.000000
1 record(s) selected.
DB2CLP C:\Program Files\SQLLIB\bin>db2 "values timestamp('2002-01-01 24:00:00') - 1 day"
1
--------------------------
2002-01-01-00.00.00.000000
1 record(s) selected.
DB2CLP C:\Program Files\SQLLIB\bin>db2 "values timestamp('2002-01-01 24:00:00')"
1
--------------------------
2002-01-01-24.00.00.000000
1 record(s) selected.
DB2CLP C:\Program Files\SQLLIB\bin>db2 "values DATE(timestamp('2002-01-01 24:00:00'))"
1
----------
01/01/2002
1 record(s) selected.
DB2CLP C:\Program Files\SQLLIB\bin>db2 "values DATE(timestamp('2002-01-01 24:00:00') + 0 day)"
1
----------
01/02/2002
1 record(s) selected.
Leo Flores
Serge Rielau
semantics to match the standard ;-)
Cheers
Serge
--
Serge Rielau
DB2 UDB SQL Compiler Development
IBM Software Lab, Toronto
Visit DB2 Developer Domain at
http://www7b.software.ibm.com/dmdd/
Paul Vernon
news:c0d87ec0.03032...@posting.google.com...
[...]
> Hours range from 00 to 23; minutes range from 00 to 59 and seconds
> range from 00 to 61. The 61 second minute is for leap-seconds when
> Atomic clock and UTC are adjusted twice a year.
To be precise, the owners of UTC reserve the right to insert (or remove) a
leap-second twice a year. More often than not, they do not actually to do so.
E.g.
http://tycho.usno.navy.mil/leapsec.html
"Since the first leap second in 1972, all leap seconds have been positive and
there were 22 leap seconds in the 27 years to January, 1999."
Regards
Paul Vernon
Business Intelligence, IBM Global Services
James Campbell
<c0d87ec0.03032...@posting.google.com>:
>>> Why can I store 24:00:00 in a Time field [sic]. <<
>
>It does not exist on the clock. The ISO-8601 Standards start a day at
>00:00:00 Hrs, but it is an open interval. Because time is a continuum
>you can only approach the end of a duration, but not get there --
>remember limits from Calculus?
>
However the (admitted) draft version of ISO8601:2000 at
http://lists.ebxml.org/archives/ebxml-core/200104/pdf00005.pdf explicitly
includes 240000 as midnight at the end of a day.
Can you give me a reference for a version that does not allow this?
thanks
James