如何检查两个日期范围是否在mysql中重叠? [英] How to check if two date ranges overlap in mysql?

问题描述

在mysql中，如何检查两个日期范围是否重叠?

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

我有这个:

注意:我们有p.date_started <= p.date_finished但dateA可以等于dateB或小于dateB或大于dateB.

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.

代码:

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

有更好的方法吗?

谢谢

推荐答案

如果我们保证date_started，datefinished，$DateA和$DateB不为NULL，并且我们保证date_started不大于date_finished ...

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`

视觉上:

      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 )

我们可以很容易地否定它，当 是重叠"时返回"true":

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

NOT ( a > f OR b < s )

将其转换为SQL:

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

这篇关于如何检查两个日期范围是否在mysql中重叠?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！