Finding overlapping intervals using SQL

Question

I want to extract rows from a table whose interval intersects with an interval specified in the query. Assuming that I have a simple ID, DATE_START, DATE_END table and two query parameters P_DATE_START and P_DATE_END, what is the simplest way of expressing the query so that I find all rows for which [DATE_START, DATE_END] has at least one common element with [P_DATE_START, P_DATE_END]?

Update:

To make the desired outcome clearer, please find a list of input values and expected outcomes below. Colums are DATE_START, DATE_END, P_DATE_START, P_DATE_END, MATCH.

16, 17, 15, 18, YES
15, 18, 16, 17, YES
15, 17, 16, 18, YES
16, 18, 15, 17, YES
16, 17, 18, 19, NO
18, 19, 16, 17, NO

http://stackoverflow.com/questions/143552/comparing-date-ranges/143568#143568 — Martin Smith, Jan 27 '11 at 13:19
@Martin: this seems to be precisely what I'm looking for, thanks! I will vote to close my own question if it is. — Robert Munteanu, Jan 27 '11 at 13:24

score 7 · Accepted Answer · answered Jan 27 '11 at 12:39

7

Even simpler:

SELECT id, date_start, date_end 
FROM thetable 
WHERE date_start <= p_date_end 
AND date_end >= p_date_start

answered Jan 27 '11 at 12:39

Ilya Kogan

21,995
15
85
141

Thanks, I tried this as well. This does not work when `[date_start, date_end]` is completely contained in `[p_date_start, p_date_end]`. – Robert Munteanu Jan 27 '11 at 12:43
@Robert, it does! For example, if [5,6] is completely contained in [1,10], then it's true that 5 <= 10 and 6 >= 1. – Ilya Kogan Jan 27 '11 at 12:44
@Ilya : please see my question update. The check should be symmetric, i.e. if it works with the input `[5,6]` and row value `[1, 10]` it should work with the input `[1,10]` and row value `[5,6]`. – Robert Munteanu Jan 27 '11 at 12:48
@Robert So can you give me an example of data on which this query fails? – Ilya Kogan Jan 27 '11 at 12:50
Try the following (tested on MySQL) `create table intervals ( id bigint unsigned, date_start date, date_end date); insert into intervals(id, date_start, date_end) VALUES ( 1, '2011-01-05', '2011-01-06'); select id from intervals where date_start <= '2011-01-01' and date_end >= '2011-01-10';` – Robert Munteanu Jan 27 '11 at 13:01
@Robert, you confused p_date_start with p_date_end. It should be: `select id from intervals where date_start <= '2011-01-10' and date_end >= '2011-01-01'` – Ilya Kogan Jan 27 '11 at 13:28

score 3 · Answer 2 · answered Jan 27 '11 at 18:58

3

Depending on your dbms, you might be able to use the OVERLAPS operator.

select * from your_table
where (date '2011-01-15', date '2011-01-18') overlaps (date_start, date_end)

answered Jan 27 '11 at 18:58

Mike Sherrill 'Cat Recall'

91,602
17
122
185

KobbyPemson · Answer 3 · 2011-01-30T14:40:10.373

2

SELECT id, date_start, date_end FROM thetable WHERE not (date_end < p_date_start OR p_date_end < date_start )

edited Jan 30 '11 at 14:40

answered Jan 27 '11 at 12:36

KobbyPemson

2,519
1
18
33

Thanks, that was my first attempt. It does not solve the problem where `[p_date_start, p_date_end]` is completely contained in `[date_start, date_end]`. They intersect, but the query does not return. – Robert Munteanu Jan 27 '11 at 12:38

Finding overlapping intervals using SQL

3 Answers3

Linked