Detect overlapping date ranges from the same table

Question

I have a table with the following data

PKey  Start       End         Type
====  =====       ===         ====
01    01/01/2010  14/01/2010  S
02    15/01/2010  31/01/2010  S
03    05/01/2010  06/01/2010  A

And want to get the following results

PKey  Start       End         Type
====  =====       ===         ====
01    01/01/2010  14/01/2010  S
03    05/01/2010  06/01/2010  A

Any ideas on where to start? A lot of the reading I've done suggests I need to create entries and for each day and join on matching days, is this the only way?

Cruzeiro

01 does not overlap 02. Why would you want to get rid of 02? – Absorptance

I understood it to mean: select rows where the interval defined in the row overlaps with any interval defined in other rows. – Camfort

I'm looking for overlapping records between Type S and A – Cruzeiro

Answer 1

If you already have entries for each day that should work, but if you don't the overhead is significant, and if that query is used often, if will affect performance.

If the data is in this format, you can detect overlaps using simple date arithmetic, because an overlap is simply one interval starting after a given interval, but before the given is finished, something like

select dr1.* from date_ranges dr1
inner join date_ranges dr2
on dr2.start > dr1.start -- start after dr1 is started
  and dr2.start < dr1.end -- start before dr1 is finished

If you need special handling for interval that are wholly within another interval, or you need to merge intervals, i.e.

PKey  Start       End         Type
====  =====       ===         ====
01    01/01/2010  20/01/2010  S
02    15/01/2010  31/01/2010  S

yielding

Start       End         Type
=====       ===         ====
01/01/2010  31/01/2010  S

you will need more complex calculation.

In my experience with this kind of problems, once you get how to do the calculation by hand, it's easy to transfer it into SQL :)

Territorial

FYI, this sql is a little incomplete. It does detect start date overlaps, but not end date overlaps (i.e. where the end date lands in the middle of another date range). You'll need another clause pair for that... 'or dr2.end > dr1.start and dr2end < dr1.end'. Watch your ands here as you may need some parenthesis around this if there's any other criteria (e.g. 'S' vs 'A') – Roberge

@ReginaldBlue, (aside from ranges completely within one another) that will just invert the order of dr1 and dr2 – Territorial

I think this solution is broken when two date ranges share the same start date. It would not detect that 1/1-1/5 overlaps 1/1-1/6. And sadly, switching the operators to allow equality would cause each row to see itself as an overlap. – Neutrino

This should avoid the problem where the ranges share a start date or share an end date: select dr1.* from date_ranges dr1 inner join date_ranges dr2 on dr2.start < dr1.end and dr1.start < dr2.end – Neutrino

This is my solution in overlap it also solve the issue when the startdate are equal: select dr1.* from date_ranges dr1 inner join date_ranges dr2 on (dr2.start >= dr1.start and dr2.end <= dr1.end) or (dr2.start > dr.start and dr2.start < dr1.end) – Retroactive

Answer 2

When I needed to compare two time spans in SQL for overlap, here are the four scenarios I could think of:

1. Span1 start is between Span2 start and Span2 end
2. Span1 end is between Span2 start and Span2 end
3. Span1 start and end are both between Span2 start and Span2 end
4. Span2 start and end are both between Span1 start and Span1 end

Here is the OR statement I created to capture these scenarios (in my case Oracle SQL):

and (
    s1.start between s2.start and s2.end
    OR
    s1.end between s2.start and s2.end
    OR
    s2.start between s1.start and s1.end
)

Klemm

I ended up with this sql: ... AND ( ({$e->start} >= `start` AND {$e->start} < `end`) OR ({$e->end} > `start` AND {$e->end} <= `end`) OR (`start` >= {$e->start} AND `start` < {$e->end}) ) ... – Kilkenny

Answer 3

If you are using PostgreSQL, simply use the built-in overlap operator

SELECT (DATE '2021-01-01', DATE '2021-04-09') 
OVERLAPS (DATE '2021-01-20', DATE '2021-02-10');

Beckett

I wasn't but that's handy to know, thanks! – Cruzeiro

Answer 4

Perhaps:

SELECT A.PKey, A.Start, A.End, A.Type
FROM calendar AS A, calendar AS B
WHERE (p.pkey<>a.pkey
AND b.start>=a.start
AND b.end<=a.end)
OR (b.pkey<>a.pkey
AND b.start<=a.start
AND b.end>=a.end)

Polycarp

Answer 5

select A.*
from MyTable A
inner join MyTable B
on (B.start <= A.end)
and (B.end >= A.start)

or something like that (assuming dates are not nullable and equal dates count as an overlap).

Camfort

This will detect intervals that are sub-intervals in the table, it will not detect monday-friday and tuesday-sunday scenario. – Territorial

I don't follow you: if A represents Mon-Fri and B represents Tues-Sun, then the first condition is fulfilled. – Camfort

my mistake, assumed the or was an and. This however will return an intersection for any B that starts before A's end, even if it's before A's start (A=fri-sun; B=mon-tue) – Territorial

oops - that OR was supposed to be an AND (now edited). Which will fulfill the situation described in your first comment, since we're free to decide which interval is B and which is A. – Camfort

It will also pick up each record, because for every record, the start is greater than or equal to its own start, ditto end. – Polycarp

Answer 6

In MySQL you basically need:

SELECT COUNT(*) FROM date_ranges AS A, date_ranges AS B WHERE A.id <> B.id AND A.id > B.id AND A.end_at > B.start_at AND B.end_at > A.start_at

> in the second and the third statement can be replaced with >= to follow includes matching.

This topic is related to the "Allen's Interval Algebra" and there are some more reading on this can be found by those links:

• http://www.ics.uci.edu/~alspaugh/cls/shr/allen.html
• http://salman-w.blogspot.com.es/2012/06/sql-query-overlapping-date-ranges.html

Tsarevitch

Answer 7

I had to do a very similar thing for to stop duplicate holiday being entered into a table. it was in access and written to a temptable on input so had to query it in VBA SQL:

 stCommandText = "SELECT " _
                    & "* " _
                    & "FROM " _
                    & "TableName a, " _
                    & "TableName b " _
                    & "WHERE " _
                    & "a.ID = b.ID " _
                    & "AND a.Startdate >= b.Startdate AND a.StartDate <= b.EndDate " _
                    & "AND a.AutoNo <> b.AutoNo "

Ali

Answer 8

We've all needed this kind of overlapping predicate in our queries for quite some time and I think I've found a really simple solution here.

In my application, as an example, I have policies that have the same Policy Number but maybe the Policy Description changes from one fiscal year to the next. When a user is entering a new record (same Policy Number, different Policy Description), I needed a way to tell if that policy already exists for the specified time range. If the new Policy Effective/Expiration dates overlap with whatever is already in the database, I needed to error out and tell the user why their input was not correct.

To do this, I went with the following predicate statement:

AND @_expiration >= EffectiveDate AND ExpirationDate >= @_effective

Hopefully someone else finds this as useful as I have.

Pell

Answer 9

With respect to others, all the previous answers are not accurate. The only fully accurate way is:

SELECT * FROM YourTable T1 
JOIN YourTable T2 ON T1.PKey <> T2.PKey 
AND (T1.[Start] BETWEEN T2.[Start] AND T2.[End] OR T1.[End] BETWEEN T2.[Start] AND T2.[End])

Kolyma

Answer 10

BTW - If you don't have a unique id , against your dates you can do this is oracle..FYI

with date_ranges
as
(
SELECT 
     rownum as pkey,
    date_ranges.*
FROM  date_ranges
) 
select 
dr1.* 
from 
date_ranges dr1 , date_ranges dr2
where  dr1.pkey > dr2.pkey
AND dr1.end_dt >= dr2.start_dt 
AND dr2.end_dt >= dr1.start_dt

Colostomy

Answer 11

Sql='SELECT task_id, task_start_date, task_due_date FROM (wba_task) WHERE (task_start_date <="2016-07-13" AND task_due_date >="2016-07-25") OR (task_due_date BETWEEN "2016-07-13" and "2016-07-25")';

Codeigniter Query is below.

$fromdaysDate="2016-07-13";//changed date
$todaysDate="2016-07-25";//changed date
$this->db->select('task_id,task_start_date, task_due_date'); 
$this->db->where('task_start_date <="'.date('Y-m-d', strtotime($fromdaysDate)).'"');
$this->db->where('task_due_date >="'.date('Y-m-d', strtotime($todaysDate)).'"');    
$this->db->or_where('task_due_date BETWEEN "'. date('Y-m-d', strtotime($fromdaysDate)). '" and "'. date('Y-m-d', strtotime($todaysDate)).'"');   
$alltask=$this->db->get('wba_task')->result_array();
echo $this->db->last_query();

get all overlap data form database....

Chung

Answer 12

To solve if the date is overlapping or not:

Table Creation:

create  table testing (
   id int,
    s_date date,
    e_date date
    
);

Loading Data:

INSERT INTO testing ( id,s_date,e_date)
VALUES ('1','1/1/2020','1/31/2020'),
('2',   '1/16/2020',    '1/26/2020'),
('3',   '1/28/2020',    '2/6/2020'),
('4',   '2/16/2020',    '2/26/2020');

Query:

select id, case when sum(Overlap) > 0 then 'True' else 'False' end as overlap  

from (

select a.*, b.id as ids,  b.e_date, b.s_date
,case when a.s_date < b.e_date then 1 else 0 end as Overlap

from testing a cross join testing b  where a.id <> b.id and a.e_date > b.s_date
) group by 1

Dallas

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center. – Gies