How to Calculate Business Hours Between Two Times in Oracle SQL?

Calculating Business Hours in Oracle SQL

When working with data involving time intervals, it's often necessary to account for business hours to accurately calculate hours between specified times. In Oracle SQL, you can achieve this by leveraging a combination of date functions and mathematical calculations.

Calculating Hours with Business Hours

To calculate the difference in hours between a start and end time based on business hours, you can follow these steps:

1. Convert Time Periods to Full Days:

• Calculate the full weeks difference between the start and end of the ISO weeks. Multiply this value by (10/24) and (6/7) to account for full days.
• Add the full days for the final week within the range using the function LEAST( TRUNC( end_time ) - TRUNC( end_time, 'IW' ), 6 ) * (10/24).
• Subtract the full days from the days of the week before the start date using LEAST( TRUNC( start_time ) - TRUNC( start_time, 'IW' ), 6 ) * (10/24).

2. Handle Hours Within Final Day:

• Add the hours of the final day using LEAST( GREATEST( end_time - TRUNC( end_time ) - 8/24, 0 ), 10/24 ).
• Subtract the hours of the day before the range starts using LEAST( GREATEST( start_time - TRUNC( start_time ) - 8/24, 0 ), 10/24 ).

3. Multiply to Convert to Minutes:

• Multiply the above expression by 24 to convert it from fractions of full days to minutes.

Example Query:

SELECT task,
       start_time,
       end_time,
       ROUND(
         (
           -- Calculate full weeks difference
           ( TRUNC( end_time, 'IW' ) - TRUNC( start_time, 'IW' ) ) * (10/24) * (6/7)
           -- Add full days for final week
           + LEAST( TRUNC( end_time ) - TRUNC( end_time, 'IW' ), 6 ) * (10/24)
           -- Subtract full days before start date
           - LEAST( TRUNC( start_time ) - TRUNC( start_time, 'IW' ), 6 ) * (10/24)
           -- Add hours of final day
           + LEAST( GREATEST( end_time - TRUNC( end_time ) - 8/24, 0 ), 10/24 )
           -- Subtract hours of day before range starts
           - LEAST( GREATEST( start_time - TRUNC( start_time ) - 8/24, 0 ), 10/24 )
         )
         -- Multiply to convert to minutes
         * 24,
         15 -- Number of decimal places
       ) AS work_day_hours_diff
FROM   your_table;

This query calculates the hours based on business hours for the provided sample data, taking into account the weekend hours.

The above is the detailed content of How to Calculate Business Hours Between Two Times in Oracle SQL?. For more information, please follow other related articles on the PHP Chinese website!