Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

How to convert time stamp to hours

Received Response
506
Views
12
Comments
2»

Answers

  • User_CEU9I
    User_CEU9I Rank 5 - Community Champion

    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
    Pedro F Rank 6 - Analytics Lead

    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.