On 2012-10-05, Anton Gavazuk <antongava...@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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql