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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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
/**/;/**/.
From | Date | Subject | |
---|---|---|---|
Next Message | air | 2012-10-06 12:47:46 | How to make this CTE also print rows with 0 as count? |
Previous Message | Andreas Kretschmer | 2012-10-06 07:55:21 | Re: checking the gaps in intervals |