Обсуждение: checking the gaps in intervals
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
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°
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 FROMview_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°
On 06/10/12 11:42, Anton Gavazuk wrote:
How about something like the following?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
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
/**/;/**/.
On 06/10/12 11:42, Anton Gavazuk wrote:
If the periods _NEVER_ overlap, you can also use this this approachHi 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
(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
/**/;/**/
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
selectdistinct (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
selectdistinct (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
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