December 11th, 2012, 02:58 PM
Select events occurring during set period
Hi, I have a table of events that I want to select from for a set period.
This is via a start date and an end date which seems ok with the following query:
However the problem I have is what about an event that starts before my start date and ends after my end date?
WHERE (Date_Start BETWEEN '$month_start' AND '$month_end')
OR (Date_End BETWEEN '$month_start' AND '$month_end')
The workaround that I have come up with is
I'm concerned that this may not be the most efficient way of doing this so would appreciate some help and advice to get me going down the right path...
OR (Date_Start < '$month_start' AND Date_End > '$month_end')
Last edited by gizz; December 12th, 2012 at 02:44 AM.
December 11th, 2012, 03:49 PM
How about events starting before the period but ending during it? And starting during but ending after? I'm guessing what you want is all the events that are active during that time period?
Or in other words, the event started before the period ended and the event ends after the period started.
December 11th, 2012, 03:52 PM
here are all the ways the date ranges can overlap
with me so far?
1 date_start---date_end | |
2 date_start--|--date_end |
3 | date_start---date_end |
5 | date_start--|--date_end
6 | | date_start---date_end
okay, so you want to find all events for case 2 through case 5
the simplest sql for this is --
WHERE date_end >= $month_start /* eliminates case 1 */
AND date_start <= $month_end /* eliminates case 6 */
Comments on this post
December 12th, 2012, 04:02 AM
Sweet - Yes, that's a much shorter version.
The former worked but it just looked bloated!
January 15th, 2013, 03:40 PM
God knows I'm not one to revive threads, but over here we discovered that <= and >= aren't quite right.
Start reading around this post.
Originally Posted by Jacques1
Originally Posted by requinix
January 15th, 2013, 03:51 PM
especially if you need some time -- like, around a half a day or a day -- to clean the room before the next guest arrives
it's a business decision whether >= and <= versus > and < are called for
January 15th, 2013, 04:11 PM
Well, if you do need some time between the events, you'll want a specific amount of time rather than an "infinitesimal pause".
On the other hand, an event directly following another one may be perfectly valid in some cases -- like phone calls or whatever.
So I think ">" and "<" really are the only sensible options in this case -- either that or a much more sophisticated approach, which accounts for travel times, pauses and whatnot.
But let's not argue about details. I just brought it up because I stumbled upon it while going though some examples.
Last edited by Jacques1; January 15th, 2013 at 04:14 PM.