blocks|key|313026|text|我曾经写过一个日历应用程序，我就有过这样的查询。我想我用的是这样的东西：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|313027|...+WHERE+new_start+<+existing_end
++++++AND+new_end+++>+existing_start;|code-block|syntax|javascript|313028|更新这肯定会起作用((ns，ne，es，ee)+=+(new_start，new_end，existing_start，existing_end))：|offset|length|style|BOLD|313029|313030|ns+-+ne+-+es+-+ee:不重叠和不匹配(因为ne+<+es)|ordered-list-item|313031|ns+-+es+-+ne+-+ee:重叠和匹配|313032|+es+-+ns+-+ee+-+ne:重叠和匹配|313033|es-+ee+-+ns+-+ne:不重叠和不匹配(因为ns+>+ee+)|313034|<代码>H112es-+ns+-+ne+-+ee:重叠和匹配<代码>H213<代码>ns-ee-+ne:重叠和匹配<代码>H215<代码>G216|313035|313036|这是一个fiddle|313037|entityMap|0|LINK|mutability|MUTABLE|url|http://sqlfiddle.com/#!2/4ab2c/5/0^0|0|0|0|2|0|0|0|0|0|0|0|0|4|6|0|0^^$0|@$1|2|3|4|5|6|7|19|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|1A|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|1B|8|@$I|1C|J|1D|K|L]]|9|@]|A|$]]|$1|M|3|-4|5|6|7|1E|8|@]|9|@]|A|$]]|$1|N|3|O|5|P|7|1F|8|@]|9|@]|A|$]]|$1|Q|3|R|5|P|7|1G|8|@]|9|@]|A|$]]|$1|S|3|T|5|P|7|1H|8|@]|9|@]|A|$]]|$1|U|3|V|5|P|7|1I|8|@]|9|@]|A|$]]|$1|W|3|X|5|6|7|1J|8|@]|9|@]|A|$]]|$1|Y|3|-4|5|6|7|1K|8|@]|9|@]|A|$]]|$1|Z|3|10|5|6|7|1L|8|@]|9|@$I|1M|J|1N|1|1O]]|A|$]]|$1|11|3|-4|5|6|7|1P|8|@]|9|@]|A|$]]]|12|$13|$5|14|15|16|A|$17|18]]]]

I had such a query with a calendar application I once wrote. I think I used something like this:

<pre><code>... WHERE new_start &lt; existing_end
 AND new_end &gt; existing_start;
</code></pre>

UPDATE This should definitely work ((ns, ne, es, ee) = (new_start, new_end, existing_start, existing_end)):

<ol>
<li>ns - ne - es - ee: doesn't overlap and doesn't match (because ne &lt; es)</li>
<li>ns - es - ne - ee: overlaps and matches</li>
<li>es - ns - ee - ne: overlaps and matches</li>
<li>es - ee - ns - ne: doesn't overlap and doesn't match (because ns > ee)</li>
<li>es - ns - ne - ee: overlaps and matches</li>
<li>ns - es - ee - ne: overlaps and matches</li>
</ol>

<hr>

Here is a <a href="http://sqlfiddle.com/#!2/4ab2c/5/0" rel="noreferrer">fiddle</a>

blocks|key|312151|text|SELECT+*+FROM+tbl+WHERE
existing_start+BETWEEN+$newStart+AND+$newEnd+OR+
existing_end+BETWEEN+$newStart+AND+$newEnd+OR
$newStart+BETWEEN+existing_start+AND+existing_end

if+(!empty($result))
throw+new+Exception('We+have+overlapping')|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|312152|这3行sql子句涵盖了所需的4种重叠情况。|unstyled|312153|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|J|8|@]|9|@]|A|$]]|$1|G|3|-4|5|F|7|K|8|@]|9|@]|A|$]]]|H|$]]

<pre><code>SELECT * FROM tbl WHERE
existing_start BETWEEN $newStart AND $newEnd OR 
existing_end BETWEEN $newStart AND $newEnd OR
$newStart BETWEEN existing_start AND existing_end

if (!empty($result))
throw new Exception('We have overlapping')
</code></pre>

These 3 lines of sql clauses cover the 4 cases of overlapping required.

blocks|key|3053348|text|Lamy的答案是好的，但您可以对其进行更多的优化。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3053349|SELECT+*+FROM+tbl+WHERE
existing_start+BETWEEN+$newSTart+AND+$newEnd+OR
$newStart+BETWEEN+existing_start+AND+existing_end|code-block|syntax|javascript|3053350|这将捕获范围重叠的所有四种情况，并排除不重叠的两种情况。|3053351|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

Lamy's answer is good, but you can optimize it a little more.

<pre class="lang-sql prettyprint-override"><code>SELECT * FROM tbl WHERE
existing_start BETWEEN $newSTart AND $newEnd OR
$newStart BETWEEN existing_start AND existing_end
</code></pre>

This will catch all four scenarios where the ranges overlap and exclude the two where they don't.

blocks|key|312165|text|我也遇到过类似的问题。我的问题是在一系列被封锁的日期之间停止预订。例如，5月2日到5月7日之间的某个酒店的预订被阻止。我需要找到任何类型的重叠日期来检测和停止预订。我的解决方案类似于LordJavac。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|312166|SELECT+*+FROM+ib_master_blocked_dates+WHERE+venue_id=$venue_id+AND+
(
++++(mbd_from_date+BETWEEN+'$from_date'+AND+'$to_date')+
++++OR
++++(mbd_to_date+BETWEEN++'$from_date'+AND+'$to_date')
++++OR
++++('$from_date'+BETWEEN+mbd_from_date+AND+mbd_to_date)
++++OR++++++
++++('$to_date'+BETWEEN+mbd_from_date+AND+mbd_to_date)++++++
)
*mbd=master_blocked_dates|code-block|syntax|javascript|312167|如果它不起作用，请告诉我。|312168|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

I had faced the similar problem. My problem was to stop booking between a range of blocked dates. For example booking is blocked for a property between 2nd may to 7th may. I needed to find any kind of overlapping date to detect and stop the booking. My solution is similar to LordJavac. 

<pre><code>SELECT * FROM ib_master_blocked_dates WHERE venue_id=$venue_id AND 
(
 (mbd_from_date BETWEEN '$from_date' AND '$to_date') 
 OR
 (mbd_to_date BETWEEN '$from_date' AND '$to_date')
 OR
 ('$from_date' BETWEEN mbd_from_date AND mbd_to_date)
 OR 
 ('$to_date' BETWEEN mbd_from_date AND mbd_to_date) 
)
*mbd=master_blocked_dates
</code></pre>

Let me know if it doesn't work.

blocks|key|315163|text|给定两个区间，如(s1，e1)和(s2，e2)，其中s1|type|unstyled|depth|inlineStyleRanges|entityRanges|data|315164|你可以这样计算重叠：|315165|
|315166|SELECT+
+++++s1,+e1,+s2,+e2,
+++++ABS(e1-s1)+as+len1,
+++++ABS(e2-s2)+as+len2,
+++++GREATEST(LEAST(e1,+e2)+-+GREATEST(s1,+s2),+0)>0+as+overlaps,
+++++GREATEST(LEAST(e1,+e2)+-+GREATEST(s1,+s2),+0)+as+overlap_length
FROM+test_intervals+|code-block|syntax|javascript|315167|315168|如果一个间隔在另一个间隔内，也会起作用。|315169|315170|315171|entityMap^0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|R|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|S|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|T|8|@]|9|@]|A|$]]|$1|F|3|G|5|H|7|U|8|@]|9|@]|A|$I|J]]|$1|K|3|E|5|6|7|V|8|@]|9|@]|A|$]]|$1|L|3|M|5|6|7|W|8|@]|9|@]|A|$]]|$1|N|3|E|5|6|7|X|8|@]|9|@]|A|$]]|$1|O|3|-4|5|6|7|Y|8|@]|9|@]|A|$]]|$1|P|3|-4|5|6|7|Z|8|@]|9|@]|A|$]]]|Q|$]]

Given two intervals like (s1, e1) and (s2, e2) with s1&lt;e1 and s2&lt;e2 
You can calculate overlapping like this:

<pre><code>SELECT 
 s1, e1, s2, e2,
 ABS(e1-s1) as len1,
 ABS(e2-s2) as len2,
 GREATEST(LEAST(e1, e2) - GREATEST(s1, s2), 0)&gt;0 as overlaps,
 GREATEST(LEAST(e1, e2) - GREATEST(s1, s2), 0) as overlap_length
FROM test_intervals 
</code></pre>

Will also work if one interval is within the other one.

blocks|key|312170|text|最近我正在为同样的问题而苦苦挣扎，并以这一简单的步骤结束(这可能不是一个好的方法或内存消耗)-|type|unstyled|depth|inlineStyleRanges|entityRanges|data|312171|SELECT+*+FROM+duty_register+WHERE+employee+=+'2'+AND+(
(
duty_start_date+BETWEEN+{$start_date}+AND+{$end_date}
OR
duty_end_date+BETWEEN+{$start_date}+AND+{$end_date}
)
OR
(
{$start_date}+BETWEEN+duty_start_date+AND+duty_end_date
OR
{$end_date}+BETWEEN+duty_start_date+AND+duty_end_date)
);|code-block|syntax|javascript|312172|这帮助我找到了日期范围重叠的条目。|312173|希望这对某些人有帮助。|312174|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|P|8|@]|9|@]|A|$]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

Recently I was struggling with the same issue and came to end with this one easy step (This may not be a good approach or memory consuming)-

<pre><code>SELECT * FROM duty_register WHERE employee = '2' AND (
(
duty_start_date BETWEEN {$start_date} AND {$end_date}
OR
duty_end_date BETWEEN {$start_date} AND {$end_date}
)
OR
(
{$start_date} BETWEEN duty_start_date AND duty_end_date
OR
{$end_date} BETWEEN duty_start_date AND duty_end_date)
);
</code></pre>

This helped me find the entries with overlapping date ranges.

Hope this helps someone.

blocks|key|315178|text|您可以涵盖所有日期重叠的情况，即使数据库中的最新数据可能为空，如下所示：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|315179|SELECT+*+FROM+`tableName`+t
WHERE+t.`startDate`+<=+$toDate
AND+(t.`endDate`+IS+NULL+OR+t.`endDate`+>=+$startDate);|code-block|syntax|javascript|315180|这将返回与新开始/结束日期重叠的所有记录。|315181|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

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

<pre><code>SELECT * FROM `tableName` t
WHERE t.`startDate` &lt;= $toDate
AND (t.`endDate` IS NULL OR t.`endDate` &gt;= $startDate);
</code></pre>

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

blocks|key|3266990|text|从性能的角度来看，Mackraken上面的答案更好，因为它不需要几个OR来评估两个日期是否重叠。很好的解决方案！|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3266991|但是，我发现在MySQL中，您需要使用DATEDIFF而不是减号运算符-|3266992|SELECT+o.orderStart,+o.orderEnd,+s.startDate,+s.endDate
,+GREATEST(LEAST(orderEnd,+endDate)+-+GREATEST(orderStart,+startDate),+0)>0+as+overlaps
,+DATEDIFF(LEAST(orderEnd,+endDate),+GREATEST(orderStart,+startDate))+as+overlap_length
FROM+orders+o
JOIN+dates+s+USING+(customerId)
WHERE+1
AND+DATEDIFF(LEAST(orderEnd,+endDate),GREATEST(orderStart,+startDate))+>+0;|code-block|syntax|javascript|3266993|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|L|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|M|8|@]|9|@]|A|$G|H]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

Mackraken's answer above is better from a performance perspective as it doesn't require several OR's in order to evaluate if two dates overlap. Nice solution!
However I found that in MySQL you need to use DATEDIFF instead of the minus operator -
<pre><code>SELECT o.orderStart, o.orderEnd, s.startDate, s.endDate
, GREATEST(LEAST(orderEnd, endDate) - GREATEST(orderStart, startDate), 0)&gt;0 as overlaps
, DATEDIFF(LEAST(orderEnd, endDate), GREATEST(orderStart, startDate)) as overlap_length
FROM orders o
JOIN dates s USING (customerId)
WHERE 1
AND DATEDIFF(LEAST(orderEnd, endDate),GREATEST(orderStart, startDate)) &gt; 0;
</code></pre>

This table is used to store sessions (events):

<pre><code>CREATE TABLE session (
 id int(11) NOT NULL AUTO_INCREMENT
, start_date date
, end_date date
);

INSERT INTO session
 (start_date, end_date)
VALUES
 ("2010-01-01", "2010-01-10")
, ("2010-01-20", "2010-01-30")
, ("2010-02-01", "2010-02-15")
;
</code></pre>

We don't want to have conflict between ranges. 
Let's say we need to insert a new session from 2010-01-05 to 2010-01-25. 
We would like to know the conflicting session(s).

Here is my query:

<pre><code>SELECT *
FROM session
WHERE "2010-01-05" BETWEEN start_date AND end_date
 OR "2010-01-25" BETWEEN start_date AND end_date
 OR "2010-01-05" &gt;= start_date AND "2010-01-25" &lt;= end_date
;
</code></pre>

Here is the result:

<pre><code>+----+------------+------------+
| id | start_date | end_date |
+----+------------+------------+
| 1 | 2010-01-01 | 2010-01-10 |
| 2 | 2010-01-20 | 2010-01-30 |
+----+------------+------------+
</code></pre>

Is there a better way to get that?

<hr>

<a href="http://sqlfiddle.com/#!9/f21cc/2" rel="noreferrer">fiddle</a>

Check overlap of date ranges in MySQL

数据库

MySQL

此表用于存储会话(事件)：CREATE TABLE session (  id int(11) NOT NULL AUTO_INCREMENT, start_date date, end_date date);INSERT INTO session  (start_date, end_date)VALUES  ("20...

问检查MySQL中日期范围的重叠
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问检查MySQL中日期范围的重叠EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问检查MySQL中日期范围的重叠
EN