QUESTION:
My customer is trying to calculate elapsed time by taking the difference
between two columns defined as time columns. We are getting a result
from the query that shows elapsed time w/o decimal number. For
example:
column 1 column 2
11:30:00 11:45:00 will yield 1500 as the result.
We would like 1500 to be 15.00 or 15:00 to delineate between hours,
minutes, and seconds. The following is an example of the SQL query
that we are using:
SELECT (column_1 - column_2) from table_name
column_1 is defined as datatype time
column_2 is defined as datatype time
We also tried
SELECT CHAR((column_1 - column_2)) from table_name and ended up with an
SQL error that indicated we could not convert the data. It would not
recognize the subtraction. SQL0171N,SQLSTATE=53015
---------- ---------- ---------- --------- ---------- ----------
A: The expression that subtracts two time values results in a datatype
called a "time duration" that is internally considered to be format
decimal(6,0). There are few scallar functions that work with
this format, but the HOUR, MINUTE, and SECOND functions do
work with it.
First I convert my timestamp field to a time field with the
TIME() scallar function. Then I do the subtraction, and
the result is a "time duration":
select name, current time, ctime, time(ctime),
current time - time(ctime)
from sysibm.systables
where creator = ;
NAME 2 CTIME 4 5
------------------ -------- -------------------------- -------- --------
MYTABLE 17:24:02 1994-11-08-11.40.55.117510 11:40:55 54307.
MYTABLE2 17:24:02 1994-11-08-12.36.37.924344 12:36:37 44725.
The HOUR, MINUTE, and SECOND scallar functions will strip out
that portion of the duration field; the result is a long integer:
select name, hour( current time - time(ctime)),
minute(current time - time(ctime)),
second(current time - time(ctime))
from sysibm.systables
where creator = ;
NAME 2 3 4
------------------ ----------- ----------- -----------
MYTABLE 5 43 7
MYTABLE2 4 47 25
---------- ---------- ---------- --------- ---------- ----------
This item was created from library item Q675135 FDZDM
Additional search words:
ARITHMETIC DATE DB2 DB26000 FDZDM FORMAT FORMATTED IX JAN95 OZNEW
RISCDB2 RISCSYSTEM SOFTWARE TIME TIMER 6000
WWQA: ITEM: RTA000051585 ITEM: RTA000051585
Dated: 01/1995 Category: RISCDB2
This HTML file was generated 99/06/24~12:43:19
Comments or suggestions?
Contact us