How Can I Calculate Working Hours Between Dates in PostgreSQL, Considering Weekends and Specific Working Hours?

Calculating Working Hours Between Dates in PostgreSQL

Introduction

In various scenarios, determining the number of working hours between two timestamps can prove to be essential in fields such as payroll and scheduling. In PostgreSQL, this calculation requires careful consideration of weekday and time-specific parameters. This article outlines a comprehensive solution, taking into account the following criteria:

• Weekends (Saturdays and Sundays) are excluded from working hours.
• Working hours are defined as Monday through Friday, 8 am to 3 pm.
• Fractional hours are to be included in the calculation.

Solution

Method 1: Rounded Results for Just Two Timestamps

This approach operates on units of 1 hour, ignoring fractional hours. It is a simple but less precise method.

Query:

SELECT count(*) AS work_hours
FROM   generate_series (timestamp '2013-06-24 13:30'
                      , timestamp '2013-06-24 15:29' - interval '1h'
                      , interval '1h') h
WHERE  EXTRACT(ISODOW FROM h) = '08:00'
AND    h::time <= '14:00';

Example Input:

2013-06-24 13:30, 2013-06-24 15:29

Output:

2

Method 2: Rounded Results for a Table of Timestamps

This approach extends the previous method to handle a table of timestamp pairs.

Query:

SELECT t_id, count(*) AS work_hours
FROM  (
   SELECT t_id, generate_series (t_start, t_end - interval '1h', interval '1h') AS h
   FROM   t
   ) sub
WHERE  EXTRACT(ISODOW FROM h) = '08:00'
AND    h::time <p><strong>Method 3: More Precise Calculation</strong></p><p>For a finer-grained calculation, smaller time units can be considered.</p><p><strong>Query:</strong></p><pre class="brush:php;toolbar:false">SELECT t_id, count(*) * interval '5 min' AS work_interval
FROM  (
   SELECT t_id, generate_series (t_start, t_end - interval '5 min', interval '5 min') AS h
   FROM   t
   ) sub
WHERE  EXTRACT(ISODOW FROM h) = '08:00'
AND    h::time <p><strong>Example Input:</strong></p><pre class="brush:php;toolbar:false">| t_id | t_start                | t_end                  |
|------|-------------------------|-------------------------|
| 1    | 2009-12-03 14:00:00    | 2009-12-04 09:00:00    |
| 2    | 2009-12-03 15:00:00    | 2009-12-07 08:00:00    |
| 3    | 2013-06-24 07:00:00    | 2013-06-24 12:00:00    |
| 4    | 2013-06-24 12:00:00    | 2013-06-24 23:00:00    |
| 5    | 2013-06-23 13:00:00    | 2013-06-25 11:00:00    |
| 6    | 2013-06-23 14:01:00    | 2013-06-24 08:59:00    |

Output:

| t_id | work_interval |
|------|----------------|
| 1    | 1 hour         |
| 2    | 8 hours        |
| 3    | 0 hours        |
| 4    | 0 hours        |
| 5    | 6 hours        |
| 6    | 1 hour         |

Method 4: Exact Results

This approach provides exact results with microsecond precision. It is more complex but more computationally efficient.

Query:

WITH var AS (SELECT '08:00'::time  AS v_start
                  , '15:00'::time  AS v_end)
SELECT t_id
     , COALESCE(h.h, '0')  -- add / subtract fractions
       - CASE WHEN EXTRACT(ISODOW FROM t_start)  v_start
               AND t_start::time  v_start
               AND t_end::time = v_start
   AND    h::time <p>This comprehensive solution addresses the need to calculate working hours accurately and efficiently in PostgreSQL.</p>

The above is the detailed content of How Can I Calculate Working Hours Between Dates in PostgreSQL, Considering Weekends and Specific Working Hours?. For more information, please follow other related articles on the PHP Chinese website!