StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POMysql Find free time slots between appointments
text
Body
copied!<p>I'm trying to find the free time slots for a day, but I'am stuck on the correct query. The open hours or day runs between 9:00 until 22:00 each day the following query returns the correct data through the day except if the first of the booking of the day is at 10:00 it will not return the unbooked slot between 9:00 and 10:00 nor if it is booked from 21:30 until 21:40 it will not show the last slot of the day being free.</p> <p>at the minute to get round this for each day and set_id i'm having to create a day start and day end 1 minute entry represented in the insert as id=1 and id=6.</p> <p>I'm trying to get away from having to create these two inserts for each day and each set_id</p> <pre><code>CREATE TABLE bookings (`id` int, `time_from` datetime, `time_to` datetime, `set_id` int); INSERT INTO bookings (`id`, `time_from`, `time_to`, `set_id`) VALUES (1, '2013-11-20 08:59:00', '2013-11-20 09:00:00', 6), (2, '2013-11-20 09:10:00', '2013-11-20 10:00:00', 6), (3, '2013-11-20 11:10:00', '2013-11-20 11:30:00', 6), (4, '2013-11-20 12:00:00', '2013-11-20 12:40:00', 6), (5, '2013-11-20 16:20:00', '2013-11-20 16:50:00', 6), (6, '2013-11-20 22:00:00', '2013-11-20 22:01:00', 6) ; SELECT Available_from, Available_to FROM ( SELECT @lasttime_to AS Available_from, time_from AS Available_to, @lasttime_to := time_to FROM (SELECT time_from, time_to FROM bookings WHERE set_id = 6 AND time_to >= '2013-11-20 08:59' AND time_from < '2013-11-20 22:01' ORDER BY time_from) e JOIN (SELECT @lasttime_to := NULL) init) x WHERE Available_to > DATE_ADD(Available_from, INTERVAL 9 MINUTE); | AVAILABLE_FROM | AVAILABLE_TO | |---------------------|---------------------------------| | 2013-11-20 09:00:00 | November, 20 2013 09:10:00+0000 | | 2013-11-20 10:00:00 | November, 20 2013 11:10:00+0000 | | 2013-11-20 11:30:00 | November, 20 2013 12:00:00+0000 | | 2013-11-20 12:40:00 | November, 20 2013 16:20:00+0000 | | 2013-11-20 16:50:00 | November, 20 2013 22:00:00+0000 | </code></pre> <p>any help would be appreciated.</p> <p><a href="http://www.sqlfiddle.com/#!2/b30a0/2" rel="nofollow">http://www.sqlfiddle.com/#!2/b30a0/2</a></p> <pre><code> CREATE TABLE `days` ( `id` int(11) NOT NULL AUTO_INCREMENT, `date` date NOT NULL, `time_from` datetime NOT NULL, `time_to` datetime NOT NULL, `step` int(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8; INSERT INTO `days` (`id`, `date`, `time_from`, `time_to`, `step`) VALUES (1, '2013-11-20', '2013-11-20 09:00:00', '2013-11-20 22:00:00', 10), (2, '2013-11-21', '2013-11-21 09:00:00', '2013-11-21 22:00:00', 10); </code></pre> <p>A Little Closer!</p> <pre><code>INSERT INTO `bookings` (`id`, `date`, `time_from`, `time_to`, `set_id`, `name`, `email`, `telephone`, `amount`, `notes`, `is_paid`, `is_booked`) VALUES (25, '2013-11-20', '2013-11-20 09:10:00', '2013-11-20 09:30:00', 1, '', '', '', NULL, NULL, 0, 0), (26, '2013-11-20', '2013-11-20 10:30:00', '2013-11-20 11:30:00', 1, '', '', '', NULL, NULL, 0, 0), (27, '2013-11-20', '2013-11-20 12:30:00', '2013-11-20 13:20:00', 1, '', '', '', NULL, NULL, 0, 0), (29, '2013-11-20', '2013-11-20 15:00:00', '2013-11-20 16:40:00', 1, '', '', '', NULL, NULL, 0, 0); </code></pre> <p>This is what i get</p> <pre><code>availableFrom availableTo 9:00 2013-11-20 09:10:00 2013-11-20 09:30:00 2013-11-20 10:30:00 2013-11-20 11:30:00 2013-11-20 12:30:00 9:00 2013-11-20 15:00:00 2013-11-20 15:00:00 22:00 </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload