mysql show time slots avaliable and time slots bus

问题:

i have this table structure for bookings

|ID|timeBooked         | duration |
|2 |2013-05-09 11:10:00| 30       |
|1 |2013-05-09 14:40:00| 15       |
|AI| timespan          | int(4)   |

duration represent the duration in minutes.

so what i want is to return record set like this when i

query available time slots in 2013-05-09 between 00:00:00 and 23:00:00

|free_from|Free_until| Free |
|00:00:00 |11:10:00  | 1
|11:10:00 |11:40:00  | 0
|11:40:00 |14:40:00  | 1
|14:40:00 |14:55:00  | 0
|14:55:00 |23:00:00  | 1

is this possible by mysql alone ?

回答1:

OK, pure MySQL - as long as one likes those tricks. I need a Variable which is initialized to the "Begin" of the periods to show, something like now() normally.

First just the test data:

create table bookingEvents 
   (id int not null primary key auto_increment,
    timeBooked datetime, 
    duration int
   );


insert into bookingEvents values (null, '2013-05-13 13:22:00', 15);
insert into bookingEvents values (null, '2013-05-13 15:10:00', 45);
insert into bookingEvents values (null, '2013-05-13 19:55:00', 30);
insert into bookingEvents values (null, '2013-05-14 03:22:00', 15);
insert into bookingEvents values (null, '2013-05-14 08:19:00', 15);

Then initializing the "slider":

set @timeSlider='2013-05-10 00:00:00';

Then the select:

select if (d.name = 'Free', @timeSlider, b.timeBooked) as free_from,
       if (d.name = 'Free', b.timeBooked, @timeSlider := b.timeBooked + interval b.duration minute) as free_until,
       d.name as Free
from (select 1 as place, 'Free' as name union select 2 as place, 'Booked' as name) d 
inner join bookingEvents b 
having free_from < free_until
order by b.timeBooked, d.place;

Result:

+---------------------+---------------------+--------+
| free_from           | free_until          | Free   |
+---------------------+---------------------+--------+
| 2013-05-10 00:00:00 | 2013-05-13 13:22:00 | Free   |
| 2013-05-13 13:22:00 | 2013-05-13 13:37:00 | Booked |
| 2013-05-13 13:37:00 | 2013-05-13 15:10:00 | Free   |
| 2013-05-13 15:10:00 | 2013-05-13 15:55:00 | Booked |
| 2013-05-13 15:55:00 | 2013-05-13 19:55:00 | Free   |
| 2013-05-13 19:55:00 | 2013-05-13 20:25:00 | Booked |
| 2013-05-13 20:25:00 | 2013-05-14 03:22:00 | Free   |
| 2013-05-14 03:22:00 | 2013-05-14 03:37:00 | Booked |
| 2013-05-14 03:37:00 | 2013-05-14 08:19:00 | Free   |
| 2013-05-14 08:19:00 | 2013-05-14 08:34:00 | Booked |
+---------------------+---------------------+--------+

If you have a given End-Timestamp, then you have to pre-set that as @timeMaximum

set @timeSlider='2013-05-10 00:00:00';
set @timeMaximum='2013-05-14 08:35:00';


select if (d.name = 'Free', @timeSlider, b.timeBooked) as free_from,
       if (d.name = 'Free', b.timeBooked, @timeSlider := b.timeBooked + interval b.duration minute) as free_until,
       d.name as Free
from (select 1 as place, 'Free' as name union select 2 as place, 'Booked' as name ) as d 
inner join bookingEvents b 
having free_from < free_until
union select @timeSlider as free_from, @timeMaximum as free_until, 'Free' as Free
from (select 1) as d
where @timeSlider < @timeMaximum

order by free_from, free_until
;

回答2:

not sure really how to get the free time records set, however I believe this is the right approach:

SELECT id, 
    DATE_FORMAT(timeBooked, '%H:%i:%s') AS initial_time, 
    DATE_FORMAT(DATE_ADD(timeBooked, INTERVAL duration MINUTE), '%H:%i:%s') AS final_time, 
    duration
FROM your_table t1
WHERE DATE(timeBooked) = '2013-05-09'
ORDER BY t1.timeBooked ASC;

I hope it comes in handy!

回答3:

EDIT: Obsoleted by my other answer. As long as you go simply through the ordered data the performance should be good.

Old:

I think in SQL alone the solution will get really slow as soon as a reasonable number of rows is in the table.

The SQL-table per se does not avoid overlappings.

I would use a table like that:

CREATE TABLE bookingEvents (id int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT, 
    event ENUM('start', 'end') NOT NULL,
    bookingId INT(11) NOT NULL,
    eventTime DATETIME NOT NULL,
    index (eventTime)
);

where bookingId references an additional table with informations about the booking.

Then you can list like that:

SELECT event, eventTime 
FROM bookingEvents 
WHERE (date or something)
ORDER BY eventTime;

you get (as long as the bookings don't overlap) alternating bookingStart and bookingEnd- times, which fill up like

time1  time2  booked
time2  time3  free
time3  time4  booked
time4  time5  free

you can at every entry easily check that free and booked match using the start/end check.

Insertion of a booking is as easy as

INSERT INTO bookingEvents (event, bookingId, eventTime) 
VALUES ('start', $bookingId, $timeBooked                          ), 
       ('end'  , $bookingId, $timeBooked + interval $length minute)
;