Best SQL query to select events between two dates

Question

After read a lot of posts about this. I need a good way to select events between two dates. The possible conditions are shown in the following figure:

My first attempt is the following:

SELECT *
FROM events
WHERE 
(
 start_date BETWEEN $start_select AND  $end_select
 OR
 end_date BETWEEN $start_select AND $end_select
)
OR
(
 start_date <=  $start_select
 AND
 end_date >= $end_select
)

The problem is that it takes a long time to make the query.

Then I saw this post: Select data from date range between two dates where @dmlukichev talks about exclude all wrong options:

Something like this:

SELECT *
FROM events
WHERE NOT 
(
 start_date <  $start_select
 OR
 end_date > $end_select
)

But it does not work me.

Any ideas?

The last one should work properly if `start_date > end_date` and `$start_select > $end_select`. Are you sure both conditions are true? — Al.G., Oct 19 '15 at 08:44
Why does the query takes long? Is is not a "heavy" query. How big is the table? Do you have indexes on the `start_date` and `end_date` columns? — Yasen Zhelev, Oct 19 '15 at 08:50

Yasen Zhelev · Accepted Answer · 2015-10-19T09:20:13.767

11

If I am getting the condition right, this is what you need.

SELECT * FROM events WHERE (end_date >=  $start_select AND start_date <= $end_select);

You will get all events where the start date is before the $end_select and the end date is after $start_select.

edited Oct 19 '15 at 09:20

answered Oct 19 '15 at 08:47

Yasen Zhelev

4,045
3
31
56

The problem is the next: if you select the last day (or first) it does not work. For example I have an event from '25-10-2015' to '29-10-2015' then my select range is '29-10-2015' to '31-10-2015', your query doesn't work. – kurtko Oct 19 '15 at 09:15
1

I think: SELECT * FROM events WHERE (end_date >= $start_select AND start_date <= $end_select); – kurtko Oct 19 '15 at 09:19
Yes, you are right. I was not sure should I include the actual dates in the check. I updated my answer. – Yasen Zhelev Oct 19 '15 at 09:20

score 0 · Answer 2 · answered Oct 25 '19 at 09:16

0

Might be a bit late (by 4 years) but I use:

SELECT * FROM events WHERE  (end_date > $start_select AND start_date BETWEEN $start_select AND $end_select
    OR start_date < $end_select AND end_date BETWEEN $start_select AND $end_select
    OR (start_date < $start_select AND end_date > $end_select) BETWEEN $start_select AND $end_select
    )

answered Oct 25 '19 at 09:16

CharlesdeK

13
5

Logically, this is correct, your expressions cover all 3 uses cases. 1) The event's start date is within the selected range (or) 2) The event's end date is within the selected range (or) 3) The event fully encompasses the selected date range. I'm only commenting because your logic works and embraces real scenarios which is easily explainable to other developers. However, this is not the most concise solution (Answered above). – Aries Apr 08 '22 at 06:42

Best SQL query to select events between two dates

2 Answers2