Mysql shows timeslots available and timeslots are busy from the table
I have this table structure when booking
|ID|timeBooked | duration |
|2 |2013-05-09 11:10:00| 30 |
|1 |2013-05-09 14:40:00| 15 |
|AI| timespan | int(4) |
duration represents duration in minutes.
so i want to revert the entry set like this when i
available available time slots on 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 it only mysql possible?
source to share
OK, pure MySQL - as long as you like these tricks. I need a variable that initializes the "Start" periods to show, something like now () usually.
First, just 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 initialize the "slider":
set @timeSlider='2013-05-10 00:00:00';
Then 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 preset timestamp you need to preset it 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
;
source to share
Not sure if really how to set free time entries, however I believe this is the correct 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 this comes in handy!
source to share
EDIT: Worried about my other answer. As long as you are just stepping through the ordered data, the performance should be good.
Old:
I think that only in SQL, the solution will be very slow once the table has a reasonable number of rows.
The SQL table itself does not exclude overlap.
I would use a table like this:
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 refers to an additional table with booking information.
Then you can list the following:
SELECT event, eventTime
FROM bookingEvents
WHERE (date or something)
ORDER BY eventTime;
you get (as long as the orders don't overlap) by alternating the booking. Start and booking. Times that fill up for example
time1 time2 booked
time2 time3 free
time3 time4 booked
time4 time5 free
you can easily check on every entry that a free and reserved match is in use by checking the start and end.
Enabling a booking is as easy as
INSERT INTO bookingEvents (event, bookingId, eventTime)
VALUES ('start', $bookingId, $timeBooked ),
('end' , $bookingId, $timeBooked + interval $length minute)
;
source to share