MySQL query to select events between start/end date

Question

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!

Met

Do you want a single query? – Simard

I search a more elegant query. I'm pretty sure this one is not very good. I have tried with 'BETWEEN start AND end' without success. The problem is when I have date withe start == end. – Met

@Met added this condition of start and end in my answer – Zecchino

Your answers have helped me, thank you. By creating this topic, I thought my answer (my queries) were bad in some cases, but infact it was not! This is the problem when we are stuck on a problem for long hours: answer can jumped out at you, without seeing it. – Met

In your first example, #2 is an ongoing event during your scope. Wouldn't an ongoing event be the same thing as an active event? And if for another example: All events [active] between 2013-06-22 and 2013-06-23 wouldn't #2 and #3 both be active events? – Gesundheit

Answer 1

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'

Simard

I have had ready from a while, waiting for op answer at my comment – Simard

You have both answered at the same time :) Your answers have helped me, thank you. By creating this topic, I thought my answer (my queries) were bad in some cases, but infact it was not! This is the problem when we are stuck on a problem for long hours: answer can jumped out at you, without seeing it. – Met

@Met I'm glad i helped you out, please consider accepting one answer, your best one, to close your issue – Simard

@Met in fact he posted after me and i told him that he copy paste my answer – Zecchino

@echo_Samir I give you a '+1' and I select Fabio answer. I would have validated your two answers, but Stackoverflow does not allow it. – Met

@echo_Samir I think OP is free to decide which answer is better for him, he's not supposed to accept first answer but what he thinks is the best – Simard

this answer does not include ongoing events. IMO Ongoing events that may either start before or end after the date range should be part of 'all the active events in a time period' – Gesundheit

That is not correct. Use: SELECT id FROM events WHERE ( ( start >= '2013-07-22' OR end >= '2013-07-22') AND ( end <= '2013-06-13' OR start <= '2013-06-13') ); – Desolate

That is incorrect e.g. does not work when the start-end date contains the event. Technically, such events should be considered active in that time period. – Waitress

Answer 2

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

Wordbook

Even if this is a Mysql query i ported to postgres and works very well with a few tweaks I owe you a beer ! – Trilbee

this is close but can be simplified with Olivier's answer. At least this answer includes events that span the scope. – Gesundheit

This is the only TRUE answer no? – Lupe

That is incorrect, there are four cases: (3) scope fully contains event (4) event fully contains scope. – Waitress

Answer 3

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

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

Nodab

helps, but not an answer to the question. this would be better though than some of the answers that do not include ongoing events. – Gesundheit

Perfect solution! – Frontispiece

That is incorrect. Now is not a time period as stated in question. Now is one point in time. – Waitress

Answer 4

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.

Colorimeter

Thank for you answer! It works, but I think that you have reversed the start / end date. The correct query sould be: SELECT id FROM events WHERE start <= '2013-06-13' AND end >= '2013-07-22' – Met

@Guicara, this is actually one of the ONLY CORRECT answers listed here. Let me explain. Let's say an event started on 2013-01-01, and ended on 2013-12-31. Should this event be included in the query? I believe yes it should because it is an ongoing active event during the range you have selected. Unless you mean 'all the active events in a time period' as ONLY events that start AND end WITHIN the date range. I recently ran into this 'bug' in my script because I was using one of the other answers. I believe that you want events that start before the end date and end after the start date. – Gesundheit

@Gesundheit exactly... you are correct. Thanks for sharing very thoughtful insight about this query. I really appreciate your effort in providing useful comment – Pogue

Answer 5

SELECT *
FROM events 
WHERE endDate >= @startDate AND startDate <= @endDate

For explanation refer to this diagram:

• Suppose sample data is [startDate: 2020-10-01, endDate: 2020-20-01]
• The user provides @startDate and @endDate to search
• For overlap there are 4 scenarios and 1 variation (red/maroon lines)
• For no overlap there are just 2 scenarios (green lines)

So, in order to get overlapping dates by providing start and end date, endDate must be greater than @startDate and startDate must be less than @endDate.

Grenville

An explanation would be nice. Also why did you use nolock, how it is related to the question? – Unbridled

@Unbridled explanation added. – Grenville

Answer 6

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

Woden

I believe there is a typo here or this is a wrong answer. it should be WHERE start <= ToDate OR end >= FromDate – Gesundheit

[at signs removed from my comment due to restraints of StackOverflow tagging multiple users] – Gesundheit

Thanks. I have restored my original SQL. Someone had edited it, for no particular reason, and ruined the simplicity of it. I believe that mine is the simplest solution. – Woden

Answer 7

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.

Truce

Answer 8

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

Zecchino

Thank, but "AND start != end" is unnecessary / wrong (because I can have a event that start and end the same day). – Met

ok its just because you mentioned in your question that you have problem with start and end have same day . anyway i edited my answer – Zecchino

This will not retrieve events that start before and end after; start before and end during; or start during and end after. An ongoing event IMO is an 'active events in a time period' – Gesundheit

Answer 9

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

Goya

What about events that start before the scope and end after the scope? Wouldn't they be considered active events? Ongoing=Active? Your answer only retrieves events that start AND end within the scope. – Gesundheit

how do I select all records that have an event datetime within 1 hour before, for example mysql select all that time_diff less than 1 hour from datetime? its for a reminder email i want to make on a cronjob can you help with this sql query? – Susceptive

Answer 10

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."'";

Aliment

Answer 11

If anyone is searching for a situation when the current date is residing between two periods (start/end date) in Microsoft SQL, please find below

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

Prohibitive

what about 1 hour before an event start time, for a reminder how can I do this?, (I cant start a question it wont let me)? – Susceptive