2

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?

  • 1
    Do you also want to skip (national) holidays? – Marco May 2 '17 at 12:30
7

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

  • I may be too lazy to think 1 step ahead but assuming you have the business_day table how exactly does that help you do the date arithmetic on business days only? See the example above with adding 2 business days but let's assume the original date can be a holiday, too. – vektor Apr 23 '18 at 6:12
  • @vektor if you join a bussines_day table with no matter which table, you can easily get only those rows that belongs to these days. – McNets Apr 23 '18 at 6:57
  • I don't want to ask a separate question for this as it would be a clear duplicate, so let me be more specific in a comment. Assume a table with a received_on date column. You need to select from this table and add 10 days to this column. With just plain days you'd do SELECT received on + interval '10 days' .... What is the equivalent when using some kind of a business_day table? – vektor Apr 23 '18 at 7:40
  • 1
    @vektor similiar to this: dbfiddle.uk/… – McNets Apr 23 '18 at 8:31
  • Thanks, that looks very reasonable. May I suggest you update your answer with this as it, in my opinion, answers the original question to much more extent? – vektor Apr 23 '18 at 9:39
1

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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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