Home » SQL & PL/SQL » SQL & PL/SQL » Oracle calculate hours between timestamps (19.2)
Oracle calculate hours between timestamps [message #685711] Thu, 10 March 2022 13:54 Go to next message
Unclefool
Messages: 47
Registered: August 2021
Member
I'm trying to calculate the number of hours between two timestamps excluding holidays, weekends and working hours.

I figured out how to exclude weekends. Valid working hours are from 730am - 1630Pm. I'm unsure how to exclude half hours and holidays.

Any help would be greatly appreciated. Thanks in advance to all who answer.


create table holidays(
          holiday_date DATE,
          holiday_name VARCHAR2(20),
          constraint holidays_pk primary key (holiday_date),
          constraint is_midnight check ( holiday_date = trunc ( holiday_date ) )
        );

INSERT into holidays (HOLIDAY_DATE,HOLIDAY_NAME)
        WITH dts as (
          select to_date('14-FEB-2021 00:00:00','DD-MON-YYYY HH24:MI:SS'), 'Valentine's Day 2022' from dual union all
          select to_date('21-FEB-2022 00:00:00','DD-MON-YYYY HH24:MI:SS'), 'Presidents Day 2022' from dual
        )
        SELECT * from dts;


with t (start_date
       ,end_date
       )
as (select to_date('01-FEB-2022 13:00:00','dd-mon-yyyy hh24:mi:ss')
          ,to_date('28-FEB-2022 13:00:00','dd-mon-yyyy hh24:mi:ss')
    from dual
   )
, hrs (dt) as
  (select start_date
   from   t
   union all
   select dt +1/24
   from   hrs
   where  hrs.dt < (select end_date-1/24 from t)
  )
select count(*)
from hrs
where to_char(dt,'dy') not in ('sat','sun')
and  to_number(to_char(dt,'hh24')) not between 17 and 23
and  to_number(to_char(dt,'hh24')) not between 0 and 6

Re: Oracle calculate hours between timestamps [message #685712 is a reply to message #685711] Thu, 10 March 2022 14:33 Go to previous messageGo to next message
Michel Cadot
Messages: 68220
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Something like in this topic?

Re: Oracle calculate hours between timestamps [message #685715 is a reply to message #685712] Thu, 10 March 2022 15:34 Go to previous messageGo to next message
Unclefool
Messages: 47
Registered: August 2021
Member
Sort of. I was trying to use MINUS operation to get rid of the holidays and I don't know how to handle times that have >0 minutes
I feel I'm almost there and just need a little hell
Re: Oracle calculate hours between timestamps [message #685716 is a reply to message #685712] Thu, 10 March 2022 17:30 Go to previous message
EdStevens
Messages: 1375
Registered: September 2013
Senior Member
Michel Cadot wrote on Thu, 10 March 2022 14:33

Something like in this topic?

Or maybe these?
https://stackoverflow.com/questions/71420800/find-working-hours-between-two-timestamp-excluding-weekend-and-holiday-in-oracle
https://stackoverflow.com/questions/71406092/create-a-function-to-calculate-working-hours-in-oracle
https://stackoverflow.com/questions/71396001/how-to-calculate-time-between-two-dates-excluding-weekends-holidays26-jan-15au
https://stackoverflow.com/questions/71050438/how-to-calculate-time-between-two-dates-excluding-weekends-holidays26-jan-15au
Previous Topic: Let's promote them all !
Next Topic: Pass a "Cursor XXXX is" statement to another procedure
Goto Forum:
  


Current Time: Sat Jul 02 08:51:15 CDT 2022