How to check if two date ranges overlap in mysql?

Question

In mysql, how can I check if two date ranges overlap?

I have this:

Note: We have that p.date_started <= p.date_finished but dateA can be equal to dateB or smaller than dateB or bigger than dateB.

CODE:

    $query = "SELECT u.first_name, u.last_name, u.avatar_filename, p.id, p.user_id, p.address, p.topic, p.latitude, p.longitude, d.name AS department_name
              FROM user u
              JOIN placement p ON p.user_id=u.id
              JOIN department d ON d.id = u.department_id
              WHERE p.active=1 AND (('{$dateA}' BETWEEN p.date_started AND p.date_finished) OR 
                                    ('{$dateB}' BETWEEN p.date_started AND p.date_finished) OR
                                    (p.date_started BETWEEN '{$dateA}' AND '{$dateB}') OR
                                    (p.date_finished BETWEEN '{$dateA}' AND '{$dateB}'))";

Is there a better way of doing this?

Thanks

Answer 1

If we are guaranteed that date_started, datefinished, $DateA and $DateB are not NULL, and we're guaranteed that date_started is not greater than date_finished...

`s` represents `date_started`
`f` represents `date_finished`
`a` represents the smaller of `$DateA` and `$DateB`
`b` represents the larger of `$DateA` and `$DateB`

Visually:

      s-----f       overlap
 -----+-----+-----  -------  
  a-b |     |        NO
  a---b     |        YES
  a-----b   |        YES
  a---------b        YES
  a-----------b      YES
      a---b |        YES
      a-----b        YES
      a-------b      YES
      | a-b |        YES
      | a---b        YES     
      | a-----b      YES     
      |     a-b      YES
      |     | a-b    NO

We can easily detect when there's no "overlap" of the ranges:

( a > f OR b < s )

And we can easily negate that to return "true" when there is an "overlap":

NOT ( a > f OR b < s )

Converting that to SQL:

NOT ( GREATEST('{$dateA}','{$dateB}') < p.date_started
      OR LEAST('{$dateA}','{$dateB}') > p.date_finished
    )

Answer 2

Logic that checks for overlap

A -> B represent one range and a -> b represent another range.

overlap scenario 1

A           B
*----------*
     a           b
     *----------*

overlap scenario 2

     A           B
     *----------*
a           b
*----------*

overlap scenario 3

     A           B
     *----------*
a                        b
*---------------------*

overlap scenario 4 - is when a or b is same as A or B

a == A || a == B || b == A || b == B  

Different scenarios would get fulfilled by checking against does four scenarios.

Our logic should check for all scenarios.

(scenario 1 (+4) || scenario 2 (+4)  || scenario 3 )

(a >= A && a <= B) || (b >= A && b <= B) || (a < A && b > B)

Answer 3

You can cover all date overlapping cases even when to-date in database can possibly be null as follows:

SELECT * FROM `tableName` t
WHERE t.`startDate` <= $toDate
AND (t.`endDate` IS NULL OR t.`endDate` >= $startDate);

This will return all records that overlaps with the new start/end dates in anyway.