Oracle Function to Calculate Duration Excluding Weekends, Public Holidays, and Non-Business Hours

Question

I need to create an Oracle function that calculates the duration between two dates while excluding weekends, public holidays, and non-business hours. Here are the specific requirements:

1. Exclusion of Weekends: The function should exclude weekends (Saturday and Sunday) from the duration calculation.

2. Exclusion of Public Holidays: Public holidays, such as New Year's Day, should also be excluded from the duration calculation. These holidays are stored in a table named public_holidays.

3. Consideration of Business Hours: The duration should only consider business hours, which are from 9:30 AM to 5:30 PM. For example, if a ticket is opened at 8:30 AM on a weekday and closed at 10:30 AM on the same day, the duration should be calculated as 1 hour, as the working hours start from 9:30 AM.

Example:

• Created Date: 29-12-2023 05:57:01.000000000 PM

• Updated Date: 02-01-2024 10:30:00.000000000 PM

The function should return the duration in the format: +00000000 01:00:00.000000000 (dd hh:mm:ss), indicating 1 hour of working time between the two dates, considering the specified conditions.

I would appreciate any guidance or sample code on how to implement such a function in Oracle. Thank you!

this was the function I was using to exclude the weekends and public holidays by having them in a separate table and get the duration between in a string format like ( DD:HH:MM:SS ) but something was not right, the results were not accurate, also now I want to only calculate the working hours, even when the updated date happened after two days, each day has 8 hours of working hours making them as 16 hrs in total even if there was two days gap,

implementing this working hours logic and making other conditions correct, is what made me struggle, please help

create or replace FUNCTION FN_getWorkingDays (
  in_start_date IN  DATE,
  in_end_date   IN  DATE
) RETURN VARCHAR2 DETERMINISTIC
IS
  p_start_date     DATE;
  p_end_date       DATE;
  p_working_days   NUMBER;
  p_holiday_days   NUMBER;
  p_remaining_seconds NUMBER;
  p_remaining_time VARCHAR2(50);
BEGIN
  IF in_start_date IS NULL OR in_end_date IS NULL THEN
    RETURN '0 00:00:00';
  END IF;

  p_start_date := LEAST( in_start_date, in_end_date );
  p_end_date   := GREATEST( in_start_date, in_end_date );

  p_working_days := ( TRUNC( p_end_date, 'IW' ) - TRUNC( p_start_date, 'IW' ) ) * 5 / 7
                    + LEAST( p_end_date - TRUNC( p_end_date, 'IW' ), 5 )
                    - LEAST( p_start_date - TRUNC( p_start_date, 'IW' ), 5 );

  SELECT COALESCE(
           SUM(
             LEAST( p_end_date, holiday_date + 1 )
             - GREATEST( p_start_date, holiday_date )
           ) * 86400,  -- Convert days to seconds
           0
         )
  INTO   p_remaining_seconds
  FROM   Public_Holidays
  WHERE  holiday_date BETWEEN TRUNC( p_start_date )
                      AND     TRUNC( p_end_date )
  AND    holiday_date - TRUNC( holiday_date, 'IW' ) < 5;

  p_remaining_seconds := GREATEST( 0, p_working_days * 86400 - p_remaining_seconds );  -- Convert days to seconds

  -- Calculate days, hours, minutes, seconds
  DECLARE
    days    NUMBER;
    hours   NUMBER;
    minutes NUMBER;
    seconds NUMBER;
  BEGIN
    days    := FLOOR(p_remaining_seconds / (24 * 3600));
    hours   := FLOOR((p_remaining_seconds - days * 24 * 3600) / 3600);
    minutes := FLOOR((p_remaining_seconds - days * 24 * 3600 - hours * 3600) / 60);
    seconds := FLOOR(p_remaining_seconds - days * 24 * 3600 - hours * 3600 - minutes * 60);

    p_remaining_time := LPAD(TO_CHAR(days), 2, '0') || ':' || LPAD(TO_CHAR(hours), 2, '0') || ':' || LPAD(TO_CHAR(minutes), 2, '0') || ':' || LPAD(TO_CHAR(seconds), 2, '0');
  END;

  RETURN p_remaining_time;
END;