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 date to hours range

Received Response
181
Views
7
Comments
User_CEU9I
User_CEU9I Rank 5 - Community Champion
edited Aug 13, 2024 6:12PM in Oracle Analytics Cloud and Server

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.

pastedImage_0.png

regards,

ram

Answers

  • Pedro F
    Pedro F Rank 6 - Analytics Lead

    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
    User_CEU9I Rank 5 - Community Champion

    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.

    pastedImage_0.png

    regards,

    ram.

  • Thomas Dodds
    Thomas Dodds Rank 8 - Analytics Strategist

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

    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
    User_CEU9I Rank 5 - Community Champion

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

    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
    User_CEU9I Rank 5 - Community Champion

    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.