--
-- a simple try to calculate the intersect of 2 intervalls
-- not fully testet!!!
--

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;


-- from Bruno Wolff III

--Postgres (at least since 8.1) has GREATEST and LEAST and they provide the
--needed functions.
--And you should be able to subtract two dates (not timestamps) to get an
--integer. (And to count the number of days rather than the time between, you
--need to add 1.)
--You also will want to put a floor on the overlap so that you don't get
--negative values.
--So in your case the query would look something like:

--bruno=> SELECT GREATEST(0, LEAST('2007-01-20'::date, '2007-01-21'::date) -
--bruno(> GREATEST('2007-01-10'::date, '2007-01-13'::date) + 1);
-- greatest
-- ----------
-- 
