원본출처 : 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';