From: | "A(dot) Kretschmer" <andreas(dot)kretschmer(at)schollglas(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Intersection of two date interval |
Date: | 2007-01-26 09:50:08 |
Message-ID: | 20070126095007.GD15591@a-kretschmer.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Achilleas Mantzios | 2007-01-26 10:02:24 | Re: Intersection of two date interval |
Previous Message | Tomas Vondra | 2007-01-26 09:41:26 | implementing (something like) UNIQUE constraint using PL/pgSQL |