How to convert time stamp to hours

User_CEU9I

Hi Pedro,

I tried with Hour/minute/second functions. But unable to convert min to hours..I tried 7 min to convert into hours like 7/60. its giving 0 because of date column.

I have used the following formula to convert into hours:

For eg:

Convert 2.88 hours to hh:mm:ss

• 2.88 hours can be broken down to 2 hours plus 0.88 hours - 2 hours
• 0.88 hours * 60 minutes/hour = 52.8 minutes - 52 minutes
• 0.8 minutes * 60 seconds/minute = 48 seconds - 48 seconds
• 02:52:48

Please let me know how to convert this by using HOUR/MINUTE/SECOND functions in obiee.

Regards,

Ram.

Pedro F

You have the all logic there, you just had to work the other way around.

If you have a date such as 01/01/2016 02:52:48, you can simply do:

HOUR(date) = 2

MINUTE(date) = 52*60 (seconds in a minute) + SECOND(DATE) = 48 = 3168

Divide that value by 3600 (seconds in a hour) and you get 3168/3600 = 0.88

Finally, just put everything together 2+0.88 = 2.88 and you get the value you want and can do all the comparisons you want as well.