问题描述
在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中重叠?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!