mysql怎么查找日期范围_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

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

解决方案

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 <> end_date

And this is the range of our 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.

To get the solution, let's draw all 4 possible modes of intersection:

start_date <> end_date

start_search <> end_search

start_date <> end_date

start_search <> end_search

start_date <> end_date

start_search <> end_search

start_date <> end_date

start_search <> end_search

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 */

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 &plus; 3 &plus; 2) comparisons. (Each between call consists of 3 comparisons.)