From: Jasen Betts <jasen@xnet.co.nz>
To: pgsql-sql@postgresql.org
Subject: Re: checking the gaps in intervals
Date: 7 Oct 2012 01:30:37 GMT
Message-ID: <k4qlvt$vh6$1@reversiblemaps.ath.cx> (raw)
References: <-3205649711969780110@unknownmsgid>
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;
--
⚂⚃ 100% natural
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: jasen@xnet.co.nz
Subject: Re: checking the gaps in intervals
In-Reply-To: <k4qlvt$vh6$1@reversiblemaps.ath.cx>
* 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