MySQL查询某个日期范围 [英] MySQL Query for certain date range
问题描述
我有以下表格数据：
Table: Seasons
id from to
---------------------------
1 2013-08-30 2013-09-04
2 2013-09-05 2013-09-08
3 2013-09-09 2013-09-20
我需要运行一个查询，返回在特定日期范围内的所有记录，例如：将所有受影响的记录从 2013-09-04到2013-09-05
i need to run a query which returns all records which are within a certain date range, for example: return all records which are affected from 2013-09-04 to 2013-09-05
它会像
date range: | 09-04 - 09-05|
seasons: 08-30 - 09-04 | 09-05 - 09-08 | 09-09 - 09-20
所以应该返回前2条记录。
我已经尝试使用BETWEEN查询，但它的接缝我需要建立几个案例 - 还是有一个更简单的方法？
thankx
so it should return the first 2 records. i've tried the query with BETWEEN but it seams i need to build up several cases - or is there a simpler way? thankx
推荐答案
这是惊人的，没有人注意到这将近两年，但其他答案是所有错误，因为它们没有考虑到开始日期和结束日期都超出搜索范围的范围的情况。考虑这是日期的范围：
It's amazing no one has noticed this for almost two years, but the other answers are all wrong because they didn't take into account the case when both the start date and the end date fall beyond the scope of the search range. Consider this is the range of the date:
start_date <<---------------------------- date range --------------------------->> end_date
这是我们搜索的范围：
start_date <<---------------------------- date range --------------------------->> end_date
start_search <<-------- search range -------->> end_search
搜索应该给我们一个积极的结果，因为它们相交。但是如果您使用其他答案，则会得到否定的结果，因为 start_date 或 end_date 之间 start_search 和 end_search 。
The search should give us a positive result because they intersect. But if you use the other answers, you would get a negative result because neither start_date nor end_date is between start_search and end_search.
要获得解决方案， 4可能的模式：
To get the solution, let's draw all 4 possible modes of intersection:
start_date <<---------- date range --------------------------->> end_date
start_search <<------------------------- search range -------->> end_search
start_date <<---------------------------- date range ---------->> end_date
start_search <<---------- search range ------------------------>> end_search
start_date <<---------------------------- date range --------------------------->> end_date
start_search <<-------- search range -------->> end_search
start_date <<----------- date range -------->> end_date
start_search <<------------------------- search range ------------------------>> end_search您可以 OR 所有4种可能的情况，以获得简单的解决方案：
You can OR all 4 possible cases to obtain the straightforward solution:
select*from table where
/* 1st case */ start_date between start_search and end_search
or /* 2nd case */ end_date between start_search and end_search
or /* 3rd case */ (start_date <= start_search and end_date >= end_search)
or /* 4th case */ (start_date >= start_search and end_date <= end_search)
/* the 4th case here is actually redundant since it is being covered by the 1st and 2nd cases */
一个不太简单的解决方案是：
A less straightforward solution is:
select*from table where
start_date between start_search and end_search /* covers 1st and 4th cases */
or start_search between start_date and end_date /* covers 2nd and 3rd cases */尝试使用上面的图形可视化它。
如果我们尝试从上面的4个图中推断出一个模式，我们可以看到，在一个交集中，
end_date 总是> = start_search ，另一方面， start_date 总是< = end_search 。实际上，进一步可视化，我们可以看到，当这两个条件成立时，我们不能有交集。
Try to visualize it using the diagrams above.
If we attempt to extrapolate a pattern out of the 4 diagrams above, we can see that during an intersection,
end_date is always >= start_search, and on the flip side, start_date is always <= end_search. Indeed, visualizing further, we can see that when those two conditions hold, we cannot not have an intersection.
因此，另一种解决方案是简单如下：
As such, another solution is as simple as:
select*from table where
end_date >= start_search && start_date <= end_search
此解决方案的优点是我们只需要2个比较。相比之下，与（$&code> OR 所有）方法相比，需要从2到多达8（3+ 3+ 2）比较。 （之间的每个由 3个比较组成。）
And the advantage of this solution is that we only need 2 comparisons. Contrast that with the "OR everything" approach which requires from 2 up to as much as 8 (3 + 3 + 2) comparisons. (Each between call consists of 3 comparisons.)
这篇关于MySQL查询某个日期范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
