Skip site navigation (1) Skip section navigation (2)

Re: checking the gaps in intervals

From: Jasen Betts <jasen(at)xnet(dot)co(dot)nz>
To: pgsql-sql(at)postgresql(dot)org
Subject: Re: checking the gaps in intervals
Date: 2012-10-07 01:30:37
Message-ID: k4qlvt$vh6$1@reversiblemaps.ath.cx (view raw or flat)
Thread:
Lists: pgsql-sql
On 2012-10-05, Anton Gavazuk <antongavazuk(at)gmail(dot)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



In response to

Responses

pgsql-sql by date

Next:From: Kim BisgaardDate: 2012-10-08 15:38:46
Subject: Error 42704
Previous:From: Jasen BettsDate: 2012-10-07 01:02:46
Subject: Re: Calling the CTE for multiple inputs

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group