From: Gavin Flower <GavinFlower@archidevsys.co.nz>
To: Jasen Betts <jasen@xnet.co.nz>
Cc: pgsql-sql@postgresql.org
Subject: Re: checking the gaps in intervals
Date: Fri, 12 Oct 2012 09:25:14 +1300
Message-ID: <50772B2A.5000603@archidevsys.co.nz> (raw)
In-Reply-To: <k4qlvt$vh6$1@reversiblemaps.ath.cx>
References: <-3205649711969780110@unknownmsgid>
<k4qlvt$vh6$1@reversiblemaps.ath.cx>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: GavinFlower@archidevsys.co.nz, jasen@xnet.co.nz
Subject: Re: checking the gaps in intervals
In-Reply-To: <50772B2A.5000603@archidevsys.co.nz>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox