Received: from makus.postgresql.org ([98.129.198.125]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TMPJt-0003Dz-DU for pgsql-sql@postgresql.org; Thu, 11 Oct 2012 20:25:25 +0000 Received: from mbx.knossos.net.nz ([202.160.48.10]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TMPJo-0006wE-46 for pgsql-sql@postgresql.org; Thu, 11 Oct 2012 20:25:21 +0000 Received: from [10.1.1.3] (60-234-150-59.bitstream.orcon.net.nz [60.234.150.59]) (authenticated bits=0) by mbx.knossos.net.nz (8.14.4/8.14.4) with ESMTP id q9BKPFlR016234 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NOT); Fri, 12 Oct 2012 09:25:15 +1300 Message-ID: <50772B2A.5000603@archidevsys.co.nz> Date: Fri, 12 Oct 2012 09:25:14 +1300 From: Gavin Flower Organization: ArchiDevSys User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:15.0) Gecko/20120911 Thunderbird/15.0.1 MIME-Version: 1.0 To: Jasen Betts CC: pgsql-sql@postgresql.org Subject: Re: checking the gaps in intervals References: <-3205649711969780110@unknownmsgid> In-Reply-To: Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit X-Pg-Spam-Score: -1.9 (-) X-Archive-Number: 201210/37 X-Sequence-Number: 36908 On 07/10/12 14:30, Jasen Betts wrote: > On 2012-10-05, Anton Gavazuk 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