特定日期范围的 MySQL 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18603318/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL Query for certain date range
提问by Fuxi
i'm having the following table data:
我有以下表格数据：
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
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
我需要运行一个查询，该查询返回某个日期范围内的所有记录，例如：返回所有受影响的记录 2013-09-04 to 2013-09-05
it would be like
它会像
date range: | 09-04 - 09-05|
seasons: 08-30 - 09-04 | 09-05 - 09-08 | 09-09 - 09-20
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
所以它应该返回前 2 条记录。我已经尝试使用 BETWEEN 进行查询，但是我需要建立几个案例 - 或者有更简单的方法吗？谢谢
回答by Pacerier
It's amazing no one has noticed this for almost two years, but the other answers are all wrongbecause 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
And this is the range of our search:
这是我们搜索的范围：
start_date <<---------------------------- date range --------------------------->> end_date
start_search <<-------- search range -------->> 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_datenor end_dateis between start_searchand end_search.
搜索应该给我们一个积极的结果，因为它们相交。但是，如果你使用其他的答案，你会得到一个否定的结果，因为既没有start_date，也不end_date是间start_search和end_search。
To get the solution, let's draw all 4 possible modesof intersection:
为了得到解决方案，让我们画出所有 4 种可能的交叉模式：
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
You can ORall 4 possible cases to obtain the straightforward solution:
您可以通过OR所有 4 种可能的情况来获得直接的解决方案：
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 */
Try to visualize it using the diagrams above.
尝试使用上面的图表将其可视化。
如果我们试图从上面的 4 个图表中推断出一个模式，我们可以看到，在相交期间，
end_dateend_date总是>= start_search>= start_search，而在另一侧，start_datestart_date总是<= end_search<= end_search。事实上，进一步可视化，我们可以看到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
And the advantage of this solution is that we only need 2 comparisons. Contrast that with the "OReverything" approach which requires from 2 up to as much as 8 (3 + 3 + 2) comparisons. (Each betweencall consists of 3 comparisons.)
而这个解决方案的优点是我们只需要进行 2 次比较。与OR需要从 2 到多达 8 (3 + 3 + 2) 次比较的“一切”方法形成对比。（每个between调用包含3 个比较。）
回答by Juan M
Try with:
尝试：
SELECT *
FROM `Seasons`
WHERE (`from` BETWEEN '2013-09-04' AND '2013-09-05' OR `to` BETWEEN '2013-09-04' AND '2013-09-05')
回答by Andrew Johnson
This should work
这应该工作
SELECT *
FROM `Seasons`
WHERE (date_field BETWEEN '2010-01-30 14:15:55' AND '2010-09-29 10:15:55')
**Make sure that the date is in mysql default formate (yyyy-mm-ff hh:mm:ss)
**确保日期在mysql默认格式中（yyyy-mm-ff hh:mm:ss）
