Add business days in date in Postgresql function

Question

I have to convert a SQL Server procedure to PostgreSQL that adds number of days to given date. I did some research and found out a solution but it only works for positive number of days, when I pass it a negative number (to move back from given date) it never works. Following is the function:

CREATE OR REPLACE FUNCTION public.add_business_day(
    from_date date,
    num_days integer)
    RETURNS date
    LANGUAGE 'sql'

    COST 100
    VOLATILE 
    
AS $BODY$
    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
$BODY$;

Is there any fix/modification or alternative to this logic to make it work for both positive and negative number of days?

S-Man · Accepted Answer · 2020-11-19 08:33:40Z

2

demo:db<>fiddle

I believe you need a conditional query for generating the date series. Like this:

select 
    d::date, 
    CASE 
        WHEN num_days >= 0 THEN row_number() over (order by d) - 1
        ELSE row_number() over (order by d DESC) * -1 + 1
    END as row_number,
    extract('dow' from d)
from
    generate_series(
        CASE WHEN num_days >= 0 THEN current_date ELSE current_date + num_days * 2 - 5 END, 
        CASE WHEN num_days >= 0 THEN current_date + num_days * 2 + 5 ELSE current_date END, 
        '1d'
    ) d

This switches the start and end values of the generate_series() function and changes the order for the row_number() window function, to ensure, that it counts negative values in the correct order.

It is possible to remove the CASE clauses from the generate_series() function using the sign() function. With this you can control the interval you want to add, which can be negative, of course:

demo:db<>fiddle

select 
    d::date, 
    CASE 
        WHEN num_days >= 0 THEN row_number() over (order by d) - 1
        ELSE row_number() over (order by d DESC) * -1 + 1
    END as row_number,
    extract('dow' from d)
from
    generate_series(current_date, current_date + num_days * 2 + sign(num_days)::int * 5, sign(num_days)::int * interval '1d') d

edited Nov 19, 2020 at 8:33

answered Nov 18, 2020 at 22:06

S-Man

24k9 gold badges51 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jeremy Over a year ago

He's trying to do business days, not just days

S-Man Over a year ago

@Jeremy Thanks, you were right of course, changed it.

Collectives™ on Stack Overflow

Add business days in date in Postgresql function

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related