descript | start | end
test 1 | 2011-07-18 14:30:00 | 2011-07-18 17:00:00
test 2 | 2011-07-18 00:00:00 | 2011-07-19 00:00:00
SELECT * FROM event WHERE start >= "2011-07-18 00:00:00" AND end <= "2011-07-18 23:59:59";
The above query is dynamically selected by user. Given this sql query I’d like to also retrieve the 2nd record as the start date falls into 18th July category. How do I do that?
The above select statement obviously can’t retrieve the 2nd result as the end date is more than “2011-07-18 23:59:59” but the start date is still in the 18july range. If i use an OR in the statement I get garbage result
The start date has to be before the ending of the given period, and the end date after the start of the given period. That way you get all rows who are active in at least a part of the given period.
WHERE start <= "2011-07-18 23:59:59"
AND end >= "2011-07-18 00:00:00"
Thanks for the reply guido. Given this start <= “2011-07-18 23:59:59” , wouldn’t it retrieve anything less than that? Say for example if I have a 2010 record?
I just think it through the logic and it seems that when user select the date range in the calendar, it will always have a start date. Instead of comparing “in-between” I use this start date like this:
start BETWEEN “2011-07-18 00:00:00” AND “2011-07-18 23:59:59”
Do you think this is the right way? SO I dont care about the end date any more since the start is the one that really triggers the event range.
