pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedchecking the gaps in intervals
7+ messages / 4 participants
[nested] [flat]
* checking the gaps in intervals
@ 2012-10-05 22:42 Anton Gavazuk <antongavazuk@gmail.com>
0 siblings, 4 replies; 7+ messages in thread
From: Anton Gavazuk @ 2012-10-05 22:42 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: checking the gaps in intervals
@ 2012-10-06 07:44 Andreas Kretschmer <akretschmer@spamfence.net>
parent: Anton Gavazuk <antongavazuk@gmail.com>
3 siblings, 1 reply; 7+ messages in thread
From: Andreas Kretschmer @ 2012-10-06 07:44 UTC (permalink / raw)
To: pgsql-sql
Anton Gavazuk <antongavazuk@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...
I'm thinking about a solution with DATERANGE (PostgreSQL 9.2)...
Are start and end including or excluding?
Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: checking the gaps in intervals
@ 2012-10-06 07:55 Andreas Kretschmer <akretschmer@spamfence.net>
parent: Andreas Kretschmer <akretschmer@spamfence.net>
0 siblings, 0 replies; 7+ messages in thread
From: Andreas Kretschmer @ 2012-10-06 07:55 UTC (permalink / raw)
To: pgsql-sql
Andreas Kretschmer <akretschmer@spamfence.net> wrote:
> Anton Gavazuk <antongavazuk@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...
>
> I'm thinking about a solution with DATERANGE (PostgreSQL 9.2)...
>
>
> Are start and end including or excluding?
Okay, my solution, quick and dirty ;-)
-- that's your table:
test=*# select * from ag;
date_start | date_end
------------+------------
2012-12-01 | 2012-12-10
2012-12-11 | 2012-12-13
2012-12-17 | 2012-12-19
(3 rows)
-- now some views:
test=*# \d+ view_ag;
View "public.view_ag"
Column | Type | Modifiers | Storage | Description
----------+-----------+-----------+----------+-------------
my_range | daterange | | extended |
View definition:
SELECT daterange(ag.date_start, ag.date_end, '[]'::text) AS my_range
FROM ag;
test=*# \d+ view_ag2;
View "public.view_ag2"
Column | Type | Modifiers | Storage | Description
----------+-----------+-----------+----------+-------------
my_range | daterange | | extended |
my_lag | daterange | | extended |
View definition:
SELECT view_ag.my_range,
lag(view_ag.my_range) OVER (ORDER BY lower(view_ag.my_range)) AS my_lag
FROM view_ag;
test=*# \d+ view_ag3;
View "public.view_ag3"
Column | Type | Modifiers | Storage | Description
-----------+-----------+-----------+----------+-------------
my_range | daterange | | extended |
my_lag | daterange | | extended |
?column? | boolean | | plain |
new_range | daterange | | extended |
View definition:
SELECT view_ag2.my_range, view_ag2.my_lag,
view_ag2.my_lag -|- view_ag2.my_range,
CASE
WHEN view_ag2.my_lag -|- view_ag2.my_range THEN view_ag2.my_lag + view_ag2.my_range
ELSE view_ag2.my_range
END AS new_range
FROM view_ag2;
-- and now my select:
-- first case, the range is not included
test=*# select count(*) from view_ag3 where new_range @> '[2012-12-12,2012-12-18]';
count
-------
0
(1 row)
-- and now, the range is included
test=*# select count(*) from view_ag3 where new_range @> '[2012-12-02,2012-12-13]';
count
-------
1
(1 row)
Hope that helps, but you need the 9.2.
Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: checking the gaps in intervals
@ 2012-10-06 08:04 Gavin Flower <GavinFlower@archidevsys.co.nz>
parent: Anton Gavazuk <antongavazuk@gmail.com>
3 siblings, 0 replies; 7+ messages in thread
From: Gavin Flower @ 2012-10-06 08:04 UTC (permalink / raw)
To: Anton Gavazuk <antongavazuk@gmail.com>; +Cc: 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
/**/;/**/.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: checking the gaps in intervals
@ 2012-10-06 21:58 Gavin Flower <GavinFlower@archidevsys.co.nz>
parent: Anton Gavazuk <antongavazuk@gmail.com>
3 siblings, 0 replies; 7+ messages in thread
From: Gavin Flower @ 2012-10-06 21:58 UTC (permalink / raw)
To: Anton Gavazuk <antongavazuk@gmail.com>; +Cc: 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
>
>
If the periods _NEVER_ overlap, you can also use this this approach
(N.B. The indexing of the period table here, can be used in my previous
solution where I had not considered the indexing seriously!)
Cheers,
Gavin
DROP TABLE IF EXISTS period;
DROP TABLE IF EXISTS target;
CREATE TABLE period
(
start_date date,
end_date date,
PRIMARY KEY (start_date, end_date)
);
CREATE INDEX ON period (end_date);
INSERT INTO period (start_date, end_date) VALUES
('2012-11-21', '2012-11-29'),
('2012-12-01', '2012-12-10'),
('2012-12-11', '2012-12-13'),
('2012-12-17', '2012-12-19'),
('2012-12-20', '2012-12-25');
TABLE period;
CREATE TABLE target
(
start_date date,
end_date date
);
INSERT INTO target (start_date, end_date) VALUES
('2012-12-01', '2012-12-01'),
('2012-12-02', '2012-12-02'),
('2012-12-09', '2012-12-09'),
('2012-12-10', '2012-12-10'),
('2012-12-01', '2012-12-09'),
('2012-12-01', '2012-12-10'),
('2012-12-01', '2012-12-12'),
('2012-12-01', '2012-12-13'),
('2012-12-02', '2012-12-09'),
('2012-12-02', '2012-12-12'),
('2012-12-03', '2012-12-11'),
('2012-12-02', '2012-12-13'),
('2012-12-02', '2012-12-15'),
('2012-12-01', '2012-12-18');
SELECT
t.start_date,
t.end_date
FROM
target t
ORDER BY
t.start_date,
t.end_date
/**/;/**/
SELECT
t1.start_date AS "Target Start",
t1.end_date AS "Target End",
(t1.end_date - t1.start_date) + 1 AS "Duration",
p1.start_date AS "Period Start",
p1.end_date AS "Period End"
FROM
target t1,
period p1
WHERE
(
SELECT
SUM
(
CASE
WHEN p2.end_date > t1.end_date
THEN p2.end_date - (p2.end_date - t1.end_date)
ELSE p2.end_date
END
-
CASE
WHEN p2.start_date < t1.start_date
THEN p2.start_date + (t1.start_date -
p2.start_date)
ELSE p2.start_date
END
+ 1
)
FROM
period p2
WHERE
p2.start_date <= t1.end_date
AND p2.end_date >= t1.start_date
) = (t1.end_date - t1.start_date) + 1
AND p1.start_date <= t1.end_date
AND p1.end_date >= t1.start_date
ORDER BY
t1.start_date,
t1.end_date,
p1.start_date
/**/;/**/
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: checking the gaps in intervals
@ 2012-10-07 01:30 Jasen Betts <jasen@xnet.co.nz>
parent: Anton Gavazuk <antongavazuk@gmail.com>
3 siblings, 1 reply; 7+ messages in thread
From: Jasen Betts @ 2012-10-07 01:30 UTC (permalink / raw)
To: pgsql-sql
On 2012-10-05, Anton Gavazuk <antongavazuk@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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: checking the gaps in intervals
@ 2012-10-11 20:25 Gavin Flower <GavinFlower@archidevsys.co.nz>
parent: Jasen Betts <jasen@xnet.co.nz>
0 siblings, 0 replies; 7+ messages in thread
From: Gavin Flower @ 2012-10-11 20:25 UTC (permalink / raw)
To: Jasen Betts <jasen@xnet.co.nz>; +Cc: pgsql-sql
On 07/10/12 14:30, Jasen Betts wrote:
> On 2012-10-05, Anton Gavazuk <antongavazuk@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;
>
>
Cunning, also much more elegant and concise than my solutions!
Cheers,
Gavin
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2012-10-11 20:25 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-10-05 22:42 checking the gaps in intervals Anton Gavazuk <antongavazuk@gmail.com>
2012-10-06 07:44 ` Andreas Kretschmer <akretschmer@spamfence.net>
2012-10-06 07:55 ` Andreas Kretschmer <akretschmer@spamfence.net>
2012-10-06 08:04 ` Gavin Flower <GavinFlower@archidevsys.co.nz>
2012-10-06 21:58 ` Gavin Flower <GavinFlower@archidevsys.co.nz>
2012-10-07 01:30 ` Jasen Betts <jasen@xnet.co.nz>
2012-10-11 20:25 ` Gavin Flower <GavinFlower@archidevsys.co.nz>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox