OBIEE 11g: How To Display The Difference Between Two TimeStamp Dates As HH:MM:SS?

You have cases where end users need to display the difference between two time stamp date columns in the format HH:MM:SS.
End-users may find it complex to use the TimeStampDiff function to get the desired output.

TimeStampDiff is the out of the box function that returns the total number of specified intervals between two timestamps.

To display the difference between two time stamp date columns in the format HH:MM:SS, use the following syntax:



Cast(TimeStampDiff(SQL_TSI_MINUTE,"Timesheets Measures"."Actual Arrival","Timesheets Measures"."Actual Departure")/(24*60) as VARCHAR(10)) ||'d '|| Cast(Mod(TimeStampDiff(SQL_TSI_MINUTE,"Timesheets Measures"."Actual Arrival","Timesheets Measures"."Actual Departure")/(60), 60) as VARCHAR(10)) ||'h '|| Cast(Mod(TimeStampDiff(SQL_TSI_MINUTE,"Timesheets Measures"."Actual Arrival","Timesheets Measures"."Actual Departure"), 60) as VARCHAR(10))||'m'


An alternative way is to place the function at the database level and use calls to the function from inside OBIEE using the EVALUATE function.

The following is an example on an Oracle database:

1. Create the following function in the Oracle database schema:

CREATE OR REPLACE FUNCTION getTimeDiff (dateBegin IN DATE, dateEnd IN DATE)
RETURN VARCHAR2
IS
TimeDiff NUMBER (10, 5) := 0;
displayTimeDiff VARCHAR2 (15) := '';
dayDiff NUMBER (10, 0) := 0;
hourDiff NUMBER (10, 0) := 0;
minDiff NUMBER (10, 0) := 0;
BEGIN
TimeDiff := TO_NUMBER (dateEnd - dateBegin);
dayDiff := TRUNC (TimeDiff);
hourDiff := TRUNC ( (TimeDiff - dayDiff) * 24);
minDiff := TRUNC ( ( ( (TimeDiff - dayDiff) * 24) - hourDiff) * 60);

displayTimeDiff := dayDiff || 'd ' || hourDiff || 'h ' || minDiff|| 'm';

RETURN displayTimeDiff;
END getTimeDiff;


2. In the Answers report, create a new calculation item as follows:


EVALUATE('getTimeDiff(%1,%2)' as CHAR,"Timesheets Measures"."Actual Arrival","Timesheets Measures"."Actual Departure")