Sfrgttk

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?

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

This method work for me in PostgreSQL.

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) 

        ) s

    where row_number = num_days

$fbd$ language sql;





select * from add_business_day('2019-03-07', 3)