重叠日期范围MySQL [英] Overlapping date range MySQL

问题描述

我有以下数据：

ID  startDate               endDate
-----------------------------------------------
1   2010-03-01 10:00:00     2010-03-01 12:00:00
2   2010-03-01 12:30:00     2010-03-01 15:30:00
3   2010-03-01 15:30:00     2010-03-01 18:30:00

我想做的是检查一下开始和结束日期不会落在我的数据的startDate和endDate范围内。

What I want to do is check that a start and end date don't fall inside the startDate and endDate ranges in my data.

所以例如下面的代码可以;

So for example, the following would be OK;

startDate               endDate
-----------------------------------------------
2010-03-01 12:00:00     2010-03-01 12:30:00
2010-03-01 18:30:00     2010-03-01 21:00:00

但以下日期会失败，因为它们会重叠;

but the following dates would fail, as they would overlap;

startDate               endDate
-----------------------------------------------
2010-03-01 09:00:00     2010-03-01 13:00:00 (overlaps ID 1)
2010-03-01 10:30:00     2010-03-01 11:00:00 (overlaps ID 1)
2010-03-01 18:00:00     2010-03-01 19:00:00 (overlaps ID 3)

我拉我的头发，因为我可以得到一两个上面的3个测试日期范围是失败但不是全部。

I'm pulling my hair out because I can get one or two of the above 3 test date ranges to fail but not all of them.

我正在使用MySQL。

I am using MySQL.

推荐答案

一个查询来选择重叠（我将列名为startTime& endTime虽然，因为时间似乎很重要...）：

A query to select overlaps (I'd name the columns startTime & endTime though, as Time seems important...):

WHERE 
<start> < endDate
AND
<end> > startDate

这篇关于重叠日期范围MySQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！