[mysql] 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!

If you would like to use INTERSECT option, the SQL is as follows

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

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

I am assuming that active events in a time period means at least one day of the event falls inside the time period. This is a simple "find overlapping dates" problem and there is a generic solution:

-- [@d1, @d2] is the date range to check against
SELECT * FROM events WHERE @d2 >= start AND end >= @d1

Some tests:

-- list of events
SELECT * FROM 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 |
+------+------------+------------+

-- events between [2013-06-01, 2013-06-15]
SELECT * FROM events WHERE '2013-06-15' >= start AND end >= '2013-06-01';
+------+------------+------------+
| id   | start      | end        |
+------+------------+------------+
|    1 | 2013-06-14 | 2013-06-14 |
|    2 | 2013-06-15 | 2013-08-21 |
+------+------------+------------+

-- events between [2013-06-16, 2013-06-30]
SELECT * FROM events WHERE '2013-06-30' >= start AND end >= '2013-06-16';
+------+------------+------------+
| id   | start      | end        |
+------+------------+------------+
|    2 | 2013-06-15 | 2013-08-21 |
|    3 | 2013-06-22 | 2013-06-25 |
+------+------------+------------+

-- events between [2013-07-01, 2013-07-01]
SELECT * FROM events WHERE '2013-07-01' >= start AND end >= '2013-07-01';
+------+------------+------------+
| id   | start      | end        |
+------+------------+------------+
|    2 | 2013-06-15 | 2013-08-21 |
|    4 | 2013-07-01 | 2013-07-10 |
+------+------------+------------+

-- events between [2013-07-11, 2013-07-29]
SELECT * FROM events WHERE '2013-07-29' >= start AND end >= '2013-07-11';
+------+------------+------------+
| id   | start      | end        |
+------+------------+------------+
|    2 | 2013-06-15 | 2013-08-21 |
+------+------------+------------+

Here lot of good answer but i think this will help someone

select id  from campaign where ( NOW() BETWEEN start_date AND end_date) 

EDIT: I've squeezed the filter a lot. I couldn't wrap my head around it before how to make sure something really fit within the time period. It's this: Start date BEFORE the END of the time period, and End date AFTER the BEGINNING of the time period

With the help of someone in my office I think we've figured out how to include everyone in the filter. There are 5 scenarios where a student would be deemed active during the time period in question:

1) Student started and ended during the time period.

2) Student started before and ended during the time period.

3) Student started before and ended after the time period.

4) Student started during the time period and ended after the time period.

5) Student started during the time period and is still active (Doesn't have an end date yet)

Given these criteria, we can actually condense the statements into a few groups because a student can only end between the period dates, after the period date, or they don't have an end date:

1) Student ends during the time period AND [Student starts before OR during]

2) Student ends after the time period AND [Student starts before OR during]

3) Student hasn't finished yet AND [Student starts before OR during]

   (
        (
         student_programs.END_DATE  >=  '07/01/2017 00:0:0'
         OR
         student_programs.END_DATE  Is Null  
        )
        AND
        student_programs.START_DATE  <=  '06/30/2018 23:59:59'
   )

I think this finally covers all the bases and includes all scenarios where a student, or event, or anything is active during a time period when you only have start date and end date. Please, do not hesitate to tell me that I am missing something. I want this to be perfect so others can use this, as I don't believe the other answers have gotten everything right yet.

try this

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

DEMO HERE

output :

 ID
 1
 3
 4

select * from tbl where (endDate>=@starDate and startDate<=@endDate)

attached the diagram for explanation

storing simple data in DB StartDate =10/01/2020 and endDate=20/01/2020. user can provide @startDate(d1) and @endDate(d2) to search.

here 6 scenarios can happen depicted by the 4 red lines (match data) 2 green lines (no match data).

so by conclusion from the image, to get data from DB by providing d1,d2. ED must be greater than d1(@startDate) and SD must be less than d2(@endDate).

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

Or use MIN() and MAX() if you don't know the precedence.

In PHP and phpMyAdmin

$tb = tableDataName; //Table name
$now = date('Y-m-d'); //Current date

//start and end is the fields of tabla with date format value (yyyy-m-d)

$query = "SELECT * FROM $tb WHERE start <= '".$now."' AND end >= '".$now."'";

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