Searching for Time Conflicts?

[david.1982]

Hello gurus!

Hoping someone can help me out. I am building a calendaring program for items with php/mysql. Each item will have an entry into a table, listing a starting unix timestamp and an ending unix timestamp.

What I'm trying to do is build a query that will search all entries for a given item, and return rows that conflict with a given starting and ending timestamp.

So, for example, if I want to put something on the calendar to start on 12/10/08 @ 10am and end on 12/12/08 @ 8pm (using unix timestamps instead, of course), I want to first check the table to look for any previously scheduled slots that:

• begin before, but end during

• begin during

• are completely enveloped

does that make sense? It should also be noted that an event can end at the same time an event begins and vice-versa (ie event 1 can end at 10am and a new one can begin at 10am without conflict)but there cannot be overlap beyond that.

Here is my table layout, if that will help:

CREATE TABLE `schedules` (
  `scheduleID` int(11) NOT NULL auto_increment,
  `itemID` int(11) NOT NULL,
  `forumID` int(11) NOT NULL,
  `statusID` tinyint(3) unsigned NOT NULL,
  `startingTimestamp` bigint(20) NOT NULL,
  `endingTimestamp` bigint(20) NOT NULL,
  `detailNotes` varchar(255) NOT NULL,
  PRIMARY KEY  (`scheduleID`),
  FOREIGN KEY `itemID` (`itemID`) REFERENCES `items`(`itemID`),
  FOREIGN KEY `statusID` REFERENCES `schedule_status_types`(`ID`)
) TYPE=InnoDB;

Any help would be apprciated :) Been racking my brain for a while on this...should probably take a nap.

-Dave

[david.1982]

Well I stared at this for a bit and finally came up with a workable solution. My MySQL skills are rather intermediate so this may not be the best way to run this sort of query. Here's what I came up with so far..any comments/suggestions/rewrites apprciated :)

SELECT count(*) as count FROM `schedules`
WHERE `itemID`={$itemID} 
AND
(
({$start} > `startingTimestamp` AND {$start} < `endingTimestamp`)
OR ({$start} < `startingTimestamp` AND {$end} > `startingTimestamp` AND {$end} < `endingTimestamp`)
OR ({$start} < `startingTimestamp` AND {$end} > `endingTimestamp`)
)

[digital-ether]

Well I stared at this for a bit and finally came up with a workable solution. My MySQL skills are rather intermediate so this may not be the best way to run this sort of query. Here's what I came up with so far..any comments/suggestions/rewrites apprciated :)

SELECT count(*) as count FROM `schedules`
WHERE `itemID`={$itemID} 
AND
(
({$start} > `startingTimestamp` AND {$start} < `endingTimestamp`)
OR ({$start} < `startingTimestamp` AND {$end} > `startingTimestamp` AND {$end} < `endingTimestamp`)
OR ({$start} < `startingTimestamp` AND {$end} > `endingTimestamp`)
)

That wouldn't accommodate the events that start together, or those that end together.

You'll want to use <= and >= appropriately.

Try:

if start1 < end2 and end1 > start2

[david.1982]

Took me a while to figure out what you meant, lol! Modified my code to the following, and it seems to catch the 2 conflicts you mentioned:

SELECT count(*) as count FROM `schedules`
									   WHERE `itemID`={$itemID} 
									   AND ( ({$start} > `startingTimestamp` AND {$start} < `endingTimestamp`)
									   OR    ({$start} < `startingTimestamp` AND {$end} > `startingTimestamp` AND {$end} <= `endingTimestamp`)
									   OR    ({$start} = `startingTimestamp`)
									   OR    ({$start} < `startingTimestamp` AND {$end} > `endingTimestamp`) )

[digital-ether]

Took me a while to figure out what you meant, lol! Modified my code to the following, and it seems to catch the 2 conflicts you mentioned:

SELECT count(*) as count FROM `schedules`
									   WHERE `itemID`={$itemID} 
									   AND ( ({$start} > `startingTimestamp` AND {$start} < `endingTimestamp`)
									   OR    ({$start} < `startingTimestamp` AND {$end} > `startingTimestamp` AND {$end} <= `endingTimestamp`)
									   OR    ({$start} = `startingTimestamp`)
									   OR    ({$start} < `startingTimestamp` AND {$end} > `endingTimestamp`) )

Try:

SELECT count(*) as count FROM `schedules` WHERE `itemID`={$itemID} AND {$start} < `endingTimestamp` AND {$end} > `startingTimestamp` LIMIT 1;

does that work?