Planning management slots/sessions
- by Glide
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 |
+------------+-------+-------+
