PostgreSQL – How to Add Business Days to a Date

datepostgresql

I am trying to find a way to add business days to any date.
An example would be:

date = '2017-04-28'  (It was a Friday)

date + 2 = '2017-05-02' (It skipped Saturday and Sunday)

Is there a way to do this without custom queries?

Best Answer

You can use generate_series() to generate a series of dates, and extract() to get day of week.

Then simply filter those dates where day of week are not 0=Sunday, 6=Saturday.

with days as
(
    select dd, extract(DOW from dd) dw
    from generate_series('2017-04-28'::date, '2017-05-02'::date, '1 day'::interval) dd
)
select *
from   days
where  dw not in (6,0);

dd                     | dw
:--------------------- | :-
2017-04-28 00:00:00+01 | 5 
2017-05-01 00:00:00+01 | 1 
2017-05-02 00:00:00+01 | 2

dbfiddle here

If you have to exclude public holidays and other non-business days, you can build a business_day table. Just insert the output from above and then remove all days that have to be excluded (in certain countries, like Hungary, there might be additional replacement days (typically Saturdays) which have to be added, too). Of course, this has to be maintained (for example, you can prepare the next year every December), but as there is no built-in functionality that knows about those days, you have no better option.

Using a calendar table

Let me create a sample calendar table and insert some values:

create table calendar
(
    id serial primary key, 
    cal_day date not null,
    bussines_day bool not null
);

insert into calendar (cal_day, bussines_day) values 
('20180101', false), ('20180102', true),
('20180103', false), ('20180104', true),
('20180105', false), ('20180106', true),
('20180107', false), ('20180108', true),
('20180109', false), ('20180110', true),
('20180111', false), ('20180112', true);

Now you can use a function to obtain the next Nth business day in this way:

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

    select max(cal_day) as the_day
    from (select   cal_day
          from     calendar
          where    cal_day > $1
          and      business_day = true
          order by cal_day
          limit    $2) bd;

$fbd$ language sql;

or

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

    select cal_day
    from   (select cal_day,
                   row_number() over (order by cal_day) rn
            from   calendar
            where  cal_day > $1
            and    business_day = true
            limit  $2) bd
    where  rn = $2;

$fbd$ language sql;

Both return same result:

select add_business_day('20180103', 4);

| add_business_day |
| :--------------- |
| 2018-01-10       |

select add_business_day2('20180103', 4)

| add_business_day2 |
| :---------------- |
| 2018-01-10        |

db<>fiddle here

Related Question