How to add number of business days to given date

Question

I'm looking for a function which adds number of business days to given date.

Holidays table

create table pyha (pyha date primary key) ;
insert into pyha values ('2018-12-24'),('2018-12-25'),('2018-12-26'),('2019-01-01');

contains holidays. Also saturday and sunday are non-business days.

I tried to create function

create or replace function add_business_day(from_date date, num_days int)
returns date
as $fbd$

with days as
(
    select dd, extract(DOW from dd) dw
    from generate_series(($1+ interval'1day')::date, ($1+ interval'1day'*$2+interval'10days')::date , '1 day'::interval) dd
)
select min(dd)::date
from   days
where  dw not in (6,0) and
dd not in (select pyha from pyha)
and dd>=$1+interval'1day'*$2+
interval'1day'*(select count(*) from pyha where pyha between $1+ interval'1day' and 
   $1+interval'1day'*$2 )
$fbd$ language sql;

But it returns sometimes incorrect result:

add_business_day('2018-12-08',2)  

returns 2018-12-10
but correct result is 2018-12-11

How to create such function in Postgres 9.1+ ?

Answer 1

Here's a solution if you don't want to rely on generate_series. It's a fairly straight forward algorithm that allows you to specify your own business day definition via an array. The default business days (mo-fr) would be defined like this array[0,1,1,1,1,1,0]. Since it doesn't use generate_series it presumably is much faster especially for larger intervals.

It doesn't consider the holiday table case, but will hopefully be a helpful pointer nonetheless.

create or replace function add_business_days(start timestamp with time zone, business_days int[], add_days int)
  returns timestamp with time zone as $$
  declare
    days_left int := add_days;
    num_bds int := (select sum(x.val) from ( select unnest(business_days) val ) as x);
    add_real_days int := 0;
    start_dow int := extract(dow from start);
  begin
    -- if all weekdays are false just ignore the logic below
    if num_bds = 0 then
      return start + make_interval(days := add_days);
    end if;

    -- if the start day is no business day, go forward until we hit one
    while business_days[((start_dow + add_real_days) % 7) + 1] = 0 loop
      add_real_days := add_real_days + 1;
    end loop;

    -- jump to the final week
    add_real_days := add_real_days + (days_left / num_bds)::int * 7;
    days_left := days_left % num_bds;

    -- go ahead with the remaining days until we hit a business day
    while days_left > 0 or business_days[((start_dow + add_real_days) % 7) + 1] = 0 loop
      add_real_days := add_real_days + 1;
      days_left := days_left - business_days[((start_dow + add_real_days) % 7) + 1];
    end loop;
    return start + make_interval(days := add_real_days);
  end;
$$
language plpgsql immutable;

You can use the function like this:

select add_business_days(now(), array[0,1,1,1,1,0], 4);

Answer 2

I've had this issue myself - here is the function i have written to replace the excel workday() function as closely as possible, allowing negative workday additions as well as positive

create or replace function workday(startdate date, i integer) returns date as 
$$ 
with workday_cte as (
    select s.a::date as workday from 
    generate_series(startdate - ((abs(i) * 2 + 10) || ' day')::interval, 
    startdate + ((abs(i) * 2 + 10) || ' day')::interval, '1 day'::interval) s(a)
    where extract(dow from s.a) between 1 and 5
    except 
    select holiday
    from holidays
)
select case when i > 0 then a.workday when i = 0 then startdate else b.workday end from 
(
   select * from workday_cte where workday > startdate 
   order by workday asc limit 1 offset greatest(abs(i) - 1,0)
) as a, 
(
   select * from workday_cte where workday < startdate 
   order by workday desc limit 1 offset greatest(abs(i) - 1,0)
) as b
$$ language sql;

You still need to update the section select holiday from holidays to your own table of holiday dates.

Here also is a networkdays() function replacement, where similarly you need to update the holidays table - but note that if startdate is not before enddate it returns 0 unlike the excel function

create or replace function networkdays(startdate date, enddate date) returns bigint as
$$
with workday_cte as (
    select s.a::date as workday from 
    generate_series(startdate, enddate, '1 day'::interval) s(a)
    where extract(dow from s.a) between 1 and 5
    except 
    select holiday
    from data.dtdholidays
)
select count(workday_cte.workday) from workday_cte
$$ language sql;

running this query to check the outputs:

select *, networkdays("wd-1", wd1) from 
(
   select day, workday(day, 1) as wd1, workday(day,0) as wd0, workday(day,-1) as "wd-1" 
   from (select day::date 
         from generate_series('2019-12-16'::date, '2019-12-23'::date, '1 day'::interval
         ) days(day)
) days(day)) a;

gives me:

    day     |    wd1     |    wd0     |    wd-1    | networkdays
------------+------------+------------+------------+-------------
 2019-12-16 | 2019-12-17 | 2019-12-16 | 2019-12-13 |           3
 2019-12-17 | 2019-12-18 | 2019-12-17 | 2019-12-16 |           3
 2019-12-18 | 2019-12-19 | 2019-12-18 | 2019-12-17 |           3
 2019-12-19 | 2019-12-20 | 2019-12-19 | 2019-12-18 |           3
 2019-12-20 | 2019-12-23 | 2019-12-20 | 2019-12-19 |           3
 2019-12-21 | 2019-12-23 | 2019-12-21 | 2019-12-20 |           2
 2019-12-22 | 2019-12-23 | 2019-12-22 | 2019-12-20 |           2
 2019-12-23 | 2019-12-24 | 2019-12-23 | 2019-12-20 |           3

Alternate weekend/holiday handling

In some situations it is better that the number of workdays in the period created by adding or subtracting a fixed number of days is constant - for example in the table above when a weekend date (the 21st or 22nd of december) is used as the startdate then networkdays is 2 and not 3. You can change the behaviour of the workday function so that the networkdays is constant by rolling to the next workday before adding or subtracting the workdays required

create or replace function workdaycwd(startdate date, i integer) returns date as 
$$ 
with workday_cte as (
    select s.a::date as workday from 
    generate_series(startdate - ((abs(i) * 2 + 10) || ' day')::interval, 
    startdate + ((abs(i) * 2 + 10) || ' day')::interval, '1 day'::interval) s(a)
    where extract(dow from s.a) between 1 and 5
    except 
    select holiday
    from data.dtdholidays
)
select case when i >= 0 then a.workday else b.workday end from 
(
   select * from workday_cte where workday >= startdate 
   order by workday asc limit 1 offset (abs(i))
) as a, 
(
   select * from workday_cte where workday < startdate 
   order by workday desc limit 1 offset greatest(abs(i) - 1, 0)
) as b
$$ language sql;

running a similar query as above gives

    day     |    wd1     |    wd0     |    wd-1    | networkdays
------------+------------+------------+------------+-------------
 2019-12-16 | 2019-12-17 | 2019-12-16 | 2019-12-13 |           3
 2019-12-17 | 2019-12-18 | 2019-12-17 | 2019-12-16 |           3
 2019-12-18 | 2019-12-19 | 2019-12-18 | 2019-12-17 |           3
 2019-12-19 | 2019-12-20 | 2019-12-19 | 2019-12-18 |           3
 2019-12-20 | 2019-12-23 | 2019-12-20 | 2019-12-19 |           3
 2019-12-21 | 2019-12-24 | 2019-12-23 | 2019-12-20 |           3
 2019-12-22 | 2019-12-24 | 2019-12-23 | 2019-12-20 |           3
 2019-12-23 | 2019-12-24 | 2019-12-23 | 2019-12-20 |           3

Answer 3

The key is to generate series of business days and number them with row_number():

create or replace function add_business_day(from_date date, num_days int)
returns date
as $fbd$
    select d
    from (
        select d::date, row_number() over (order by d)
        from generate_series(from_date+ 1, from_date+ num_days* 2+ 5, '1d') d
        where 
            extract('dow' from d) not in (0, 6) 
            and d not in (select pyha from pyha)
        ) s
    where row_number = num_days
$fbd$ language sql;

The test query's results seem correct:

select days, add_business_day('2018-12-08', days)
from generate_series(1, 20) days

 days | add_business_day 
------+------------------
    1 | 2018-12-10
    2 | 2018-12-11
    3 | 2018-12-12
    4 | 2018-12-13
    5 | 2018-12-14
    6 | 2018-12-17
    7 | 2018-12-18
    8 | 2018-12-19
    9 | 2018-12-20
   10 | 2018-12-21
   11 | 2018-12-27
   12 | 2018-12-28
   13 | 2018-12-31
   14 | 2019-01-02
   15 | 2019-01-03
   16 | 2019-01-04
   17 | 2019-01-07
   18 | 2019-01-08
   19 | 2019-01-09
   20 | 2019-01-10
(20 rows)

Alternatively, you can find the date in a loop:

create or replace function add_business_day_loop(from_date date, num_days int)
returns date
as $fbd$
begin
    while num_days > 0 loop
        from_date:= from_date+ 1;
        while from_date in (select pyha from pyha) or extract('dow' from from_date) in (0, 6) loop
            from_date:= from_date+ 1;
        end loop;
        num_days:= num_days- 1;
    end loop;
    return from_date;
end;
$fbd$ language plpgsql;