Re: checking the gaps in intervals

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
selectdistinct (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
selectdistinct (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