Add business days in PostgreSQL

Question

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?

Answer 1

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

Answer 2

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)

Answer 3

In a related use case, I needed a function to return the next 10 days from current date that are not either Saturdays or Sundays. I added to the current day the requested number of days, plus the number of weekends (the tricky part being to decide if there is one more or less weekend to be added).

Here is my commented function:

CREATE OR REPLACE FUNCTION business_days(from_date date default CURRENT_DATE, num_days int default 0) RETURNS TABLE(i bigint, d date)
LANGUAGE sql AS $$
    SELECT ROW_NUMBER() OVER (ORDER BY d) - 1, d::date
    FROM generate_series(
      from_date,
      from_date +
        num_days * interval '1 day' + -- add number of days
        2 * (num_days / 5) * interval '1 day' + -- add natural week ends
        (CASE extract('dow' from from_date) WHEN 0 THEN 1 ELSE 2 END) * (CASE extract('dow' from from_date) WHEN 0 THEN 7 ELSE extract('dow' from from_date) END + MOD(num_days, 5) > 5)::int * interval '1 day', -- add gap days if needed
        --
        -- Gap days are added
        -- When (num_days % 5) + day_num > 5
        -- If current day is Sunday, we need to add 1 day
        -- Else add 2 days
        --
        --       |------------
        --       | 0 1 2  3  4
        -- |-----|------------
        -- | L 1 | 1 2 3  4  5
        -- | M 2 | 2 3 4  5  6
        -- | M 3 | 3 4 5  6  7
        -- | J 4 | 4 5 6  7  8
        -- | V 5 | 5 6 7  8  9
        -- | S 6 | 6 7 8  9 10
        -- | D 7 | 7 8 9 10 11
        --
      '1d'
    ) d
    WHERE extract('dow' from d) NOT IN (0, 6)
$$;

You can then use it to get the 10th next business day.
For instance in another function:

CREATE OR REPLACE FUNCTION next_business_day(from_date date default CURRENT_DATE, num_days int default 0) RETURNS date
LANGUAGE sql AS $$
    SELECT d FROM business_days(from_date, num_days) WHERE i = num_days
$$;