How to Accurately Calculate Working Hours Between Two Dates in PostgreSQL?

How to Calculate Working Hours Between 2 Dates in PostgreSQL

The Challenge

Calculating working hours between two dates can be complex, especially when considering factors such as weekends and specific working hours. In this post, we'll explore several approaches using PostgreSQL to efficiently handle this task.

Taking Weekends and Working Hours into Account

Let's assume our working hours are from Monday to Friday, between 8:00 AM and 3:00 PM. Using this definition, we can calculate the working hours between any two timestamps as follows:

1. Ignore Weekends: Check if the dates fall on weekends (Saturday or Sunday) and exclude them from the calculation.
2. Truncate Hours: Convert the timestamps to only include the date and time within working hours. For example, "2023-03-08 19:30:00" would become "2023-03-08 15:00:00".
3. Calculate Hours: Compute the difference between the truncated end time and the truncated start time. This will give you the total number of working hours.

PostgreSQL Solutions

Rounded Results

To get rounded results, we can use PostgreSQL's generate_series() function to generate a series of 1-hour intervals within the working hours range. We then count the number of eligible intervals falling within the specified time period. Here's an example query:

SELECT count(*) AS work_hours
FROM   generate_series('2023-03-08 14:00', '2023-03-09 09:00' - interval '1 hour', interval '1 hour') h
WHERE  EXTRACT(ISODOW FROM h) < 6
AND    h::time >= '08:00'
AND    h::time < '15:00';

More Precision

For more precise results, you can use smaller time units, such as 5-minute increments. The following query provides results with 5-minute precision:

SELECT count(*) * interval '5 min' AS work_interval
FROM   generate_series('2023-03-08 14:01', '2023-03-09 09:00' - interval '5 min', interval '5 min') h
WHERE  EXTRACT(ISODOW FROM h) < 6
AND    h::time >= '08:00'
AND    h::time < '15:00';

Exact Results

For exact results, you can take a more nuanced approach by separately handling the start and end of the time frame. Here's a query that provides precise interval results to the microsecond:

SELECT t_id
     , COALESCE(h.h, '0')
       - CASE WHEN EXTRACT(ISODOW FROM t_start) < 6
               AND t_start::time > v_start
               AND t_start::time < v_end
         THEN t_start - date_trunc('hour', t_start)
         ELSE '0'::interval END
       + CASE WHEN EXTRACT(ISODOW FROM t_end) < 6
               AND t_end::time > v_start
               AND t_end::time < v_end
         THEN t_end - date_trunc('hour', t_end)
         ELSE '0'::interval END                 AS work_interval
FROM   t CROSS JOIN var
LEFT   JOIN (
   SELECT t_id, count(*)::int * interval '1h' AS h
   FROM  (
      SELECT t_id, v_start, v_end
           , generate_series (date_trunc('hour', t_start)
                            , date_trunc('hour', t_end) - interval '1h'
                            , interval '1h') AS h
      FROM   t, var
      ) sub
   WHERE  EXTRACT(ISODOW FROM h) < 6
   AND    h::time >= v_start
   AND    h::time <= v_end - interval '1h'
   GROUP  BY 1
   ) h USING (t_id)
ORDER  BY 1;

Comparison of Approaches

The different approaches presented provide varying levels of precision and performance.

Rounded Results: This method is simple to implement and provides reasonable estimates, especially when the input times are close to the boundaries of the working hours range.
More Precision: This approach offers better accuracy by using smaller time units. The impact on performance is minimal in most scenarios.
Exact Results: This method is more complex and requires additional calculations. It provides the most precise results but may come at a higher computational cost.

Conclusion

Choosing the appropriate approach depends on the required precision and performance constraints. For general-purpose use, the "More Precision" method with 5-minute increments strikes a good balance between accuracy and efficiency. However, for cases where absolute accuracy is paramount, the "Exact Results" approach can be employed to provide precise intervals to the microsecond.

The above is the detailed content of How to Accurately Calculate Working Hours Between Two Dates in PostgreSQL?. For more information, please follow other related articles on the PHP Chinese website!