Re: [SQL] Intersection of two date interval

[A. Kretschmer]

am  Fri, dem 26.01.2007, um 11:39:03 +0200 mailte Suha Onay folgendes:
> Hi,
> 
> How can i find the number of days in the intersection of 2 date interval?
> For example:
> 1st  interval: (10.01.2007, 20.01.2007)
> 2nd interval: (13.01.2007, 21.01.2007)
> The intersection dates are: 13,14,15,16,17,18,19, 20
> The result is: 8
> 
> How can i find the result, 8 in an sql query without using CASE statements?


Some time ago i wrote a function for this, i hope it's helpfull for you:
(not realy tested, be careful!)



create type start_end as (t1 timestamptz, t2 timestamptz);

create or replace function time_intersect (timestamptz, timestamptz, 
timestamptz, timestamptz) returns start_end as $$
declare
        _s1     alias for $1;
        _e1     alias for $2;
        _s2     alias for $3;
        _e2     alias for $4;
        _start  timestamptz;
        _end    timestamptz;
        _return start_end;
begin

        if _s1 < _s2 then
                _start := _s2;
        else
                _start := _s1;
        end if;

        if _e1 < _e2 then
                _end := _e1;
        else
                _end := _e2;
        end if;

        if _start < _end then
                _return.t1 := _start;
                _return.t2 := _end;
        else
                _return.t1 := NULL;
                _return.t2 := NULL;
        end if;

        return _return;
end
$$ language plpgsql;


test=# set datestyle=german;
SET
test=*# select time_intersect('10.01.2007'::date, '20.01.2007'::date, 
'13.01.2007'::date, '21.01.2007'::date);
                    time_intersect
-------------------------------------------------------
 ("13.01.2007 00:00:00 CET","20.01.2007 00:00:00 CET")
(1 row)


test=*# select *, t2-t1 from time_intersect('10.01.2007'::date, 
'20.01.2007'::date, '13.01.2007'::date, '21.01.2007'::date);
           t1            |           t2            | ?column?
-------------------------+-------------------------+----------
 13.01.2007 00:00:00 CET | 20.01.2007 00:00:00 CET | @ 7 days
(1 row)




Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to [EMAIL PROTECTED] so that your
       message can get through to the mailing list cleanly