business_hours.sql

--http://stackoverflow.com/a/17298994/95552
--Generate lookup
create table work_minutes (
  work_minute timestamp primary key
);

insert into work_minutes
select work_minute
from 
  (select generate_series(timestamp '2016-01-01 00:00:00', timestamp '2016-12-31 11:59:00' - interval '1 minute', '1 minute') as work_minute) t
where extract(isodow from work_minute) < 6
  and cast(work_minute as time) between time '07:00' and time '18:00';
  
--Calculate business hours
select count(*) / 60.0 as elapsed_minutes from work_minutes where work_minute > '2016-10-07 07:00:00' 
and work_minute <= '2016-10-07 18:00:00';