Calculate working hours between 2 dates in PostgreSQL

According to your question working hours are: Mo–Fr, 08:00–15:00.

Rounded results

For just two given timestamps

Operating on units of 1 hour. Fractions are ignored, therefore not precise but simple:

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

• The function generate_series() generates one row if the end is greater than the start and another row for every full given interval (1 hour). This wold count every hour entered into. To ignore fractional hours, subtract 1 hour from the end. And don't count hours starting before 14:00.

• Use the field pattern ISODOW instead of DOW for EXTRACT() to simplify expressions. Returns 7 instead of 0 for Sundays.

• A simple (and very cheap) cast to time makes it easy to identify qualifying hours.

• Fractions of an hour are ignored, even if fractions at begin and end of the interval would add up to an hour or more.

For a whole table

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') -- examples in question , (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') -- max. fractions at begin and end ; 

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; 

db<>fiddle here
_{Old sqlfiddle}

More precision

To get more precision you can use smaller time units. 5-minute slices for instance:

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; 

The smaller the unit the higher the cost.

Cleaner with LATERAL in Postgres 9.3+

In combination with the new LATERAL feature in Postgres 9.3, the above query can then be written as:

1-hour precision:

SELECT t.t_id, h.work_hours FROM t LEFT JOIN LATERAL ( SELECT count(*) AS work_hours FROM generate_series (t.t_start, t.t_end - interval '1h', interval '1h') h WHERE EXTRACT(ISODOW FROM h) < 6 AND h::time >= '08:00' AND h::time <= '14:00' ) h ON TRUE ORDER BY 1; 

5-minute precision:

SELECT t.t_id, h.work_interval FROM t LEFT JOIN LATERAL ( SELECT count(*) * interval '5 min' AS work_interval FROM generate_series (t.t_start, t.t_end - interval '5 min', interval '5 min') h WHERE EXTRACT(ISODOW FROM h) < 6 AND h::time >= '08:00' AND h::time <= '14:55' ) h ON TRUE ORDER BY 1; 

This has the additional advantage that intervals containing zero working hours are not excluded from the result like in the above versions.

More about LATERAL:

• Find most common elements in array with a group by
• Insert multiple rows in one table based on number in another table

Exact results

Postgres 8.4+

Or you deal with start and end of the time frame separately to get exact results to the microsecond. Makes the query more complex, but cheaper and exact:

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

db<>fiddle here
_{Old sqlfiddle}

Postgres 9.2+ with tsrange

The new range types offer a more elegant solution for exact results in combination with the intersection operator *:

Simple function for time ranges spanning only one day:

CREATE OR REPLACE FUNCTION f_worktime_1day(_start timestamp, _end timestamp) RETURNS interval LANGUAGE sql IMMUTABLE AS $func$ -- _start & _end within one calendar day! - you may want to check ... SELECT CASE WHEN extract(ISODOW from _start) < 6 THEN ( SELECT COALESCE(upper(h) - lower(h), '0') FROM ( SELECT tsrange '[2000-1-1 08:00, 2000-1-1 15:00)' -- hours hard coded * tsrange( '2000-1-1'::date + _start::time , '2000-1-1'::date + _end::time ) AS h ) sub ) ELSE '0' END $func$; 

If your ranges never span multiple days, that's all you need.
Else, use this wrapper function to deal with any interval:

CREATE OR REPLACE FUNCTION f_worktime(_start timestamp , _end timestamp , OUT work_time interval) LANGUAGE plpgsql IMMUTABLE AS $func$ BEGIN CASE _end::date - _start::date -- spanning how many days? WHEN 0 THEN -- all in one calendar day work_time := f_worktime_1day(_start, _end); WHEN 1 THEN -- wrap around midnight once work_time := f_worktime_1day(_start, NULL) + f_worktime_1day(_end::date, _end); ELSE -- multiple days work_time := f_worktime_1day(_start, NULL) + f_worktime_1day(_end::date, _end) + (SELECT count(*) * interval '7:00' -- workday hard coded! FROM generate_series(_start::date + 1 , _end::date - 1, '1 day') AS t WHERE extract(ISODOW from t) < 6); END CASE; END $func$; 

Call:

SELECT t_id, f_worktime(t_start, t_end) AS worktime FROM t ORDER BY 1; 

db<>fiddle here
_{Old sqlfiddle}

How about this: create a small table with 24*7 rows, one row for each hour in a week.

CREATE TABLE hours ( hour timestamp not null, is_working boolean not null ); INSERT INTO hours (hour, is_working) VALUES ('2009-11-2 00:00:00', false), ('2009-11-2 01:00:00', false), . . . ('2009-11-2 08:00:00', true), . . . ('2009-11-2 15:00:00', true), ('2009-11-2 16:00:00', false), . . . ('2009-11-2 23:00:00', false); 

Likewise add 24 rows for each of the other days. It doesn't matter what year or month you give, as you'll see in a moment. You just need to represent all seven days of the week.

SELECT t.id, t.start, t.end, SUM(CASE WHEN h.is_working THEN 1 ELSE 0 END) AS hours_worked FROM mytable t JOIN hours h ON (EXTRACT(DOW FROM TIMESTAMP h.hour) BETWEEN EXTRACT(DOW FROM TIMESTAMP t.start) AND EXTRACT(DOW FROM TIMESTAMP t.end)) AND (EXTRACT(DOW FROM TIMESTAMP h.hour) > EXTRACT(DOW FROM TIMESTAMP t.start) OR EXTRACT(HOUR FROM TIMESTAMP h.hour) >= EXTRACT(HOUR FROM TIMESTAMP t.start)) AND (EXTRACT(DOW FROM TIMESTAMP h.hour) < EXTRACT(DOW FROM TIMESTAMP t.end) OR EXTRACT(HOUR FROM TIMESTAMP h.hour) <= EXTRACT(HOUR FROM TIMESTAMP t.end)) GROUP BY t.id, t.start, t.end; 

This following functions will take the input for the
working start time of the day
working end time of the day
start time
end time

-- helper function CREATE OR REPLACE FUNCTION get_working_time_in_a_day(sdt TIMESTAMP, edt TIMESTAMP, swt TIME, ewt TIME) RETURNS INT AS $$ DECLARE sd TIMESTAMP; ed TIMESTAMP; swdt TIMESTAMP; ewdt TIMESTAMP; seconds INT; BEGIN swdt = sdt::DATE || ' ' || swt; -- work start datetime for a day ewdt = sdt::DATE || ' ' || ewt; -- work end datetime for a day IF (sdt < swdt AND edt <= swdt) -- case 1 and 2 THEN seconds = 0; END IF; IF (sdt < swdt AND edt > swdt AND edt <= ewdt) -- case 3 and 4 THEN seconds = EXTRACT(EPOCH FROM (edt - swdt)); END IF; IF (sdt < swdt AND edt > swdt AND edt > ewdt) -- case 5 THEN seconds = EXTRACT(EPOCH FROM (ewdt - swdt)); END IF; IF (sdt = swdt AND edt > swdt AND edt <= ewdt) -- case 6 and 7 THEN seconds = EXTRACT(EPOCH FROM (edt - sdt)); END IF; IF (sdt = swdt AND edt > ewdt) -- case 8 THEN seconds = EXTRACT(EPOCH FROM (ewdt - sdt)); END IF; IF (sdt > swdt AND edt <= ewdt) -- case 9 and 10 THEN seconds = EXTRACT(EPOCH FROM (edt - sdt)); END IF; IF (sdt > swdt AND sdt < ewdt AND edt > ewdt) -- case 11 THEN seconds = EXTRACT(EPOCH FROM (ewdt - sdt)); END IF; IF (sdt >= ewdt AND edt > ewdt) -- case 12 and 13 THEN seconds = 0; END IF; RETURN seconds; END; $$ LANGUAGE plpgsql; -- Get work time difference CREATE OR REPLACE FUNCTION get_working_time(sdt TIMESTAMP, edt TIMESTAMP, swt TIME, ewt TIME) RETURNS INT AS $$ DECLARE seconds INT = 0; strst VARCHAR(9) = ' 00:00:00'; stret VARCHAR(9) = ' 23:59:59'; tend TIMESTAMP; tempEdt TIMESTAMP; x int; BEGIN <<test>> WHILE sdt <= edt LOOP tend = sdt::DATE || stret; -- get the false end datetime for start time IF edt >= tend THEN tempEdt = tend; ELSE tempEdt = edt; END IF; -- skip saturday and sunday x = EXTRACT(DOW FROM sdt); if (x > 0 AND x < 6) THEN seconds = seconds + get_working_time_in_a_day(sdt, tempEdt, swt, ewt); ELSE -- RAISE NOTICE 'MISSED A DAY'; END IF; sdt = (sdt + (INTERVAL '1 DAY'))::DATE || strst; END LOOP test; --RAISE NOTICE 'diff in minutes = %', (seconds / 60); RETURN seconds; END; $$ LANGUAGE plpgsql; -- Table Definition DROP TABLE IF EXISTS test_working_time; CREATE TABLE test_working_time( pk SERIAL PRIMARY KEY, start_datetime TIMESTAMP, end_datetime TIMESTAMP, start_work_time TIME, end_work_time TIME ); -- Test data insertion INSERT INTO test_working_time VALUES (1, '2015-11-03 01:00:00', '2015-11-03 07:00:00', '08:00:00', '22:00:00'), (2, '2015-11-03 01:00:00', '2015-11-04 07:00:00', '08:00:00', '22:00:00'), (3, '2015-11-03 01:00:00', '2015-11-05 07:00:00', '08:00:00', '22:00:00'), (4, '2015-11-03 01:00:00', '2015-11-06 07:00:00', '08:00:00', '22:00:00'), (5, '2015-11-03 01:00:00', '2015-11-07 07:00:00', '08:00:00', '22:00:00'), (6, '2015-11-03 01:00:00', '2015-11-03 08:00:00', '08:00:00', '22:00:00'), (7, '2015-11-03 01:00:00', '2015-11-04 08:00:00', '08:00:00', '22:00:00'), (8, '2015-11-03 01:00:00', '2015-11-05 08:00:00', '08:00:00', '22:00:00'), (9, '2015-11-03 01:00:00', '2015-11-06 08:00:00', '08:00:00', '22:00:00'), (10, '2015-11-03 01:00:00', '2015-11-07 08:00:00', '08:00:00', '22:00:00'), (11, '2015-11-03 01:00:00', '2015-11-03 11:00:00', '08:00:00', '22:00:00'), (12, '2015-11-03 01:00:00', '2015-11-04 11:00:00', '08:00:00', '22:00:00'), (13, '2015-11-03 01:00:00', '2015-11-05 11:00:00', '08:00:00', '22:00:00'), (14, '2015-11-03 01:00:00', '2015-11-06 11:00:00', '08:00:00', '22:00:00'), (15, '2015-11-03 01:00:00', '2015-11-07 11:00:00', '08:00:00', '22:00:00'), (16, '2015-11-03 01:00:00', '2015-11-03 22:00:00', '08:00:00', '22:00:00'), (17, '2015-11-03 01:00:00', '2015-11-04 22:00:00', '08:00:00', '22:00:00'), (18, '2015-11-03 01:00:00', '2015-11-05 22:00:00', '08:00:00', '22:00:00'), (19, '2015-11-03 01:00:00', '2015-11-06 22:00:00', '08:00:00', '22:00:00'), (20, '2015-11-03 01:00:00', '2015-11-07 22:00:00', '08:00:00', '22:00:00'), (21, '2015-11-03 01:00:00', '2015-11-03 23:00:00', '08:00:00', '22:00:00'), (22, '2015-11-03 01:00:00', '2015-11-04 23:00:00', '08:00:00', '22:00:00'), (23, '2015-11-03 01:00:00', '2015-11-05 23:00:00', '08:00:00', '22:00:00'), (24, '2015-11-03 01:00:00', '2015-11-06 23:00:00', '08:00:00', '22:00:00'), (25, '2015-11-03 01:00:00', '2015-11-07 23:00:00', '08:00:00', '22:00:00'), (26, '2015-11-03 08:00:00', '2015-11-03 11:00:00', '08:00:00', '22:00:00'), (27, '2015-11-03 08:00:00', '2015-11-04 11:00:00', '08:00:00', '22:00:00'), (28, '2015-11-03 08:00:00', '2015-11-05 11:00:00', '08:00:00', '22:00:00'), (29, '2015-11-03 08:00:00', '2015-11-06 11:00:00', '08:00:00', '22:00:00'), (30, '2015-11-03 08:00:00', '2015-11-07 11:00:00', '08:00:00', '22:00:00'), (31, '2015-11-03 08:00:00', '2015-11-03 22:00:00', '08:00:00', '22:00:00'), (32, '2015-11-03 08:00:00', '2015-11-04 22:00:00', '08:00:00', '22:00:00'), (33, '2015-11-03 08:00:00', '2015-11-05 22:00:00', '08:00:00', '22:00:00'), (34, '2015-11-03 08:00:00', '2015-11-06 22:00:00', '08:00:00', '22:00:00'), (35, '2015-11-03 08:00:00', '2015-11-07 22:00:00', '08:00:00', '22:00:00'), (36, '2015-11-03 08:00:00', '2015-11-03 23:00:00', '08:00:00', '22:00:00'), (37, '2015-11-03 08:00:00', '2015-11-04 23:00:00', '08:00:00', '22:00:00'), (38, '2015-11-03 08:00:00', '2015-11-05 23:00:00', '08:00:00', '22:00:00'), (39, '2015-11-03 08:00:00', '2015-11-06 23:00:00', '08:00:00', '22:00:00'), (40, '2015-11-03 08:00:00', '2015-11-07 23:00:00', '08:00:00', '22:00:00'), (41, '2015-11-03 12:00:00', '2015-11-03 18:00:00', '08:00:00', '22:00:00'), (42, '2015-11-03 12:00:00', '2015-11-04 18:00:00', '08:00:00', '22:00:00'), (43, '2015-11-03 12:00:00', '2015-11-05 18:00:00', '08:00:00', '22:00:00'), (44, '2015-11-03 12:00:00', '2015-11-06 18:00:00', '08:00:00', '22:00:00'), (45, '2015-11-03 12:00:00', '2015-11-07 18:00:00', '08:00:00', '22:00:00'), (46, '2015-11-03 12:00:00', '2015-11-03 22:00:00', '08:00:00', '22:00:00'), (47, '2015-11-03 12:00:00', '2015-11-04 22:00:00', '08:00:00', '22:00:00'), (48, '2015-11-03 12:00:00', '2015-11-05 22:00:00', '08:00:00', '22:00:00'), (49, '2015-11-03 12:00:00', '2015-11-06 22:00:00', '08:00:00', '22:00:00'), (50, '2015-11-03 12:00:00', '2015-11-07 22:00:00', '08:00:00', '22:00:00'), (51, '2015-11-03 12:00:00', '2015-11-03 23:00:00', '08:00:00', '22:00:00'), (52, '2015-11-03 12:00:00', '2015-11-04 23:00:00', '08:00:00', '22:00:00'), (53, '2015-11-03 12:00:00', '2015-11-05 23:00:00', '08:00:00', '22:00:00'), (54, '2015-11-03 12:00:00', '2015-11-06 23:00:00', '08:00:00', '22:00:00'), (55, '2015-11-03 12:00:00', '2015-11-07 23:00:00', '08:00:00', '22:00:00'), (56, '2015-11-03 22:00:00', '2015-11-03 23:00:00', '08:00:00', '22:00:00'), (57, '2015-11-03 22:00:00', '2015-11-04 23:00:00', '08:00:00', '22:00:00'), (58, '2015-11-03 22:00:00', '2015-11-05 23:00:00', '08:00:00', '22:00:00'), (59, '2015-11-03 22:00:00', '2015-11-06 23:00:00', '08:00:00', '22:00:00'), (60, '2015-11-03 22:00:00', '2015-11-07 23:00:00', '08:00:00', '22:00:00'), (61, '2015-11-03 22:30:00', '2015-11-03 23:30:00', '08:00:00', '22:00:00'), (62, '2015-11-03 22:30:00', '2015-11-04 23:30:00', '08:00:00', '22:00:00'), (63, '2015-11-03 22:30:00', '2015-11-05 23:30:00', '08:00:00', '22:00:00'), (64, '2015-11-03 22:30:00', '2015-11-06 23:30:00', '08:00:00', '22:00:00'), (65, '2015-11-03 22:30:00', '2015-11-07 23:30:00', '08:00:00', '22:00:00'); -- select query to get work time difference SELECT start_datetime, end_datetime, start_work_time, end_work_time, get_working_time(start_datetime, end_datetime, start_work_time, end_work_time) AS diff_in_minutes FROM test_working_time; 

This will give the difference of only the work hours in seconds between the start and end datetime