From: | Gavin Flower <GavinFlower(at)archidevsys(dot)co(dot)nz> |
---|---|
To: | Jasen Betts <jasen(at)xnet(dot)co(dot)nz> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: checking the gaps in intervals |
Date: | 2012-10-11 20:25:14 |
Message-ID: | 50772B2A.5000603@archidevsys.co.nz |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
On 07/10/12 14:30, Jasen Betts wrote:
> 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;
>
>
Cunning, also much more elegant and concise than my solutions!
Cheers,
Gavin
From | Date | Subject | |
---|---|---|---|
Next Message | Andreas | 2012-10-14 17:43:13 | rolling statistic probaply a window function? |
Previous Message | Sebastien FLAESCH | 2012-10-11 14:13:55 | Re: Database object names and libpq in UTF-8 locale on Windows |