To: pgsql-sql@postgresql.org
From: Jasen Betts <jasen@xnet.co.nz>
Subject: Re: checking the gaps in intervals
Date: 7 Oct 2012 01:30:37 GMT
Organization: Dis (not Dat) Organisation
Lines: 54
Message-ID: <k4qlvt$vh6$1@reversiblemaps.ath.cx>
References: <-3205649711969780110@unknownmsgid>
Mime-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
User-Agent: slrn/pre1.0.0-18 (Linux)

On 2012-10-05, Anton Gavazuk <antongavazuk@gmail.com> wrote:
> Hi dear community,
>
> Have probably quite simple task but cannot find the solution,
>
> Imagine the table A with 2 columns start and end, data type is date
>
> start          end
> 01 dec.     10 dec
> 11 dec.     13 dec
> 17 dec.     19 dec
> .....
>
> If I have interval, for example, 12 dec-18 dec, how can I determine
> that the interval cannot be fully covered by values from table A
> because of the gap 14-16 dec? Looking for solution and unfortunately
> nothing has come to the mind yet...

perhaps you can do a with-recursive query ?

create temp table Gavazuk 
      (id serial primary key, start date ,fin date);
insert into Gavazuk (start,fin) 
values ('2012-12-01','2012-12-10')
      ,('2012-12-11','2012-12-13')
      ,('2012-12-17','2012-12-19');

-- this version treats ('2012-12-01','2012-12-10') ('2012-12-11','2012-12-13')
-- as contiguous   

with recursive a as (
   select max (fin) as f from Gavazuk  
   where ('2012-12-12') between start and fin
  union all
   select distinct (fin) from gavazuk,a 
   where a.f+1 between start and fin and start <= '2012-12-12'
)
select max(f) >= '2012-12-18' from a;

-- this version treats ('2012-12-01','2012-12-10') ('2012-12-11','2012-12-13')
-- as non-contiguous   

with recursive a as (
   select max (fin) as f from Gavazuk  
   where ('2012-12-12') between start and fin
  union all
   select distinct (fin) from gavazuk,a 
   where a.f between start and fin-1 and start <= '2012-12-12'
)
select max(f) >= '2012-12-18' from a;


-- 
⚂⚃ 100% natural