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?
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
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;
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 |
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)