Member Avatar for 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

  • 2 Contributors
  • 4 Replies
  • 185 Views
  • 2 Weeks Discussion Span
  • Latest Post Latest Post by digital-ether

Dani AI

Generated

Short answer: treat end-times as exclusive and test for overlap with a single, simple condition — the new event overlaps an existing event if the new start is before the existing end AND the new end is after the existing start. That keeps events that exactly touch (one ends at 10:00, the next begins at 10:00) from being considered conflicts. This is the concise idea behind 's suggestion and will cover the “begin during”, “end during” and “enveloped” cases that was handling with several ORs.

A safe, practical pattern (using prepared statements) is to check for any match and stop at the first one found:

$stmt = $pdo->prepare("
  SELECT 1 FROM schedules
  WHERE itemID = :item
    AND :new_start < endingTimestamp
    AND :new_end   > startingTimestamp
  LIMIT 1
");
$stmt->execute([':item'=>$itemID, ':new_start'=>$start, ':new_end'=>$end]);
$conflict = (bool) $stmt->fetchColumn();

If you want inclusive end-times instead (treat end as occupied), switch the comparisons to <= / >= accordingly. Always validate in the application that startingTimestamp < endingTimestamp before attempting the insert (or add a DB constraint where your MySQL version enforces CHECK).

For performance, add indexes so the DB can narrow rows by item quickly — for example an index starting with itemID (and including startingTimestamp or endingTimestamp). Also use LIMIT 1 in the existence check so MySQL can stop early when a conflict is found.

Finally, guard against race conditions: two processes might both check “no conflict” and then both insert. Use an application-level advisory lock per item (MySQL’s GET_LOCK/RELEASE_LOCK) or wrap the check+insert in a transaction with an appropriate lock strategy so only one writer at a time can schedule for a given item. Release locks in a finally/cleanup block to avoid leaving them held on errors.

Recommended Answers

All 4 Replies

Member Avatar for 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`)
)
Member Avatar for 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
Member Avatar for 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`) )
Member Avatar for 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?

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.