How to get end date based on a start date and duration (workdays) in PL/pgSQL?

Question

I need to calculate the end date of a job based on a start date and a duration (an integer representing the number of working days).

I already wrote a function, based on this answer, that computes the number of weekend days between two dates (start and virtual end).

-- 0 Sunday
-- 1 Monday
-- 2 Tuesday
-- 3 Wednesday
-- 4 Thursday
-- 5 Friday
-- 6 Saturday
CREATE OR REPLACE FUNCTION weekend_days(date, date) RETURNS INT AS
$$
SELECT COUNT(days)::INT
    FROM generate_series($1, $2, '1 day') AS days
    WHERE EXTRACT(DOW FROM days) BETWEEN 1 AND 5;
$$
LANGUAGE 'sql' IMMUTABLE STRICT;

I want to create a trigger ON INSERT or ON UPDATE that will populate the corresponding end_date column. Obviously, adding the weekend days to the given duration does not solve the problem.

Recursion

Is there any way to make a recursive function to continue adding the weekends or holidays?

EDIT: Maybe another good example could be found in this answer, that mixes weekend and holidays.

Use a calendar table. You probably care about holidays as well, and a calendar table is the safest approach. — Gordon Linoff, Aug 23 '16 at 07:33
Could you please provide any real example? At the moment, I need an Italian *holiday calendar*. — sentenza, Aug 23 '16 at 07:53
You need to create it on your own. Use `generate_series` to generate the dates. Mark those which are holidays etc :) — Kamil Gosciminski, Aug 23 '16 at 08:35
At the end I built an helper table to skip holidays, using `generate_series` and **the Meeus/Jones/Butcher algorithm** for computing Easter Sundays for the years to come. https://gist.github.com/sentenza/e8f8003e2581d52f12f8c89d663c00d7 — sentenza, Sep 09 '16 at 07:49

score 3 · Accepted Answer · edited Aug 23 '16 at 09:03

CREATE OR REPLACE FUNCTION is_business_day(p_date date)
  RETURNS boolean AS
$BODY$
DECLARE
    is_business_day boolean;
    dow int;
BEGIN
    dow := extract('dow' from p_date);

    IF dow = 6 OR dow = 0
        THEN
        is_business_day := FALSE;
    ELSE
        is_business_day := TRUE;
    END IF;

    RETURN is_business_day ;
END;
$BODY$
  LANGUAGE plpgsql IMMUTABLE;



CREATE OR REPLACE FUNCTION working_date(date start_date, int duration)
  RETURNS date AS
$BODY$
DECLARE 
    ret_date date;
    loop_date date;
BEGIN   
    --add days
    ret_date := start_date + (duration - 1);

    loop_date := start_date + 1; 
    --add extra day for each no business day between start_date and ret_date
    WHILE loop_date <= ret_date LOOP
        IF not is_business_day(loop_date) THEN
            ret_date := ret_date + 1;
        END IF;
        loop_date := loop_date + 1;
    END LOOP;
    --add day if ret_date is no business day
    WHILE not is_business_day(ret_date) LOOP
        ret_date := ret_date + 1;
    END LOOP;

    RETURN ret_date;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE SECURITY DEFINER
  COST 100;

Why is there a `RETURN is_holiday` inside *is_business_day()* function? I presume it should be `RETURN is_business_day`, right? — sentenza, Aug 23 '16 at 08:23
I edited the second function. There wasn't a return instruction and the type returned had to be date and not integer. — sentenza, Aug 23 '16 at 09:03
I've reported all all the useful PgSQL on this [gist](https://gist.github.com/sentenza/e8f8003e2581d52f12f8c89d663c00d7). — sentenza, Jan 04 '17 at 09:42

How to get end date based on a start date and duration (workdays) in PL/pgSQL?

Recursion

1 Answers1