0

I have a database to manage my schedule the database looks like:

CREATE TABLE activities(
name VARCHAR(255),
startTime INT(11),
endTime INT(11)
);

Now I'd like to detect overlaps in activities. so lets say i have the following variables:

$start=time();
$end=time()+3600;

now i want to query the database to get ALL activities that overlap with these times. Anyone has an idea how to do this?

Thank you!

p.s. I realize similiar questions have already been asked but i couldn't find the answer for MySQL with a table similiar to this one.

user746379
  • 1,492
  • 2
  • 13
  • 21
  • possible duplicate of [MySQL range date overlap check](http://stackoverflow.com/questions/2545947/mysql-range-date-overlap-check) – binaryLV Sep 28 '11 at 10:37

3 Answers3

1

use something like

SELECT * FROM activities WHERE
(.$start. BETWEEN startTime AND endTime) OR
(.$end. BETWEEN startTime AND endTime) OR
(startTime < .$start. AND endTime > .$end.);
Yahia
  • 69,653
  • 9
  • 115
  • 144
  • The solution I propose does the same thing with only two comparisons. – Alfwed Sep 28 '11 at 11:02
  • You don't think so? Why not? My query does the same if $end > $start which I think is quite safe to assume. – Alfwed Sep 28 '11 at 12:31
  • @Alfwed, your query won't return result if `$start` equals to ``endTime`` or `$end` equals to ``startTime``, will it? `n between min and max` is the same as `n >= min and n <= max` rather than `n > min and n < max`. – binaryLV Sep 28 '11 at 12:50
  • I could argue that a class scheduled between 3pm and 4pm does NOT overlap a class scheduled between 4pm and 5pm. It depends what the OP meant by overlap i think. – Alfwed Sep 28 '11 at 13:21
  • Anyway, the query proposed by Yahia is still doable with only 2 comparisons. – Alfwed Sep 28 '11 at 13:28
  • @Alfwed, I didn't say that your solution does not work. I just said that that your query does NOT return the same results as Yahia's query, although you wrote that it does. – binaryLV Sep 28 '11 at 13:32
0

I guess you'd want something like:

mysql_query( "SELECT * FROM `activities` WHERE `startTime`<".$start." AND `endTime`>".$end);

Just be careful to properly index your tables, otherwise that's gonna be one heck of a slow query.

Niet the Dark Absol
  • 320,036
  • 81
  • 464
  • 592
  • would work for some situations but it won't work if my new activity starts before an existing one and ends after an existing one. – user746379 Sep 28 '11 at 10:38
  • no, you need to take into account activities where startTime between $start and $end *OR* endTime between $start and $end – Carlos Campderrós Sep 28 '11 at 10:39
0

Use the query below.

"SELECT * 
FROM `activities` 
WHERE `endTime`>".$start." AND `startTime`<".$end

The solution become quite obvious when you draw your problem down.

Alfwed
  • 3,307
  • 2
  • 18
  • 20