7

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

Fuxi
  • 329
  • 2
  • 6
  • 15

3 Answers3

16

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.

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

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

Pacerier
  • 86,231
  • 106
  • 366
  • 634
  • 1
    I want to link this other question, which contains a simplier (answer)[http://stackoverflow.com/questions/2545947/mysql-range-date-overlap-check]. It's about foins something like: WHERE new_start < existing_end AND new_end > existing_start; which is more efficient. Thanks to @Gibbok, Xavi Lopez and Steve Lillis for the advices. – Iván Rodríguez Torres Aug 04 '15 at 11:38
  • @IvanRodriguezTorres, Odd, I thought I had already submitted an update using a 2-comparison solution after having been inspired by the ["rectangle overlaps"](http://stackoverflow.com/questions/306316/determine-if-two-rectangles-overlap-each-other#comment150804_306332) solution. Anyway thanks for notifying, I've resubmitted an update. – Pacerier Aug 05 '15 at 07:59
  • 1
    answer is perfect now. Thanks! – Iván Rodríguez Torres Aug 05 '15 at 08:12
  • @Pacerier what about SELECT * FROM table WHERE AND ( ( start_date BETWEEN start_search AND end_search ) OR ( end_date BETWEEN start_search AND end_search ) OR ( start_search BETWEEN start_date AND end_date ) OR ( start_search BETWEEN start_date AND end_date ) ) – CodeCrack Feb 20 '18 at 19:09
8

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')
Juan M
  • 137
  • 3
  • Thanks. This is exactly what I was looking for on two separate fields to find a date range. – Louie Miranda Feb 18 '14 at 13:52
  • @LouieMiranda, This solution is flawed because it only covers the 3 scenarios out of the 4 possible scenarios. In other words, if you use this code you **will** get false negatives. – Pacerier Aug 05 '15 at 08:24
2

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)

Andrew Johnson
  • 446
  • 3
  • 11