6

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.

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.

I'm trying to get away from having to create these two inserts for each day and each set_id

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 |

any help would be appreciated.

http://www.sqlfiddle.com/#!2/b30a0/2

    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);

A Little Closer!

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);

This is what i get

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
Tim
  • 83
  • 1
  • 6

1 Answers1

0

One possible option is to left-join the table with itself something along these lines: 

select (case when ltime_to is null then '9:00' else ltime_to end) as availableFrom,
rtime_from as availableTo
from (select l.time_to as ltime_to, r.time_from as rtime_from from bookings r 
left join bookings l on l.id=r.id-1) lr
union
select max(time_from) as availableFrom, '22:00' as availableTo from bookings
Ashalynd
  • 12,363
  • 2
  • 34
  • 37
  • No that way round returns nothing. – Tim Nov 20 '13 at 17:58
  • Ah I see, did not get immediately what you were doing. One of the easy silly ways would be to insert a bogus booking that starts and stops at the first moment of the day, which you could then use as the first time_from. Other option, if you guarantee that your table has non-overlapping consecutive periods, could be to do LEFT JOIN the table with itself on l.id=r.id-1 and then use either previous time_from or beginning of day as time_from. – Ashalynd Nov 20 '13 at 20:48
  • thanks for that, the bogus entries are what the first and last entries are on the insert above - id's 1 & 6 you will notice they are only 1 minute bookings. This is what I'm trying to get away from because I'm having to create those bogus bookings for 6 venues or set_id's every day. and thought there must be a way to integrate a start and end time to the query so i don't have to do the bogus inserts. ( i like the name bogus! ) i have the start and end times in the days table that i could use? thanks – Tim Nov 20 '13 at 21:24
  • How about the other option - left-joining that table with itself, but shifted a row back? – Ashalynd Nov 20 '13 at 22:23
  • I'm not sure i follow you with that? – Tim Nov 20 '13 at 22:27
  • edited my answer with the example of left-joining a table with itself. Have to say that I had a similar problem (subtract booked time slots from a chunk of time) but I solved it programmatically. – Ashalynd Nov 20 '13 at 23:05
  • Now that nearly works! I will read it again in the morning but its looking good. I've got one random 9.00 in the start time where it shouldn't be a 9.00 but i will get some sleep and try working through it. Thanks for now! I will edit my question and add the insert just incase you fancy having a look at the random 9.00 one ;-) thanks again. – Tim Nov 21 '13 at 00:00