MySQL query to select events between start/end date

15

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!

This question is tagged with mysql sql date date-range

~ Asked on 2013-06-09 20:53:17

The Best Answer is


22

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'

~ Answered on 2013-06-09 21:01:20


16

You need the events that start and end within the scope. But that's not all: you also want the events that start within the scope and the events that end within the scope. But then you're still not there because you also want the events that start before the scope and end after the scope.

Simplified:

  1. events with a start date in the scope
  2. events with an end date in the scope
  3. events with the scope startdate between the startdate and enddate

Because point 2 results in records that also meet the query in point 3 we will only need points 1 and 3

So the SQL becomes:

SELECT * FROM events 
WHERE start BETWEEN '2014-09-01' AND '2014-10-13' 
OR '2014-09-01' BETWEEN start AND end

~ Answered on 2014-09-25 09:35:39


Most Viewed Questions: