How to calculate number of hours or seconds between 2 datetime fields

If you are using DB2, there is a function:

TIMESTAMPDIFF(4,CHAR(TIMESTAMP('2001-09-29-.25.42.483219')
- TIMESTAMP('2001-09-26-12.07.58.065497')))

the first parameter is the interval for the required result:

1=fractions of sec, 2=seconds, 4=minutes etc

Thanks, but this is Sybase database.
Any more thoughts?

I don't know of any Business Objects functions which manipulate datetime / timestamp fields, so I think that you should try to do it at the SQL level by creating an object in the universe.

You could convert a datetime to a character field and substring it to its basic elements. Then you could use basic arithmetic to convert it to an absolute number of seconds, but it is a nuisance particularly if the period can go over days (n.b. leap years).

Brian,

For your information,
The TIMESTAMPDIFF function on DB2 returns an ESTIMATE of the actual difference, due to a bug in the function (at least this existed up to version 7.2)

The actual calculation with the precision in seconds is possible within the report through work-arounds, but since I am sort of on vacation I do not have the exact calculations within reach.. Can publish them next week..

T. Blom
Information analyst
tbl@shimano-eu.com

Step 1:

variable day_check:


For situation where DATE1 = DATE2 this variable is 0, otherwise 1 or higher

Step 2:

variables for calc. seconds after midnight (X):


ditto for DATE2 (Y)

Step 3:

Variable that holds the difference in seconds between the two timestamps. (Z)
This is the tricky bit. If day_check = 0 it is an easy subtraction. If day_check =1 it will be something like:


If day_check = a (a>1), then


The integer value in seconds can be reformatted like:


Example is for minutes:seconds, but can be expanded to include hours...



T. Blom
Information analyst
tbl@shimano-eu.com