Calculate working hours between 2 dates in Postgresql

Question

I am developing an algorithm with Postgres (PL/PgSQL), and I need to calculate the number of working hours between 2 dates, taking into account that weekends are not working and the rest of the days are counted only from 8am to 15pm.

Examples:

• From Dec 3rd at 14pm to Dec 4th at 9am should count 2 hours. (3rd = 1, 4th = 1)
• From Dec 3rd at 15pm to Dec 7th at 8am should count 8 hours. (3rd = 0, 4th = 8, 5th = 0, 6th = 0, 7th = 0)

It would be great to consider hour fractions as well.

Thank you!

Answer 1

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;