원본출처 : http://www.brasileiro.net/postgres/cookbook/view-one-recipe.adp?recipe_id=137
Submitted on: 04-12-2001
Description:
This function returns the interval of time for which the two datetime ranges overlap. They are an expansion of the builtin overlaps(dt1,dt2,dt3,dt4) function. Contributed by Josh Berkus.
Code:
-- This function returns the interval of time for which the two
-- datetime ranges overlap.
-- By Josh Berkus
CREATE FUNCTION overlap_interval(DATETIME, DATETIME, DATETIME, DATETIME)
RETURNS INTERVAL AS '
DECLARE
begin1 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';
|