Search code examples
mysqlsqldatedate-range

MySQL query to select events between start/end date

I have a MySQL table named 'events' that contains event data. The important columns are 'start' and 'end' which contain string (YYYY-MM-DD) to represent when the events starts and ends.

I want to get the records for all the active events in a time period.

Events:

------------------------------
ID | START      | END        |
------------------------------
1  | 2013-06-14 | 2013-06-14 |
2  | 2013-06-15 | 2013-08-21 |
3  | 2013-06-22 | 2013-06-25 |
4  | 2013-07-01 | 2013-07-10 |
5  | 2013-07-30 | 2013-07-31 |
------------------------------

Request/search:

Example: All events between 2013-06-13 and 2013-07-22 : #1, #3, #4

SELECT id FROM events WHERE start BETWEEN '2013-06-13' AND '2013-07-22' : #1, #2, #3, #4
SELECT id FROM events WHERE end   BETWEEN '2013-06-13' AND '2013-07-22' : #1, #3, #4
====> intersect : #1, #3, #4
Example: All events between 2013-06-14 and 2013-06-14 : 

SELECT id FROM events WHERE start BETWEEN '2013-06-14' AND '2013-06-14' : #1
SELECT id FROM events WHERE end   BETWEEN '2013-06-14' AND '2013-06-14' : #1
====> intersect : #1

I tried many queries still I fail to get the exact SQL query.

Don't you know how to do that? Any suggestions?

Thanks!

Solution

  • If I understood correctly you are trying to use a single query, i think you can just merge your date search toghter in WHERE clauses

    SELECT id 
FROM events 
WHERE start BETWEEN '2013-06-13' AND '2013-07-22' 
AND end BETWEEN '2013-06-13' AND '2013-07-22'

    or even more simply you can just use both column to set search time filter

    SELECT id 
FROM events 
WHERE start >= '2013-07-22' AND end <= '2013-06-13'