Re: Getting the amount of overlap when using OVERLAPS

On 23 Sep 2005 11:30:24 -0700, gregstumph@gmail.com
<gregstumph@gmail.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';