Re: Getting the amount of overlap when using OVERLAPS

On 23 Sep 2005 11:30:24 -0700, gregstumph(at)gmail(dot)com
<gregstumph(at)gmail(dot)com> wrote:
> I don't think your algorithm for calculating the number of days in the
> overlap will work. Picture a scenario where we have one date range that
> covers 100 days, and another that covers 10 days, and they overlap by 2
> days. Doing "age(<first range>) - age(<second range>)" will return 90
> days, not 2 days.

The other problem with using intervals if that once they exceed 30
days you lose resolution because it starts thinking of the interval in
months.

I wrote this simple function to calculate the days of overlap. I
haven't found any obvious bugs. Please let me know if this is useful
for you or if you see any silly bugs.

CREATE OR REPLACE FUNCTION days_of_overlap(DATE, DATE, DATE, DATE)
RETURNS INTEGER AS
$BODY$

DECLARE
s1 ALIAS FOR $1;
e1 ALIAS FOR $2;
s2 ALIAS FOR $3;
e2 ALIAS FOR $4;
out_days INTEGER := 0;
max_overlap INTEGER := int4smaller(e1-s1,e2-s2);
BEGIN

IF max_overlap < 0 THEN
RAISE EXCEPTION 'invalid date range(s) entered, please enter them
in the format (start date 1, end date 1, start date 2, end date 2)';
ELSIF max_overlap = 0 THEN
RAISE NOTICE 'Zero length date range(s) entered';
END IF;

IF (s1,e1) OVERLAPS (s2,e2) THEN
--RAISE NOTICE 'they overlap!';
IF e1-s1 > e2-s2 THEN
--RAISE NOTICE 's2,e2 is smaller !';
IF (s1,e1) OVERLAPS (s2,INTERVAL '0') THEN
--RAISE NOTICE 's2 overlaps!';
IF (s1,e1) OVERLAPS (e2,INTERVAL '0') THEN
out_days := e2-s2;
ELSE
out_days := e1-s2;
END IF;
ELSIF (s1,e1) OVERLAPS (e2,INTERVAL '0') THEN
--RAISE NOTICE 'e2 overlaps!';
out_days := e2-s1;
ELSE
RAISE EXCEPTION 'logic problem, neither date boundary overlapped.';
END IF;
ELSE
--RAISE NOTICE 's1,e1 is smaller !';
IF (s1,INTERVAL '0') OVERLAPS (s2,e2) THEN
--RAISE NOTICE 's1 overlaps!';
IF (e1,INTERVAL '0') OVERLAPS (s2,e2) THEN
out_days := e1-s1;
ELSE
out_days := e2-s1;
END IF;
ELSIF (e1,INTERVAL '0') OVERLAPS (s2,e2) THEN
--RAISE NOTICE 'e1 overlaps!';
out_days := e1-s2;
END IF;
END IF;
ELSE
RAISE NOTICE 'date ranges do not overlap.';
END IF;

-- little sanity check here
--RAISE NOTICE 'out_days -> %',out_days;
--RAISE NOTICE 'max_overlap -> %',max_overlap;
IF out_days > max_overlap THEN
RAISE EXCEPTION 'logic error found! result is bigger than maximum possible';
END IF;

RETURN out_days;
END;

$BODY$ LANGUAGE 'plpgsql';