Postgres 10

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table tbl (id int, start_date timestamp, end_date timestamp);
insert into tbl values
(1, '2017-06-01', '2017-06-01'),
(2, '2017-05-29', '2017-06-02');

CREATE TABLE



INSERT 0 2


select start_date, end_date, 
       sum(case when extract (dow from dt) in (1,2,3,4,5) then 1 else 0 end) as thediff
from (
       select start_date, end_date, 
              generate_series(start_date, end_date, '1 day'::interval) as dt
       from   tbl
     ) t
group by start_date, end_date


start_date
end_date
thediff


2017-05-29 00:00:00
2017-06-02 00:00:00
5

2017-06-01 00:00:00
2017-06-01 00:00:00
1



SELECT 2


select id, start_date, end_date, 
       sum(case when extract (dow from dt) in (1,2,3,4,5) then 1 else 0 end) as thediff
from (
       select id, start_date, end_date, 
              generate_series(start_date, end_date, '1 day'::interval) as dt
       from   tbl
     ) t
group by id, start_date, end_date


id
start_date
end_date
thediff


1
2017-06-01 00:00:00
2017-06-01 00:00:00
1

2
2017-05-29 00:00:00
2017-06-02 00:00:00
5



SELECT 2

start_date	end_date	thediff
2017-05-29 00:00:00	2017-06-02 00:00:00	5
2017-06-01 00:00:00	2017-06-01 00:00:00	1