How to Calculate Working Hours Between Two Dates in PostgreSQL, Excluding Weekends and Considering Specific Working Hours?

Calculate Working Hours between 2 Dates in PostgreSQL

Problem:

Given two timestamps, determine the number of working hours between them, considering weekends as non-working days and that working hours are from 8:00 AM to 3:00 PM.

Examples:

• Timestamps: Dec 3rd, 14:00 to Dec 4th, 9:00
• Working Hours: 2 hours (Dec 3rd = 1 hour, Dec 4th = 1 hour)
• Timestamps: Dec 3rd, 15:00 to Dec 7th, 8:00
• Working Hours: 8 hours (Dec 3rd = 0 hours, Dec 4th = 8 hours, Dec 5th = 0 hours, Dec 6th = 0 hours, Dec 7th = 0 hours)

Solution - Rounded Results:

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';

Solution - More Precision:

Using smaller time units (e.g., 5-minute slices) provides more precision:

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

Solution - Exact Results:

For exact results down to the microsecond level, use the following approach:

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)
ORDER  BY 1;

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