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) = '08:00'
AND    h::time <p><strong>For an Entire Table:</strong></p><pre class="brush:php;toolbar:false">-- 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) = '08:00'
AND    h::time <h3 id="More-Precise-Results">More Precise Results</h3><p>For higher precision, use smaller time units, such as 5-minute slices.</p><pre class="brush:php;toolbar:false">-- 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) = '08:00'
AND    h::time <h3 id="Exact-Results">Exact Results</h3><p>For accurate results down to the microsecond, handle start and end times separately.</p><p><strong>Postgres 8.4 :</strong></p><pre class="brush:php;toolbar:false">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 

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!