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

Question

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.

See http://stackoverflow.com/questions/6571538/checking-a-table-for-time-overlap — Barmar, Oct 25 '16 at 19:59

score 3 · Accepted Answer · 2016-10-25T20:21:58.550

3

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.

edited Oct 25 '16 at 20:21

answered Oct 25 '16 at 20:15

i see this should help me @a_horse_with_no_name, thank you very much – Youcef LAIDANI Oct 25 '16 at 20:20

score 2 · Answer 2 · answered Oct 25 '16 at 20:04

2

That's way too complicated. Look at all the possibilities: If "x" and "y" are the values you need to test, and "a" and "b" are your database values, then here's how it all can lay out:

           a   b
1      x y           - x<a & y<a   - no overlap
2      x   y         - x<a & y=a
3      x     y       - x<a & a <= y <= b
4      x       y     - x<a & y=b
5      x         Y   - x<a & y>b   - complete overlap
6          x y       - x=a & y<b
7          x   y     - x=a & y=b   - same dates
8          x     y   - x=a & y>b
9            xy      - (x>a & x<b) & (y>a & y<b) - complete overlap
10             x y     - (x>a & x<b) & b=y
11             x y   - x=b & y>b
12               x y - x>a & y>a   - no overlap

Out of all of those, you only care about 1 and 12 - no overlap at all, which means your logic statement is literally just

if ((y < a) || (x > b)) {
    ... no overlap
}

answered Oct 25 '16 at 20:04

Marc B

356,200
43
426
500

thank you @MarcB so my i can use my query like that? or there are another minimised way? or more quick? – Youcef LAIDANI Oct 25 '16 at 20:09
that's about as minimum as you can get. as long as `x – Marc B Oct 25 '16 at 20:10
so there are no other way, my solution is true until now? – Youcef LAIDANI Oct 25 '16 at 20:11
@MarcB: the simplest statement is an `overlaps` condition – Oct 25 '16 at 20:21
that's just the a `not` of the above statement. if they're not NOT overlapping, they are overlapping. then the question is in what way, of which there's 10 choices. – Marc B Oct 25 '16 at 20:24

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

2 Answers2