How to convert date to hours range

User_CEU9I

Hi all,

How to convert date time to hours range. Please see the below screen shot for the requirement.

I have 3 hour ranges shift:

1. 12AM - 8AM

2. 8 AM - 4 PM

3. 4 PM - 12 AM.

In hours range column .....for the first row it should be 4PM - 12 AM, for the second row it should be 12 AM - 8 AM. So in in Hours range it should display hours range as specified. How to achieve this at the report level. pls advice. I am using obiee 11g (11.1.1.7)

These hours range will be my report filter.

regards,

ram

Pedro F

Hi,

To be honest, you should have that attribute in our database table, it's must better to have it there than calculate this in a report.

However, considering what you have you can achieve what you want by implementing a CASE WHEN logic similar to this:

CASE WHEN HOUR(CURRENT_TIMESTAMP) BETWEEN 0 AND 8 THEN '12AM-8AM'

ETC...

Using the HOUR function over a timestamp it will give you an integer between 0 and 23, therefore giving you the hour. You can have the rest if you just build the logic for the 3 possible thresholds you want.

User_CEU9I

Hi Pedro,

Thanks for the reply. I am able to get the result but it is not counting minutes. Please see the below screen shot.

For the first row, it should come 4 PM - 12 AM.

Third row also it should be 4 PM- 12 AM.

How to consider minutes also in the formula.

regards,

ram.

Thomas Dodds

you are forcing a dimension ... do it right and have your hours bucket dimension and get a key into your fact table for the range in which the fact timestamp falls (do it in ETL; where you have a ton more power and control to do it right)

Hint:  when you have timestamp sensitive data it's best to start with DATE and TIME as separate dimensions both of which are keyed into the fact grain.  Use of information drives physical design.

Pedro F

Don't really understand your issue. You just want to show 3 ranges right? 12AM-8AM, 8AM-4PM, 4PM-12AM, so why do you need the minutes?

If you're worried about how the hours are calculated that will be fine because if you 15:59:59 it will show up as 15 hours and if you have 16:00:00 it will show up as 16 hours. Should do what you need.

@Thomas Dodds: have to admit I was finding it weird that you haven't replied to this one earlier

User_CEU9I

Hi Pedro,

You are right, i want the ranges mentioned in my first thread only. Please let me know how to make 4:07:00 PM fall under 4 PM - 12 AM. instead of 8 AM- 4 PM.

Regards,

Ram

Pedro F

Should work with:

>= 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.

However, consider what I told you first and what Thomas explained in more details. This shouldn't be the way you're doing things. It will be much better to take care of this in the database.

User_CEU9I

Hi Pedro,

Thanks for the help. I understand your suggestions and thomas also. I am using cloud instance in my project and i dont have access to change DB and RPD.

This is the limitation in the current project. I have only webcat to work on. I am forced to do everything at the report level.

Regards,

Shubham.