I have events table. I want to get all event records which are running between searched dates.
Event Table
id title start_date_time end_date_time
-----------------------------------------------------------------
1 xyz 2014-08-12 11:40:00 2014-08-23 10:30:00
2 pqr 2014-09-05 11:40:00 2014-09-25 09:20:00
3 abc 2014-10-10 11:40:00 2014-10-25 10:00:00
For example, I searched for events which are running between 2014-08-15 and 2014-09-10.
Expected result would be :
id title start_date_time end_date_time
-----------------------------------------------------------------
1 xyz 2014-08-12 11:40:00 2014-08-23 10:30:00
2 pqr 2014-09-05 11:40:00 2014-09-25 09:20:00
So what will be the query to get expected result?
Assuming you have $startdate and $enddate, then you want this where clause for overlaps:
select e.*
from events e
where $startdate <= e.end_date_time and
$enddate >= e.start_date_time;
The choice of <= or < depends on whether the end dates are included or not in the event times.
The logic is simple: there is an overlaps if the event starts before the period ends and ends after the period starts.