Youcef Laidani Youcef Laidani - 1 month ago 10
SQL Question

Test if an interval of two dates not participate the other intervals

I have in my table

StartDate
and
EndDate
like this:

+----+----------------------+--------------------+
| id | start_date | end_date |
+----+----------------------+--------------------+
| 1 | 2016-10-24 09:00:00 |2016-10-24 10:30:00 |
| 2 | 2016-10-31 09:00:00 |2016-10-31 10:30:00 |
+----+----------------------+--------------------+


I want to test if my new record (
new start_date
and
end_date
) not participation ONE SECONDE of the other records.

I used to test all the possibilities, but i think this take a long time is there any good solution for this problem, i tryed with this query it work fine, but take time:

select * from myTable where

(new_start_date >= myTable.start_date and new_end_date <= myTable.end_date)

or (new_start_date > myTable.start_date and new_end_date < myTable.end_date)

or (new_start_date > myTable.start_date and new_end_date < myTable.end_date)

or (new_start_date < myTable.start_date and new_end_date > myTable.end_date)

or (new_start_date < myTable.start_date and new_end_date = myTable.end_date)

or (new_start_date = myTable.start_date and new_end_date > myTable.end_date)


Is there any other solution for this problem?

Here is all the possibilities that i can get

if :

newSD_______startdate___newED_____enddate_____ : return false

newSD_______startdate________enddate___newED__ : return false

___startdate___newSD____newED_____enddate_____ : return false

___startdate___newSD_____enddate___newED______ : return false

____startdate_____enddate___newED___newED_________ : return true

___newED___newED___startdate_____enddate__________ : return true


Thank you.

Answer

This will show you all rows that would overlap with the new start/end interval.

select *
from myTable 
where (new_start_date, new_end_date) overlaps (start_date, end_date);

The above is ANSI standard SQL.


If you want, you can create a constraint that prevents inserting overlapping rows.

This is done with an exclusion constraint over a range type - a kind of unique constraint but for ranges.

alter table mytable
  add constraint no_overlap
  exclude using gist (tsrange(start_date, end_date) with &&);

Exclusions constraints are Postgres specific.