SQL Brain Buster...selecting dates

[End User]

I've been trying to get a good solution to a pair of problems and am looking for any ideas that might work. I have a list of timestamps that can span any number of days or months (including crossing a year boundary). The first problem: I want to get a list of all of the rows that "touch" or include a given month, like March. Like these, for example:2010-02-05 10:15:30 to 2010-02-15 08:25:30 (feb 2nd to feb 15th, doesn't touch march)2010-02-05 10:15:30 to 2010-03-02 08:25:30 (feb 5th to march 2nd, touches march)2010-03-05 14:20:30 to 2010-03-06 16:30:10 (march 5th to march 6th, touches march)2010-03-11 12:20:30 to 2010-03-14 18:22:10 (march 11th to march 14th, touches march)2010-03-27 15:20:30 to 2010-04-10 18:22:10 (march 27th to april 10th, touches march)2010-04-16 13:20:30 to 2010-04-18 09:22:10 (april 16th to april 18th, doesn't touch march)The timestamps (in theory) won't have any overlaps at all, they *should* all be sequential. That is, no two of them can overlap and include the same exact moment. They can butt right up against each other, but can't overlap or start/stop on the exact same minute (seconds will be ignored). So a time stamp can end on March 10th at 3:15pm and the next one could not start until March 10th at 3:16pm. They also can't "contain" an existing period. That is, if there was a period from March 5th to March 10th, there couldn't be a period from March 1st to March 20th.Question is, given the example set of timestamps above, how can I get all of the rows that touch or contain March?The second problem is an extension of the first one, sort of. Given the example above, if someone were to try and enter a time period that overlapped or contained any of the already existing time periods in March, how could I detect that? This is to make sure that they do end up sequential and don't overlap or contain any of the existing periods. Is there some SQL that would return all of the periods that touch March? Or, some SQL that would exclude all of the periods that touch March? I have a couple of ideas, but they're all pretty clumsy...I'm hoping someone here might have some suggestions as to how to accomplish these two things, or hopefully solve at least part of either problem.

[Dilated]

I hope I understand your question correctly. I've had to do something similar to this before in PHP. I ended up using the mktime function:This is what it looked like:

$year = 2010;$month = 4;$db->query('SELECT * FROM tbl WHERE time >= '.mktime(0, 0, 0, $month, 1, $year).' AND time < '.mktime(0, 0, 0, $month+1, 1, $year));

That piece of code would retrieve every row with a timestamp between the first second of April 1st and the last second of April 30th, in practical terms, from April to May.edit: In your case I guess it would look something like:

$year = 2010;$month = 4;$start = mktime(0, 0, 0, $month, 1, $year);$end = mktime(0, 0, 0, $month+1, 1, $year);$db->query('SELECT * FROM tbl WHERE (time1 >= '.$start.' AND time1 < '.$end.') OR (time2 >= '.$start.' AND time2 < '.$end.')');

BTW, mktime is a very smart function. If you ask for the 13th month of the year 2005, it will assume you meant January of 2006 and return the unix timestamp.

[End User]

I think this is close to what I need...almost. In addition to the rows in April, I'm trying to also get rows where the start date/time might be before April (like March 25th) and possibly end after April (like May 15th. In other words, find every row where the timestamps might not only be in April, but also include April (i.e March 25 to May 15). So I'm not sure this will do exactly what I'm looking to accomplish. (??)

I hope I understand your question correctly. I've had to do something similar to this before in PHP. I ended up using the mktime function:This is what it looked like:

$year = 2010;$month = 4;$db->query('SELECT * FROM tbl WHERE time >= '.mktime(0, 0, 0, $month, 1, $year).' AND time < '.mktime(0, 0, 0, $month+1, 1, $year));

That piece of code would retrieve every row with a timestamp between the first second of April 1st and the last second of April 30th, in practical terms, from April to May.edit: In your case I guess it would look something like:

$year = 2010;$month = 4;$start = mktime(0, 0, 0, $month, 1, $year);$end = mktime(0, 0, 0, $month+1, 1, $year);$db->query('SELECT * FROM tbl WHERE (time1 >= '.$start.' AND time1 < '.$end.') OR (time2 >= '.$start.' AND time2 < '.$end.')');

BTW, mktime is a very smart function. If you ask for the 13th month of the year 2005, it will assume you meant January of 2006 and return the unix timestamp.

[Dilated]

I think this is close to what I need...almost. In addition to the rows in April, I'm trying to also get rows where the start date/time might be before April (like March 25th) and possibly end after April (like May 15th. In other words, find every row where the timestamps might not only be in April, but also include April (i.e March 25 to May 15). So I'm not sure this will do exactly what I'm looking to accomplish. (??)

As far as I can figure, the basic logic behind the second code example I posted should work for that. It checks both timestamps to see if either of them fall within the specified month.

[End User]

As far as I can figure, the basic logic behind the second code example I posted should work for that. It checks both timestamps to see if either of them fall within the specified month.

Maybe I'm misunderstanding...Getting rows inside April are easy enough- I can look for any matches that have April (for example) as part of the start or the stop timestamp. But an event that runs from March to May won't match either of the timestamps. At the moment what I'm trying is something like this:1) Grab all the timestamps.2) Check if the end of the event is before the start of April *OR* if the start of the event is after the end of April. If either of these are true then it's not a matching event and the event is discarded.3) Check to see if the start of the event is before the start of April *AND* the end of the event is after April. If so, the event is kept (because it includes or spans April). 4) Grab and keep all the timestamps that have a start and/or stop time in April.I think this will give me a collection of all of the events that touch April. If I've got this right, I can then loop through all the collected events and (with a little extra calculating) figure out how much total time for the events in April. Please tell me if I'm having a brain cramp here and my logic doesn't seem right!

[Dilated]

Maybe I'm misunderstanding...Getting rows inside April are easy enough- I can look for any matches that have April (for example) as part of the start or the stop timestamp. But an event that runs from March to May won't match either of the timestamps.

Ah, ok. Now I understand the problem. You're right, this is a lot more complicated than it seems at first. This requires some heavier logic.

$year = 2010;$month = 4;$start = mktime(0, 0, 0, $month, 1, $year); $end = mktime(0, 0, 0, $month+1, 1, $year); $db->query('SELECT * FROM tbl WHERE (time1 >= '.$start.' AND time1 < '.$end.') OR (time2 >= '.$start.' AND time2 < '.$end.') OR (((time1+'.($diff = $end-$start).') >= '.$start.') AND ((time1+'.$diff.') < '.$end.'))');

Let me know if that works, I'm not entirely sure it will.

[End User]

I haven't tried that yet, but I think I was "over thinking" this and making it harder than it was. In PHP I think it's very simple. I'm still going to try your SQL and see if it works because it would probably be a faster way to get the data, but here's what I came up with in PHP...I cut out a lot of code here that deals with converting to timestamps and whatnot, but this POC code *seems* to work. It's within a loop that pulls all of the client's rows from the DB:

	// is the START date AFTER the end of target month?	if($event_start_stamp > $end_of_month){		print "<b>Discarding:</b> $event_id, Start: $start_ts to $stop_ts <br>";		continue;	}	// is the END date BEFORE the start of target month?	if($event_stop_stamp < $start_of_month){		print "<b>Discarding:</b> $event_id, Start: $start_ts to $stop_ts <br>";		continue;	}		// if neither of the above conditions match, the the event touches our month..		print "<b>Keeping event:</b> $event_id, Start: $start_ts to $stop_ts <br>";		$proc_list[] = "$event_id|$event_start_stamp|$event_stop_stamp";

Basically if the event end date is before the target month starts or the event start date is after the target month ends, it get thrown out. All the other rows touch the month in some way. This also captures events that span the target month...I think. At least it works on my test table.

[End User]

I tried your SQL but it kept returning an empty set. It may have been something I was doing wrong, but I don't know. If you're bored and want to play with it, here's my test table:

CREATE TABLE IF NOT EXISTS `test_table` (  `event_id` int(11) NOT NULL auto_increment,  `start_ts` datetime NOT NULL,  `stop_ts` datetime NOT NULL,  PRIMARY KEY  (`event_id`)) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=9;-- Dumping data for table `test_table`INSERT INTO `test_table` VALUES(1, '2010-10-03 13:22:28', '2010-10-05 13:22:33');INSERT INTO `test_table` VALUES(2, '2010-10-12 13:45:19', '2011-01-30 13:45:39');INSERT INTO `test_table` VALUES(3, '2010-10-26 12:51:14', '2010-11-02 12:51:20');INSERT INTO `test_table` VALUES(4, '2010-11-03 12:49:17', '2010-11-05 12:49:20');INSERT INTO `test_table` VALUES(5, '2010-11-10 12:48:26', '2010-11-13 12:48:30');INSERT INTO `test_table` VALUES(6, '2010-11-21 12:49:24', '2010-11-24 12:49:31');INSERT INTO `test_table` VALUES(7, '2010-11-29 12:49:58', '2010-12-08 12:50:03');INSERT INTO `test_table` VALUES(8, '2010-12-20 13:23:01', '2010-12-27 13:23:09');

When I run my hacked up load o' code for events that touch November, here's what I get:Event: 2|1286909119|1296416739Event: 3|1288115474|1288720280Event: 4|1288806557|1288979360Event: 5|1289414906|1289674110Event: 6|1290365364|1290624571Event: 7|1291056598|1291834203(I realize some of these events do overlap, but this was just for testing purposes.)

[justsomeguy]

There are three situations you need to account for in your WHERE conditions. I'll use start and stop as the database fields, and begin and end as the values you're looking for in the range:begin <= start and end >= start -- start is in the rangebegin <= stop and end >= stop -- stop is in the rangebegin >= start and end <= stop -- start is before and stop is afterAny record you're looking for should fit in one of those conditions, so those need to be separated by OR.