Calculate working hours between 2 dates in PostgreSQL Calculate working hours between 2 dates in PostgreSQL sql sql

Calculate working hours between 2 dates in PostgreSQL


sql postgresql date-arithmetic generate-series range-types

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_hoursFROM   generate_series (timestamp '2013-06-24 13:30'                      , timestamp '2013-06-24 15:29' - interval '1h'                      , interval '1h') hWHERE  EXTRACT(ISODOW FROM h) < 6AND    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 TEMP 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_hoursFROM  (   SELECT t_id, generate_series (t_start, t_end - interval '1h', interval '1h') AS h   FROM   t   ) subWHERE  EXTRACT(ISODOW FROM h) < 6AND    h::time >= '08:00'AND    h::time <= '14:00'GROUP  BY 1ORDER  BY 1;

SQL Fiddle.

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_intervalFROM  (   SELECT t_id, generate_series (t_start, t_end - interval '5 min', interval '5 min') AS h   FROM   t   ) subWHERE  EXTRACT(ISODOW FROM h) < 6AND    h::time >= '08:00'AND    h::time <= '14:55'  -- 15.00 - interval '5 min'GROUP  BY 1ORDER  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_hoursFROM   tLEFT   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 TRUEORDER  BY 1;

5-minute precision:

SELECT t.t_id, h.work_intervalFROM   tLEFT   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 TRUEORDER  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:

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_intervalFROM   t CROSS JOIN varLEFT   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;

SQL Fiddle.

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 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$  LANGUAGE sql IMMUTABLE;

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) 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$  LANGUAGE plpgsql IMMUTABLE;

Call:

SELECT t_id, f_worktime(t_start, t_end) AS worktimeFROM   tORDER  BY 1;

SQL Fiddle.

sql postgresql date-arithmetic generate-series range-types

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

sql postgresql date-arithmetic generate-series range-types

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 functionCREATE 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 differenceCREATE 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 DefinitionDROP 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 insertionINSERT 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 differenceSELECT   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

Recent Posts
How can I color dots in a xy scatterplot according to column value?
How to update a claim in ASP.NET Identity?
What does {0} mean when initializing an object?
Accessing members of items in a JSONArray with Java
How to log SQL statements in Spring Boot?
Powershell Get-WebSite name parameter is ignored
How to detect scroll to bottom of html element
Java synchronized method
How to test controllers with CodeIgniter?
Detect Visual Composer
Matplotlib: Specify format of floats for tick labels
Rails join a list of strings with commas and "and" before the last