How to Calculate Working Hours Between Two Dates in PostgreSQL?

Calculate Working Hours Between Two Dates in PostgreSQL

When calculating working hours between specific timestamps, it's necessary to consider weekends and the designated work hours. In PostgreSQL, we can leverage various techniques to accomplish this task.

Rounded Results

For Specific Timestamp Ranges:

Consider units of 1 hour, ignoring fractions. The formula is as follows:

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) < 6
AND    h::time >= '08:00'
AND    h::time <= '14:00';

For an Entire Table:

-- Table Creation and Data Insertion
CREATE TABLE t (t_id int PRIMARY KEY, t_start timestamp, t_end timestamp);
INSERT INTO t VALUES
  (1, '2009-12-03 14:00', '2009-12-04 09:00')
, (2, '2009-12-03 15:00', '2009-12-07 08:00')
, (3, '2013-06-24 07:00', '2013-06-24 12:00')
, (4, '2013-06-24 12:00', '2013-06-24 23:00')
, (5, '2013-06-23 13:00', '2013-06-25 11:00')
, (6, '2013-06-23 14:01', '2013-06-24 08:59');

-- Main 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) < 6
AND    h::time >= '08:00'
AND    h::time <= '14:00'
GROUP  BY 1
ORDER  BY 1;

More Precise Results

For higher precision, use smaller time units, such as 5-minute slices.

-- Precision with 5-minute Slices
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) < 6
AND    h::time >= '08:00'
AND    h::time <= '14:55'  -- 15.00 - interval '5 min'
GROUP  BY 1
ORDER  BY 1;

Exact Results

For accurate results down to the microsecond, handle start and end times separately.

Postgres 8.4 :

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) < 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 (  -- count full hours, similar to above solutions
   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)

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