CODEWITHSUNDEEP
mysqldateintervals
Momen Zalabany
Momen Zalabany

Reputation: 9007

mysql show time slots avaliable and time slots busy from table

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 ?

Upvotes: 0

Views: 3425

Answers (4)

Sen K Mathew
Sen K Mathew

Reputation: 29

@flaschenpost Kindly Check your answer with two dates. I think it is not working as expected. I am saying about this answer of yours 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;

not working. Always giving the constant answer

Upvotes: 0

flaschenpost
flaschenpost

Reputation: 2235

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

Upvotes: 1

flaschenpost
flaschenpost

Reputation: 2235

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
;

Upvotes: 5

medina
medina

Reputation: 8159

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!

Upvotes: 1

Related Questions