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.

OBIEE and Oracle INTERVAL DAY(3) TO SECOND(2)

How can I get the time difference from an INTERVAL DAY TO SECOND column?

Push the requirement back to the database if possible and we will use a 'trick' of SQL and dates to return a number result as if we had subtracted one date from the other. I'll leave the different calculations you may need to get the difference in hours etc... What I was interested in was the difference in seconds.

Assuming that the column in question is RUN_DURATION and has been defined as INTERVAL DAY(3) TO SECOND(2)

Aside/hint: We can add an interval to a date and the answer is date.

We will add the interval to a date and then subtract the date, yes I know it sounds like one of those trick mathematical quizzes that children are so fond of.

our RUN_DURATION is wrapped as follows

(SYSDATE+RUN_DURATION-SYSDATE)*86400 AS RUN_DURATION

The answer is given as the difference between two dates as standard in Oracle where 12 hours is 0.5 of a day. Multiplying the answer by 86400 gives me the answer in seconds.

Working with date differences (TIMESTAMPDIFF)

TIMESTAMPDIFF

Unlike in the Oracle database the difference between two dates is not as simple as subtracting one date from the other (oh if only....), but we do have a system that is actually flexible in other interesting ways.

Don't forget, OBIEE at the business modelling level and in answers has no concept of the type of database your data is coming from, it could be one database or multiple. OBIEE will translate the date calculations, where possible, and push back down into the database layer if the date calculation is done in the repository.

The syntax is:

 TimestampDiff(interval, timestamp1, timestamp2)

So where's the flexibility.... it is in the interval parameter.

We can ask for the difference between the two dates in differing units of

• SQL_TSI_SECOND, 
• SQL_TSI_MINUTE, 
• SQL_TSI_HOUR, 
• SQL_TSI_DAY, 
• SQL_TSI_WEEK, 
• SQL_TSI_MONTH, 
• SQL_TSI_QUARTER, 
• SQL_TSI_YEAR

For example 

TimestampDiff(SQL_TSI_MONTH, TIMESTAMP'1999-07-31 00:00:00', TIMESTAMP'1999-12-31 00:00:00') 

would give you 5