8

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      |
      ---------------------------------------------------
2
  • Is there any id or PK column?
    – McNets
    May 24, 2018 at 7:28
  • why do you want to count instead of just computing it?
    – Jasen
    May 26, 2018 at 10:21

5 Answers 5

9

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

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

1

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

1

Number of business days from today until the desired date.

select count(*) week_days
from (
  select 
    getdate()+dt, 
    extract(dow from getdate()+dt) dow
  from(
    select 
      generate_series(
        1,
        datediff('days', getdate(), '2023-07-11 00:00:00.000'),
        1
      ) as dt
  )
) 
where dow not in (0, 6)
1

Actually working solutions with date arithmetic (counting days elapsed, so if start_date = end_date, then the result is 0):

(end_date - start_date)
-- number of days, business and non-business
- EXTRACT (isodow FROM end_date)
-- number of days to sunday strictly before end_date
+ least(EXTRACT (isodow FROM end_date),5)
-- number of days to friday before end_date if end_date is week-end, else to end_date
- (7 - EXTRACT (isodow FROM start_date))
-- number of days from sunday strictly after start_date
+ greatest (5 - EXTRACT (isodow FROM start_date), 0)
-- add back number of business days from start_date to that sunday
- (end_date - start_date - EXTRACT (isodow FROM end_date) - (7 - EXTRACT (isodow FROM start_date))) / 7 * 2
-- remove week-ends between the two sundays (whole weeks)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.