CODEWITHSUNDEEP
postgresqldatenumbersadditiondays
hotcoder
hotcoder

Reputation: 3256

Add business days in date in Postgresql function

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?

Upvotes: 1

Views: 836

Answers (1)

S-Man
S-Man

Reputation: 23766

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

Upvotes: 2

Related Questions