MySQL查询某些日期范围

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到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的查询,但它接缝我需要建立几个案例 - 还是有更简单的方法? thankx

3 个解决方案

#1

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

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_date nor end_date is between start_search and end_search.

搜索应该给我们一个积极的结果,因为它们相交。但是如果您使用其他答案,则会得到否定结果,因为start_date和end_date都不在start_search和end_search之间。

To get the solution, let's draw all 4 possible modes of 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 OR all 4 possible cases to obtain the straightforward solution:

您可以或所有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.

尝试使用上面的图表将其可视化。

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

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.)

此解决方案的优点是我们只需要进行2次比较。与“OR OR”方法形成对比,这种方法需要2到8(3 + 3 + 2)个比较。 (每次通话都包含3次比较。)

#2

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')

#3

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)

#1

start_date <<---------------------------- date range --------------------------->> end_date