Count business days between 2 dates in postgreSQL

Question

I am trying to count business days between a certain date range in PostgreSQL.

My table:

|start_date |end_date |
------------------------
|2017-06-01 |2017-06-01| 
|2017-05-29 |2017-06-02| 

This is my code:

SELECT pto.start_date, pto.end_date, 
SUM(CASE WHEN extract (dow FROM foo) IN(1,2,3,4,5) THEN 1 ELSE 0 END) as theDIFF 
FROM (
  SELECT start_date, (start_date::date + (generate_series(0,end_date::date 
        - start_date::date)||'days')::interval) AS foo
    FROM pto
) foo inner join pto pto
on pto.start_date = foo.start_date 
group by pto.start_date, pto.end_date

My OUTPUT:

      |start_date(date)| end_date(date) |theDiff(integer)
      ---------------------------------------------------
      |2017-06-01      |  2017-06-01    |        29     |
      |2017-05-29      |  2017-06-02    |        12     |
      ---------------------------------------------------

Expected Output:

      |start_date(date)| end_date(date) |theDiff(integer)
      ---------------------------------------------------
      |2017-06-01      |  2017-06-01    |        1      |
      |2017-05-29      |  2017-06-02    |        5      |
      ---------------------------------------------------

Answer 1

Here is a function for calculating Business Days between two dates simmilar to networkdays excel formula. Doesn't support Holiday list yet.

create or replace function count_business_days(from_date date, to_date date)
returns bigint
as $fbd$
    select count(d::date) as d
    from generate_series(from_date, to_date, '1 day'::interval) d
    where extract('dow' from d) not in (0, 6) 
$fbd$ language sql;

Usage

SELECT issue_id, start_date, live_date, count_business_days(
    TO_DATE(start_date, 'YYYY-MM-DD'),
    TO_DATE(live_date, 'YYYY-MM-DD')
    ) as businessdays
    FROM atable;

Answer 2

You're not using (or showing us) a unique key, if you have more than one row with the same (start_date, end_date) you will get wrong results.

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

db<>fiddle here

Answer 3

No need for generate_series, date arithmetic will do :

create table tbl (start_date date, end_date date);

insert into tbl values
('2017-06-01', '2017-06-01'),
('2017-06-17', '2017-06-17'),
('2017-06-01', '2017-06-12'),
('2017-06-01', '2017-06-13'),
('2017-06-01', '2017-06-16'),
('2017-06-01', '2017-06-17'),
('2017-06-01', '2017-06-18'),
('2017-06-03', '2017-06-12'),
('2017-06-03', '2017-06-13'),
('2017-06-03', '2017-06-16'),
('2017-06-03', '2017-06-17'),
('2017-06-03', '2017-06-18');

SELECT start_date,
       end_date,
       ((end_date - start_date) - 
         EXTRACT (isodow FROM end_date) + 
         EXTRACT (isodow FROM start_date)) * 5 / 7 + 
       least(EXTRACT (isodow FROM end_date),5) + 5 - 
       2 * least(EXTRACT (isodow FROM start_date),5) theDiff
FROM tbl

start_date |  end_date  | thediff
:--------: | :--------: | :-----:
2017-06-01 | 2017-06-01 | 1      
2017-06-17 | 2017-06-17 | 0      
2017-06-01 | 2017-06-12 | 8      
2017-06-01 | 2017-06-13 | 9      
2017-06-01 | 2017-06-16 | 12     
2017-06-01 | 2017-06-17 | 12     
2017-06-01 | 2017-06-18 | 12     
2017-06-03 | 2017-06-12 | 6      
2017-06-03 | 2017-06-13 | 7      
2017-06-03 | 2017-06-16 | 10     
2017-06-03 | 2017-06-17 | 10     
2017-06-03 | 2017-06-18 | 10     

db<>fiddle here