How to convert time stamp to hours — Oracle Analytics

Oracle Analytics Cloud and Server

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
491
Views
12
Comments
User_CEU9I
User_CEU9I Rank 5 - Community Champion

Hi all,

I have a date time column, there is requirement to convert time stamp to hours.

For eg: 9/21/2016 4:07:28 PM   -->  take 4:07:28 --> Convert to hours -----> 4.1245 hrs

How to calculate the above time stamp to hours in a report level. Please advice.

Regards,

Ram.

«1

Answers

  • Pedro F
    Pedro F Rank 6 - Analytics Lead

    Hi Ram,

    It's ok to use OTN to post questions that you need help with and people will indeed help you most of the time. However, it's also expected that people learn from what is explained and try to do something on their own without having the others do their work for them.

    You previously created this topic which I helped you solve. What you're asking here is very similar so make an effort to do this on your own. Write on a paper the logic of what you're trying to achieve (described above) and based on what I already told you on the other topic, you should be more than able to solve this on your own.

    Even if you can't do it by yourself, at least explain what have you tried to do and why is isn't working. Creating a topic and just saying "How to calculate X, please advice" isn't really the way things should work.

  • User_CEU9I
    User_CEU9I Rank 5 - Community Champion

    HI Pedro,

    This question is with regard to my previous thread, which you have mentioned in your thread..

    that is you have given solution for hours range like below.

    >= 0 and < 8 falls in the range 12AM to 8AM

    >= 8 and < 16 falls in the range 8AM to 4PM

    >= 16 and <=23 falls in the range 4PM to 12AM.

    Here i am having a small problem with this solution. See the below screen shot.

    pastedImage_0.png

    For the last row it should fall under 4PM- 12AM. But it is giving 12AM-8AM.

    Hour formula does not satisfy this.

    For eg: Hour(9/30/2016 4:40:00) gives 16 hours

                   Hour(9/30/2016 4:00:00) also gives 16 hours.

    I thought of converting time stamp to hours to make it current result.

    For eg: 9/21/2016 4:07:28 PM   -->  take 4:07:28 --> Convert to hours -----> 4.1245 hrs

    I tried to convert but unable to convert it. Unable to convert min to hours.

    Regards,

    Ram.

  • User_CEU9I
    User_CEU9I Rank 5 - Community Champion

    Hi Pedro,

    The following is the range of hours.

    08:01:00 to 16:00:00 (8AM-4PM)

    16:01:00 to 00:00:00 (4PM-12AM)

    00:01:00 to 08:00:00 (12AM-8AM)

    Regards,

    Ram.

  • Pedro F
    Pedro F Rank 6 - Analytics Lead

    So you have this:

    >= 0 and < 8 falls in the range 12AM to 8AM

    >= 8 and < 16 falls in the range 8AM to 4PM

    >= 16 and <=23 falls in the range 4PM to 12AM.

    and you have this as your requirement:

    00:01:00 to 08:00:00 (12AM-8AM)

    08:01:00 to 16:00:00 (8AM-4PM)

    16:01:00 to 00:00:00 (4PM-12AM)

    Find the differences, adjust the logic...

  • User_CEU9I
    User_CEU9I Rank 5 - Community Champion

    Hi Pedro,

    I changed in the following way.

    CASE    

    WHEN HOUR("- Shipment Dimensions"."Attribute Date 5") > 0 AND HOUR("- Shipment Dimensions"."Attribute Date 5") <= 8 THEN '12AM-8AM'    

    WHEN HOUR("- Shipment Dimensions"."Attribute Date 5") > 16 AND HOUR("- Shipment Dimensions"."Attribute Date 5")<=23 THEN '4PM-12AM'    

    WHEN HOUR("- Shipment Dimensions"."Attribute Date 5") > 8 AND HOUR("- Shipment Dimensions"."Attribute Date 5")<=16 THEN '8AM-4PM'

    END

    But the outcome is not correct.

    pastedImage_8.png

    If you see first row, it is falling under 8AM-4PM instead of 4PM-12AM.

    In 4PM-12AM, i have date 5> 16 , if you see in the third column the hour formula gives 16 hours (from  4:00:00 PM - 4:59:00 PM also).

    Regards,

    Ram.

  • Pedro F
    Pedro F Rank 6 - Analytics Lead

    Ok so based on your example, it seems like using the hour doesn't work out for you. What else can you add to the logic to make it work?

  • User_CEU9I
    User_CEU9I Rank 5 - Community Champion

    Hi Pedro,

    I have tried to include minutes in the above case statement, it become so many case loops. I have to include minutes also. I think this not right approach.

    I thought to convert time stamp to hours

    For eg: 9/21/2016 4:07:28 PM   -->  take 4:07:28 --> Convert to hours -----> 16.1245 hrs

    I tried to convert but unable to convert it in obiee report. How to convert hours:Min:Secs to Hours. Please suggest me.

    Please also let me know any other approach to solve this one.

    Regards,

    Ram.

  • Pedro F
    Pedro F Rank 6 - Analytics Lead

    Hey,

    Post here what you've tried to do with minutes. If you just say you tried to do and it has to many CASES without posting anything, we can't really help you.

    Just describe the logic you're trying to implement and how exactly you're trying to do it and I'm sure you'll get help.

  • User_CEU9I
    User_CEU9I Rank 5 - Community Champion

    Hi Pedro,

    The following is the query and it's output....(considered hours and minutes not seconds in the time stamp).

    Query written:

    CASE WHEN HOUR("- Shipment Dimensions"."Attribute Date 5") >0 and HOUR("- Shipment Dimensions"."Attribute Date 5") < 8 then 

    (case when minute("- Shipment Dimensions"."Attribute Date 5")>=0 then '12AM-8AM' end) else

    (case

    when HOUR("- Shipment Dimensions"."Attribute Date 5") = 8 then

    (case when minute("- Shipment Dimensions"."Attribute Date 5")<=0 then '12AM-8AM' end) else

    (CASE WHEN  hour("- Shipment Dimensions"."Attribute Date 5") =8 THEN  (case when minute("- Shipment Dimensions"."Attribute Date 5")>0 then '8AM-4PM' end) ELSE

    (case WHEN HOUR("- Shipment Dimensions"."Attribute Date 5") >8 and  HOUR("- Shipment Dimensions"."Attribute Date 5") < 16 then

    (case when minute("- Shipment Dimensions"."Attribute Date 5")>=0 then '8AM-4PM' end) else

    (case WHEN HOUR("- Shipment Dimensions"."Attribute Date 5")=16 AND minute("- Shipment Dimensions"."Attribute Date 5")<=0 then

    (case when minute("- Shipment Dimensions"."Attribute Date 5")<=0 then '8AM-4PM' end) ELSE

    (case WHEN HOUR("- Shipment Dimensions"."Attribute Date 5")=16 AND minute("- Shipment Dimensions"."Attribute Date 5")>0 then

    (case when minute("- Shipment Dimensions"."Attribute Date 5")>0 then '4PM-12AM' end) ELSE

      (case WHEN HOUR("- Shipment Dimensions"."Attribute Date 5") > 16 then

    (case when minute("- Shipment Dimensions"."Attribute Date 5")>=0 then '4PM-12AM' end) else

    (case  when HOUR("- Shipment Dimensions"."Attribute Date 5")=23 then

    (case when minute("- Shipment Dimensions"."Attribute Date 5")>=0 then '4PM-12AM' end) ELSE

    ( CASE WHEN HOUR("- Shipment Dimensions"."Attribute Date 5") =0 THEN (case when minute("- Shipment Dimensions"."Attribute Date 5")<=0 then  '4PM-12AM' end)

    END) end )END )

      end ) end) end) end)END) END

    The output: Giving correct result.

    pastedImage_0.png

    If i consider the seconds, then i have to add many more case loops to this code.

    I thought of converting the time stamp to hours. Request you please let me know to how to convert the time-stamp to Hours.

    For eg: 9/21/2016 4:07:28 PM   -->  take 4:07:28 --> Convert to hours -----> 16.1245 hrs

    Regards,

    Ram.

  • Pedro F
    Pedro F Rank 6 - Analytics Lead

    Hi,

    Your CASE works even though it could probably be improved, didn't read it carefully. You're right when you say that if you also want to consider seconds the logic will become even more complicated.

    So, as you said, you can probably try to follow a logic as you have for your 2nd option and just see if 16.1245 > 16:01:00 and decide based on that. You have there that 4:07:28 <> 16.1245 hours. How did you make that calculation? All you need is translate that calculation you made on paper to OBIEE and you can do it by simple using the HOUR/MINUTE/SECOND functions.