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

[bainmath]

Hi
Any tips on how to calculate number of hours or seconds between 2 datetime fields?
Thanks!

 

[BrianTyler]

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

 

[bainmath]

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

 

[BrianTyler]

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).

 

[blom0344]

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

 

[blom0344]

Step 1:

variable day_check:

=DaysBetween(<DATE1> ,<DATE2>)

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

Step 2:

variables for calc. seconds after midnight (X):

 =ToNumber(SubStr(FormatDate(<DATE1> ,"HHmmss") ,1 ,2))*3600+ToNumber(SubStr(FormatDate(<DATE1> ,"HHmmss") ,3 ,2))*60+ToNumber(SubStr(FormatDate(<DATE1> ,"HHmmss") ,5 ,2))

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:

 (86400-X+Y)

If day_check = a (a>1), then

 ((a-1)*86400)+ 86400-X+Y

The integer value in seconds can be reformatted like:

 =FormatNumber(Truncate((<Z/60) ,0) , "00") + ":" + FormatNumber(Mod(<Z> ,60) ,"00")

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



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