Skip site navigation (1) Skip section navigation (2)

Re: checking the gaps in intervals

From: Gavin Flower <GavinFlower(at)archidevsys(dot)co(dot)nz>
To: Anton Gavazuk <antongavazuk(at)gmail(dot)com>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: checking the gaps in intervals
Date: 2012-10-06 08:04:23
Message-ID: 506FE607.50608@archidevsys.co.nz (view raw or flat)
Thread:
Lists: pgsql-sql
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
/**/;/**/.

In response to

pgsql-sql by date

Next:From: airDate: 2012-10-06 12:47:46
Subject: How to make this CTE also print rows with 0 as count?
Previous:From: Andreas KretschmerDate: 2012-10-06 07:55:21
Subject: Re: checking the gaps in intervals

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group