MySQL - Find bookings free time slot

Question

I am trying to find free time blocks on a day between the business hours (08:00 to 18:00) of a service station. The schema of the database is as below,

CREATE TABLE IF NOT EXISTS `Bookings` (
  `id` int(11) NOT NULL,
  `date` date NOT NULL,
  `start_time` time NOT NULL,
  `end_time` time NOT NULL,
  `bay_no` int(11) NOT NULL,
  `status` int(11) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

INSERT INTO `Bookings` (`id`, `date`, `start_time`, `end_time`, `bay_no`, `status`) VALUES
(1, '2016-05-24', '09:00:00', '11:30:00', 1, 1),
(2, '2016-05-24', '12:30:00', '15:00:00', 1, 1),
(3, '2016-05-24', '14:00:00', '16:00:00', 1, 2),
(4, '2016-05-24', '08:00:00', '09:30:00', 2, 1);

ALTER TABLE `Bookings`
  ADD PRIMARY KEY (`id`);

The results needs to be generated for a day. status of 1 means booked and 2 are cancelled bookings which can be considered as free slots. The station has 2 bays which can handle bookings.

Expected results for a query for the date 2016-05-24 will be producing the below results

+-----------+------------+----------+--------------+
|  bay_no   | start_time | end_time | block_length |
+-----------+------------+----------+--------------+
|         1 |   08:00:00 | 09:00:00 |     01:00:00 |
|         1 |   11:30:00 | 12:30:00 |     01:00:00 |
|         1 |   15:00:00 | 18:00:00 |     03:00:00 |
|         2 |   09:30:00 | 18:00:00 |     08:30:00 |

I was trying the below query, but with no success.

SELECT available_from, available_to
FROM (
    SELECT @lasttime_to AS available_from, start_time AS available_to, @lasttime_to := end_time
    FROM (SELECT start_time, end_time
          FROM Bookings
          WHERE end_time >= '08:00:00'
            AND start_time < '18:00:00'
            AND status = 1
      ORDER BY start_time) e
    JOIN (SELECT @lasttime_to := NULL) init) x
WHERE available_to > available_from

MySQL - Find bookings free time slot

0 Answers0