Decimal difference to hours and minutes in OBIEE

Just has one of those issues of different interpretations of the same item by different people in different ways.

Actually, if I'm honest, I'm glad someone's out there manually checking all the hard work we put in here.

And what is this magical value that causes so much confusion - the difference between two time values displayed as a number of hours. Does the value 5.08 mean a. five hours and eight hundredths of an hour, or b. five hours and eight minutes.

Turns out it's we thought it was the first - five hours and eight hundredths of an hour and (some of) the users thought it was five hours and eight minutes.

In the interests of sanity it was decided that a disclaimer should be put against the report in question to clarify the issue and all metadata altered to definitively reflect that this was an decimal representation of hours and fractions thereof.

But the question arose - how difficult is it to show the value 5.08 as hours and minutes.

To derive 5.08 in the database we had subtracted the start date from the end date and multiplied the result by 24 giving us the difference in hours. Oracle does offer us the opportunity to do this calculation and have the result as an interval (day-to-second), but that gets stuck in the database hence the numeric difference in hours.

Thinking the solution through it is immediately apparent the first number is the hours regardless of what comes after the decimal point. For this we can use the FLOOR function which returns 5.

FLOOR(5.08) = 5

calculating minutes is working with what's left after we remove the hours.

5.08 - FLOOR(5.08) = 0.08

which in minutes is

60*0.08 = 4.8

Which is very different to the 8 minutes assumed by some users. We could carry on and work out the seconds implied by the 0.8 of a minute, but for the purpose of this discussion, rounding the result is sufficient. Putting the last piece together gives

ROUND(60*(5.08 - FLOOR(5.08)))

so sticking it all together to make it look pretty means we'll have to cast our numbers to CHAR

CAST(FLOOR(5.08) AS CHAR) || ' hours and ' || CAST(ROUND(60*(5.08 - FLOOR(5.08))) AS CHAR) || ' minutes'

Problem solved.