[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-sql
Subject:    Re: [SQL] checking the gaps in intervals
From:       Gavin Flower <GavinFlower () archidevsys ! co ! nz>
Date:       2012-10-11 20:25:14
Message-ID: 50772B2A.5000603 () archidevsys ! co ! nz
[Download RAW message or body]

On 07/10/12 14:30, Jasen Betts wrote:
> 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;
>
>
Cunning, also much more elegant and concise than my solutions!

Cheers,
Gavin


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[prev in list] [next in list] [prev in thread] [next in thread]