Check time interval using SQL condition

Question

I'm storing some intervals in the SQL, example:

id INT
from DATE
to DATE

How can I check, using only one condition (if possible) if a NEW interval conflict with a existing one?

Examples:

|-----------| (from 1 to 5)
    |-----------| (from 2 to 6)
       |--| (from 3 to 4)
                   |--| (from 7 to 8)

Every interval (in the first three) has some conflict with the other two intervals... Except the last one that's alone.

--

This check can be achieved using some condition like:

WHERE (`from` <= $FROM and `to` >= $TO)

But this only check intervals that contains the new one... Not the other intervals that has some intersections OR the ones that's inside the new one.

Maybe something like this?

WHERE NOT (`from` < $FROM and `to` < $TO) AND NOT (`from` > $FROM and `to` > $TO)

Obs.: I need to find the collisions to alert the user that this new period already exist or get in conflict with an existin one.

Answer 1

WHERE ($TO >= `from` AND $FROM <= `to`)

Note this works for the case where the new range overlaps the whole range, when it only partially overlaps and when it encompasses it as well.

Answer 2

I came across this question and just wanted to try to show how a truth table can identify the reduced logic that Paul already posted.

Assume you have an interval from [ to ] that you want to check against from { to }.

This translates to the following truth table:

 #   [ < {   [ < }   ] < {   ] < }    Collision?  Example
 1     T       T       T       T          F       [ ] { }
 2     T       T       T       F          T       [ } { ] *
 3     T       T       F       T          T       [ { ] }
 4     T       T       F       F          T       [ { } ]
 5     T       F       T       T          T       ] } [ { *
 6     T       F       T       F          T       } [ ] { *
 7     T       F       F       T          Contradiction
 8     T       F       F       F          T       } [ { ] *
 9     F       T       T       T          T       ] { [ } *
10     F       T       T       F          Contradiction
11     F       T       F       T          T       { [ ] }
12     F       T       F       F          T       { [ } ]
13     F       F       T       T          T       ] { } [ *
14     F       F       T       F          T       } ] { [ *
15     F       F       F       T          T       { ] } [ *
16     F       F       F       F          F       { } [ ]

Looking at this truth table, the simplest expression to identify collisions is:

NOT ( [ < { AND [ < } AND ] < { AND ] < } ) AND NOT ( [ >= { AND [ >= } AND ] >= { AND ] >= } )

However we know that, since { < } and [ < ], this reduces to

NOT ( [ < { AND ] < { ) AND NOT ( [ >= } AND ] >= } )

Which corresponds to the SQL:

WHERE NOT ('from' < $FROM and 'to' < $FROM ) AND NOT ('from' > $TO and 'to' > $TO) (similar to what @TiuTalk suggested).

However, we have already assumed that { < } and [ < ]. This is critical. Look at the rows marked * in the truth table. In those rows, either } < { or ] < [. We know those won't happen. Plus, some rows imply completely contradictory things like } < { AND { < } which we know to be impossible. Eliminating all these rows gives just 6 rows:

 #   [ < {   [ < }   ] < {   ] < }    Collision?  Example
 1     T       T       T       T          F       [ ] { }
 3     T       T       F       T          T       [ { ] }
 4     T       T       F       F          T       [ { } ]
11     F       T       F       T          T       { [ ] }
12     F       T       F       F          T       { [ } ]
16     F       F       F       F          F       { } [ ]

Here, we can see that just the middle two clauses determine whether there's a collision. Namely, ( [ < } ) AND NOT ( ] < { ). This is equivalent to ( [ < } ) AND ( ] >= { ) (negating the second comparator) which is equivalent to the SQL WHERE ('from' < $TO AND 'to' >= $FROM). This is semantically equivalent to Paul's clause (barring working through the <= to the end).