Planning management slots/sessions

Question

I have a planning structure on two tables to store available slots by day, and sessions.

A slot is defined by a range of time in the day.

CREATE TABLE slot (
  `id` int(11) NOT NULL AUTO_INCREMENT
, `date` date
, `start` time
, `end` time
);

Sessions can't overlap themselves and must be wrapped in a slot.

CREATE TABLE session (
  `id` int(11) NOT NULL AUTO_INCREMENT
, `date` date
, `start` time
, `end` time
);

I need to generate a list of available blocks of time of a certain duration, in order to create sessions.

Example:

INSERT INTO slot
  (date, start, end)
VALUES
  ("2010-01-01", "10:00", "19:00")
, ("2010-01-02", "10:00", "15:00")
, ("2010-01-02", "16:00", "20:30")
;

INSERT INTO slot
  (date, start, end)
VALUES
  ("2010-01-01", "10:00", "19:00")
, ("2010-01-02", "10:00", "15:00")
, ("2010-01-02", "16:00", "20:30")
;

2010-01-01

   <##><####>                               <- Sessions
 ------------------------------------       <- Slots
10  11  12  13  14  15  16  17  18  19  20

2010-01-02

         <##########>          <########>   <- Sessions
 --------------------    ------------------ <- Slots
10  11  12  13  14  15  16  17  18  19  20

I need to know which spaces of 1 hour I can use:

+------------+-------+-------+
| date       | start | end   |
+------------+-------+-------+
| 2010-01-01 | 13:00 | 14:00 |
| 2010-01-01 | 14:00 | 15:00 |
| 2010-01-01 | 15:00 | 16:00 |
| 2010-01-01 | 16:00 | 17:00 |
| 2010-01-01 | 17:00 | 18:00 |
| 2010-01-01 | 18:00 | 19:00 |
| 2010-01-02 | 10:00 | 11:00 |
| 2010-01-02 | 11:00 | 12:00 |
| 2010-01-02 | 16:00 | 17:00 |
+------------+-------+-------+

Answer 1

I think you're life is going to be a lot easier if you approach this algorithmically rather than using a query. That could be using a sproc and that could mean writing an algorithm in the middle tier.

My recommendation is to just do it in the middle tier. Make the database responsible for storing the slots and sessions and leave the computations to something better suited to the task.

Databases are for persisting knowledge, not hosting business logic.