Function overlaps_interval

Folks,
I wrote this PL/pgSQL function for my current project, and thought it
would be generally useful.  An expansion of the builtin
overlaps(dt1,dt2,dt3,dt4) function, this function returns the interval
of time for which the two datetime ranges overlap.
Roberto, please include this in your online PL/pgSQL function library.

CREATE FUNCTION overlap_interval(DATETIME, DATETIME, DATETIME, DATETIME)
RETURNS INTERVAL AS '
DECLAREbegin1 ALIAS for $1;end1 ALIAS for $2;begin2 ALIAS for $3;end2 ALIAS for $4;overlap_amount INTERVAL;
BEGIN--test for overlap using the ovelap function.--if not found, return 0 interval.
IF NOT overlaps(begin1, end1, begin2, end2) THEN    RETURN ''00:00:00''::INTERVAL;END IF;
--otherwise, test for the various forms of overlap
IF begin1 < begin2 THEN    IF end1 < end2 THEN        overlap_amount := end1 - begin2;    ELSE        overlap_amount :=
end2- begin2;    END IF;ELSE    IF end1 < end2 THEN        overlap_amount := end1 - begin1;    ELSE
overlap_amount:= end2 - begin1;    END IF;END IF;
 
RETURN overlap_amount;
END;'
LANGUAGE 'plpgsql';        
-Josh Berkus


______AGLIO DATABASE SOLUTIONS___________________________                                      Josh Berkus Complete
informationtechnology      josh@agliodbs.com  and data management solutions       (415) 565-7293 for law firms, small
businesses       fax 621-2533   and non-profit organizations.      San Francisco