nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Oracle Communities

Oracle Analytics and AI

Child Item

How to convert time stamp to hours

User_CEU9I

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.

Find more posts tagged with

Accepted answers

All comments

Pedro F

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

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.

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

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

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

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.

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

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

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

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

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.

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

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.