Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29])
	by malur.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <GavinFlower@archidevsys.co.nz>) id 1TKPNL-0002hO-JU
	for pgsql-sql@postgresql.org; Sat, 06 Oct 2012 08:04:43 +0000
Received: from mbx.knossos.net.nz ([202.160.48.10])
	by magus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <GavinFlower@archidevsys.co.nz>) id 1TKPNG-0007Ly-Ru
	for pgsql-sql@postgresql.org; Sat, 06 Oct 2012 08:04:42 +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 q9684R5h017394
	(version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NOT);
	Sat, 6 Oct 2012 21:04:27 +1300
Message-ID: <506FE607.50608@archidevsys.co.nz>
Date: Sat, 06 Oct 2012 21:04:23 +1300
From: Gavin Flower <GavinFlower@archidevsys.co.nz>
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: Anton Gavazuk <antongavazuk@gmail.com>
CC: pgsql-sql@postgresql.org
Subject: Re: checking the gaps in intervals
References: <-3205649711969780110@unknownmsgid>
In-Reply-To: <-3205649711969780110@unknownmsgid>
Content-Type: multipart/alternative;
	boundary="------------030307030904010209010807"
X-Pg-Spam-Score: -1.9 (-)
X-Archive-Number: 201210/29
X-Sequence-Number: 36900

This is a multi-part message in MIME format.
--------------030307030904010209010807
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On 06/10/12 11:42, 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...
>
> Thanks,
> Anton
>
>
How about something like the following?

Cheers,
Gavin

DROP TABLE IF EXISTS period;

CREATE TABLE period
(
     id          serial PRIMARY KEY,
     start_date  date,
     end_date    date
);


INSERT INTO period (start_date, end_date) VALUES
('2012-12-01', '2012-12-10'),
('2012-12-11', '2012-12-13'),
('2012-12-17', '2012-12-19'),
('2012-12-20', '2012-12-25');


WITH RECURSIVE
     slot (start_date, end_date) AS
     (
             SELECT
                 p1.start_date,
                 p1.end_date
             FROM
                 period p1
             WHERE
                 NOT EXISTS
                 (
                     SELECT
                         1
                     FROM
                         period p2
                     WHERE
                         p1.start_date = p2.end_date + 1
                 )
         UNION ALL
             SELECT
                 s1.start_date,
                 p3.end_date
             FROM
                 slot s1,
                 period p3
             WHERE
                     p3.start_date = s1.end_date + 1
                 AND p3.end_date > s1.end_date
     )

SELECT
     s3.start_date,
     MIN(s3.end_date)
FROM
     slot s3
WHERE
         s3.start_date <= '2012-12-01'
     AND s3.end_date >= '2012-12-18'
GROUP BY
     s3.start_date
/**/;/**/.


--------------030307030904010209010807
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<html>
  <head>
    <meta content="text/html; charset=ISO-8859-1"
      http-equiv="Content-Type">
  </head>
  <body text="#000000" bgcolor="#FFFFFF">
    <div class="moz-cite-prefix">On 06/10/12 11:42, Anton Gavazuk wrote:<br>
    </div>
    <blockquote cite="mid:-3205649711969780110@unknownmsgid" type="cite">
      <pre wrap="">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...

Thanks,
Anton


</pre>
    </blockquote>
    <font face="Courier New, Courier, monospace">How about something
      like the following?<br>
      <br>
      Cheers,<br>
      Gavin</font><br>
    <font face="Courier New, Courier, monospace"><br>
      DROP TABLE IF EXISTS period;<br>
      <br>
      CREATE TABLE period<br>
      (<br>
      &nbsp;&nbsp;&nbsp; id&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; serial PRIMARY KEY,<br>
      &nbsp;&nbsp;&nbsp; start_date&nbsp; date,<br>
      &nbsp;&nbsp;&nbsp; end_date&nbsp;&nbsp;&nbsp; date<br>
      );<br>
      <br>
      <br>
      INSERT INTO period (start_date, end_date) VALUES<br>
      ('2012-12-01', '2012-12-10'),<br>
      ('2012-12-11', '2012-12-13'),<br>
      ('2012-12-17', '2012-12-19'),<br>
      ('2012-12-20', '2012-12-25');<br>
      <br>
      <br>
      WITH RECURSIVE <br>
      &nbsp;&nbsp;&nbsp; slot (start_date, end_date) AS<br>
      &nbsp;&nbsp;&nbsp; (<br>
      &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SELECT <br>
      &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; p1.start_date, <br>
      &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; p1.end_date<br>
      &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FROM<br>
      &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; period p1<br>
      &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; WHERE<br>
      &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; NOT EXISTS<br>
      &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (<br>
      &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SELECT<br>
      &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1<br>
      &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FROM<br>
      &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; period p2<br>
      &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; WHERE<br>
      &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; p1.start_date = p2.end_date + 1<br>
      &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; )<br>
      &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; UNION ALL<br>
      &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SELECT <br>
      &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; s1.start_date, <br>
      &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; p3.end_date<br>
      &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FROM<br>
      &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; slot s1,<br>
      &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; period p3<br>
      &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; WHERE<br>
      &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; p3.start_date = s1.end_date + 1<br>
      &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; AND p3.end_date &gt; s1.end_date<br>
      &nbsp;&nbsp;&nbsp; )<br>
      <br>
      SELECT<br>
      &nbsp;&nbsp;&nbsp; s3.start_date, <br>
      &nbsp;&nbsp;&nbsp; MIN(s3.end_date)<br>
      FROM<br>
      &nbsp;&nbsp;&nbsp; slot s3<br>
      WHERE<br>
      &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; s3.start_date &lt;= '2012-12-01'<br>
      &nbsp;&nbsp;&nbsp; AND s3.end_date &gt;= '2012-12-18'<br>
      GROUP BY<br>
      &nbsp;&nbsp;&nbsp; s3.start_date<br>
      /**/;/**/</font><font face="Courier New, Courier, monospace">.</font><br>
    <br>
  </body>
</html>

--------------030307030904010209010807--