CODEWITHSUNDEEP
mysqldate
omega
omega

Reputation: 43833

How to check if two date ranges overlap in mysql?

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

Upvotes: 8

Views: 4800

Answers (4)

Born2Code
Born2Code

Reputation: 1075

I think you have one extra test. The first two statements will cover if the two ranges overlap on one end, or if the first range full falls within the second.

You will then need to test whether the second range is fully enclosed in the first, anything else will be covered in the first two tests. For that, you only need to test the start or the end.

Thus, I think this will work

 $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}'))";

Upvotes: -1

Hussein Akar
Hussein Akar

Reputation: 425

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.

Upvotes: 1

DevWL
DevWL

Reputation: 18840

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)

Upvotes: 2

spencer7593
spencer7593

Reputation: 108370

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
    )

Upvotes: 11

Related Questions