Hi all,

I have a problem with calculating a duration from two timestamps.

There are two columns in a table "start_ts" and "end_ts" both being of type TIMESTAMP. Now i would like to calculate a duration by subtracting end_ts from start_ts. The result should be put into a column "duration" which is of type TIME.

This is my code :

UPDATE JOB_STATISTICS
SET DURATION=END_TS-START_TS
WHERE DURATION='00:00:00' AND JOB_NAME='...'

I get the following error:
SQL0408N A value is not compatible with the data type of its assignment
target. Target name is "DURATION". SQLSTATE=42821

How can I type-cast the calculation so the result will be of type TIME?

Your could use TIME function. Check out the following:

When subtracting one timestamp from another, the result is a timestamp-duration ( not a time ). To store the result as TIME, some calculations/transformations are necessary.

Try this:


... SET DURATION=TIME('00:00:00') + cast ((END_TS - START_TS ) as decimal(6,0)) ...

Thanks for the replies so far.

I tried the following:

UPDATE JOB_STATISTICS
SET DURATION=TIME('00:00:00') + cast ((END_TS - START_TS ) as decimal(6,0))
WHERE DURATION='00:00:00' AND JOB_NAME='...'


And got this error message:
SQL0413N Overflow occurred during numeric data type conversion.
SQLSTATE=22003


Fiddled around with some variations like "cast ((END_TS - START_TS ) as decimal(10,0)) because the timestamps are of length 10 (was just a guess..). I then got this error message:
SQL0182N An expression with a datetime value or a labeled duration is not
valid. SQLSTATE=42816


Something like this didnt work either:
SET DURATION=TIME(END_TS )- TIME(START_TS )
or
SET DURATION=TIME(END_TS -START_TS )

what is the maximum difference between END_TS and START_TS
... if its more than 24 hours you can't store it in an column of type TIME.


Indeed subtracting two timestamps results in an DEC(20,6) number
consisting of:
yyyymmddhhmmss,zzzzz

where yyyy is the difference of years,
mm is the difference of month, dd of days and so on till zzzzz the difference of microseconds.

if you calculate:
ts1 = ' 2006-07-20-15.31.08.135090' and
ts2 = ' 2006-07-21-15.34.18.135090'

ts2 - ts1 will result in the decimal number
00000001000310,000000
( difference is 0 years, 0 month, 1 day, 0 hours, 3 minutes and 10 seconds )


so, if the maximum difference of the timestamps is less than 24 hours, the maximum value you may receive is

00000000235959,999999
( 23 hours, 59 minutes, 59 seconds and 999999 microseconds )
this number can be cast to a decimal(6,0) which can be interpreted as an time duration and -by added to time 00:00- be trasformed to a TIME-type


if the maximum difference is more than 24 hours, you can't it store in an TIME column

Ah, this makes sense. The duration in my test-environment is indeed longer then 24 hours.

Thank you very much for the detailed analysis.

why dont u chk out the timestampdiff function. chk out more at

http://publib.boulder.ibm.com/infoce...61%6d%70%22%20

db2 values( TIMESTAMPDIFF(4,CHAR(TIMESTAMP('2001-09-29-11.25.42.483219') -TIMESTAMP('2001-09-26-12.07.58.065497'))) )

will give u the difference in minutes which can easily be converted into days/hrs.

regards,
Rahul