MySQL overlapping dates, none conflicting

Question

I've seen lots of threads about date ranges in MySQL but I still don't seem to be able to find an answer for what I'm looking for so any help will be greatly received.

I have a MySQL table with 3 columns, date - startTime - finishTime. The date is a MySQL 'date' type field and the start and finish times are both 'time' type fields.

Say for example I have an entry in the database as follows, lets call this session 1;

date = 2011-06-30, startTime = 09:00:00, finishTime = 11:00:00

If I come to add another session I need to make sure that it doesn't conflict with an existing session. So the following would fail because it falls in between session 1 start and finish times.

date = 2011-06-30, startTime = 10:00:00, finishTime = 12:00:00

So the record can only be inserted 'AFTER' or 'BEFORE' an existing session.

I'm using PHP/MySQL and am going on the basis that a query can be run and if there 'are' matching results then, fail, if there 'arent' matching results then insert.

Thanks in advance.

Answer 1

I faced this problem of date overlapping detection, my first idea was to do something like Hammerite's solution but i found this solution incomplete mainly because there are too many possible scenarios where two date ranges can be in conflict:

The query I ended up using to solve this was something like:

/* overlapping dates */
SELECT * 
FROM my_table
WHERE `date` = $date AND
  NOT (
    `start_time` < $start_time and `finish_time` < $start_time
    OR
    `start_time` > $end_time and `finish_time` > $end_time
  )

For more details you can check my blog here

Answer 2

I'm using PHP/MySQL and am going on the basis that a query can be run and if there 'are' matching results then, fail, if there 'arent' matching results then insert.

Well, try this. Here :date: is the date of the entry you are going to add, and :start-time: and :finish-time: are its start and finish times respectively.

SELECT EXISTS (
    SELECT
        1
    FROM
        TableName
    WHERE
        `date` = :date: AND
        ( :start-time: BETWEEN startTime AND finishTime OR
          :finish-time: BETWEEN startTime AND finishTime OR
          startTime BETWEEN :start-time: AND :finish-time:
          )
) AS `Clash`

Answer 3

I would structure the table in a different way. I'd have two columns, both datetime type, named session_start and session_end.

Logic is: you cannot insert new session if it's session_start time isn't > or < than old session session_end.

Answer 4

Assuming $date, $startTime and $finishTime are your PHP variables that store the date, start time and finish time respectively to be inserted.

$query = 'INSERT INTO `session`
SELECT \'' . $date . '\', \'' . $startTime . '\', \'' . $finishTime . '\'
FROM `session`
WHERE NOT EXISTS (SELECT *
    FROM `session`
    WHERE `date` = \'' . $date . '\'
    AND \'' . $startTime . '\' BETWEEN `startTime` and `finishTime`
)';

Hope this helps.

Answer 5

I would use the simple:

INSERT INTO session
  ( `date`, startTime, finishTime )
  SELECT
  ( $date, $startTime, $finishTime )
  WHERE NOT EXISTS
    ( SELECT
          *
      FROM
          session
      WHERE
            `date` = $date 
        AND $startTime <  finishTime 
        AND  startTime < $finishTime 

    )

The < should be changed to <= if you want the two periods 09:00 - 11:00 and 11:00 - 13:00 to collide.