Oracle Query To Exclude Weekends, And 6pm To 9pm

I am trying to achieve a query that returns the time difference between two dates excluding weekends(Saturday and Sunday) and excluding time (6 pm-9 am). For now, I have a function

Solution 1:

You can directly calculate the difference in days (adapted from my answer here):

SELECT start_date,
       end_date,
       ROUND(
         (
           -- Calculate the full weeks difference from the start of ISO weeks.
           ( TRUNC( end_date, 'IW' ) - TRUNC( start_date, 'IW' ) ) * (9/24) * (5/7)
           -- Add the full days for the final week.+ LEAST( TRUNC( end_date ) - TRUNC( end_date, 'IW' ), 5 ) * (9/24)
           -- Subtract the full days from the days of the week before the start date.- LEAST( TRUNC( start_date ) - TRUNC( start_date, 'IW' ), 5 ) * (9/24)
           -- Add the hours of the final day+ LEAST( GREATEST( end_date - TRUNC( end_date ) -9/24, 0 ), 9/24 )
           -- Subtract the hours of the day before the range starts.- LEAST( GREATEST( start_date - TRUNC( start_date ) -9/24, 0 ), 9/24 )
         )
         -- Multiply to give minutes rather than fractions of full days.*24*60
       ) AS work_day_mins_diff
FROM   table_name;

Which, for the sample data:

CREATETABLE table_name ( start_date, end_date ) ASSELECTDATE'2020-12-30'+INTERVAL'00'HOUR, DATE'2020-12-30'+INTERVAL'12'HOURFROM DUAL UNIONALLSELECTDATE'2020-12-30'+INTERVAL'18'HOUR, DATE'2020-12-30'+INTERVAL'20'HOURFROM DUAL UNIONALLSELECTDATE'2020-12-30'+INTERVAL'17:30'HOURTOMINUTE, DATE'2020-12-30'+INTERVAL'21:30'HOURTOMINUTEFROM DUAL UNIONALLSELECTDATE'2021-01-01'+INTERVAL'00'HOUR, DATE'2021-01-04'+INTERVAL'00'HOURFROM DUAL UNIONALLSELECTDATE'2021-01-02'+INTERVAL'00'HOUR, DATE'2021-01-04'+INTERVAL'00'HOURFROM DUAL UNIONALLSELECTDATE'2020-12-28'+INTERVAL'00'HOUR, DATE'2021-01-04'+INTERVAL'00'HOURFROM DUAL UNIONALLSELECTDATE'2020-12-28'+INTERVAL'00'HOUR, DATE'2020-12-29'+INTERVAL'00'HOURFROM DUAL;

Outputs:

(Using ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS (DY)';)

START_DATE                | END_DATE                  | WORK_DAY_MINS_DIFF
:------------------------ | :------------------------ | -----------------:
2020-12-30 00:00:00 (WED) | 2020-12-30 12:00:00 (WED) |                180
2020-12-30 18:00:00 (WED) | 2020-12-30 20:00:00 (WED) |                  0
2020-12-30 17:30:00 (WED) | 2020-12-30 21:30:00 (WED) |                 30
2021-01-01 00:00:00 (FRI) | 2021-01-04 00:00:00 (MON) |                540
2021-01-02 00:00:00 (SAT) | 2021-01-04 00:00:00 (MON) |                  0
2020-12-28 00:00:00 (MON) | 2021-01-04 00:00:00 (MON) |               2700
2020-12-28 00:00:00 (MON) | 2020-12-29 00:00:00 (TUE) |                540

db<>fiddle here