MySQL query to find available slots between two time

Question

I have table called booked meeting and following is the table structure

meeting table

id | meeting_name | from_time  | to_time | date

Following are the structure

CREATE TABLE `bookings` (
  `id` int(11) NOT NULL,
  `meeting_name` varchar(255) DEFAULT NULL,
  `from_time` time DEFAULT NULL,
  `to_time` time DEFAULT NULL,
  `date` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;



INSERT INTO `bookings` (`id`, `meeting_name`, `from_time`, `to_time`, `date`) VALUES(
(1, 'meeting1', '09:00:00', '10:30:00', '2018-07-26'),
(2, 'meeting2', '11:40:00', '12:25:00', '2018-07-26');
(3, 'meeting3', '03:40:00', '04:25:00', '2018-07-26'););

and here i am trying find available slots between two time suppose if i enter from time as 10:00:00 and to time as 01:00:00 then it should return all the meetings between this time and need to find out available timing between them

I am new to mysql so tried few queries but didn't got expected result

SELECT * 
FROM `bookings` 
WHERE bookings.from_time>="10:00:00" and bookings.to_time<="01:00:00"


SELECT * 
FROM `bookings` 
WHERE bookings.from_time>="10:00:00" or
    bookings.to_time<="10:00:00" or
    bookings.from_time>="01:00:00" or
    bookings.to_time<="01:00:00"

Expected Result if i pass time between 10:00:00.00 to 13:00:00 then it should return

and also available slots like

10:31:00 to 11:39:00
12.26 to 03:49:00

Do you want a list of meetings between the two times, or available empty times as your question suggests? — Sloan Thrasher, Jul 26 '18 at 17:07
Also, why not store the start/end times as timestamps or date/times? Are the times in 24 hour format or 12 hour format? — Sloan Thrasher, Jul 26 '18 at 17:08
@SloanThrasher. I need list of meetings booked between two time .and if any slots free avail then i need display time avail to book — scott, Jul 26 '18 at 17:08
@SloanThrasher.if that makes result i can . i am new to mysql so done like this.any help and correction in table is acceptable.thanks — scott, Jul 26 '18 at 17:09
The first is easy and you could simply use BETWEEN. The second isn't as simple, and would be a separate query. — Sloan Thrasher, Jul 26 '18 at 17:09
@SloanThrasher.two queries or sub queries also fine if it doesnt produce result — scott, Jul 26 '18 at 17:11
`SELECT * FROM bookings WHERE bookings.from_time>="10:00:00" and to_time <="13:00:00"` // "01:00:00" means 1 am . I think you should format your time accordingly — Madhan Varadhodiyil, Jul 26 '18 at 17:11
@MadhanM.it will return only one record 11.40 to 12 not that 9 to 10.30 — scott, Jul 26 '18 at 17:14
Do you need to satisfy either of the time? from_time > 10 am or to_time < 1pm ? — Madhan Varadhodiyil, Jul 26 '18 at 17:19
Why not or Query then ? `SELECT * FROM bookings WHERE bookings.from_time>="10:00:00" or to_time <="13:00:00"` — Madhan Varadhodiyil, Jul 26 '18 at 17:26
you should specify your expected result as well to let us help you quickly — Kunal, Jul 26 '18 at 17:34
@vision how about `SELECT * FROM bookings WHERE (bookings.from_time>="10:00:00" AND to_time <="13:00:00") OR (from_time<="13:00:00" AND to_time >="10:00:00" )` — Madhan Varadhodiyil, Jul 26 '18 at 18:03

Carsten Massmann · Answer 1 · 2018-07-26T18:07:19.937

To find all meetings with at least some of their time within the defined time slot (from 10:00h to 13:00h) you should do

SELECT * 
FROM `bookings` 
WHERE bookings.from_time<="13:00:00" -- end of time slot
AND   bookings.to_time>=  "10:00:00" -- start of time slot

I hope that I understood the question properly with respect to the end time. I prefer to use the 24h notation. For completeness you should also check that the date value is identical to the investigated time slot.

Try out the little demo here: http://rextester.com/LNUD45826

score 0 · Answer 2 · answered Jul 26 '18 at 17:58

Firstly, I used 24h format, you should too.

Here's your data enriched by one additional day, to show, that query will encapsulate gaps between meetings within the same day:

CREATE TABLE `bookings` (
  `id` int(11) NOT NULL,
  `meeting_name` varchar(255) DEFAULT NULL,
  `from_time` time DEFAULT NULL,
  `to_time` time DEFAULT NULL,
  `date` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `bookings` (`id`, `meeting_name`, `from_time`, `to_time`, `date`) VALUES
(1, 'meeting1', '09:00:00', '10:30:00', '2018-07-25'),
(2, 'meeting2', '11:40:00', '12:25:00', '2018-07-25'),
(3, 'meeting3', '03:40:00', '04:25:00', '2018-07-25'),
(1, 'meeting4', '09:00:00', '10:30:00', '2018-07-26'),
(2, 'meeting5', '11:40:00', '12:25:00', '2018-07-26'),
(3, 'meeting6', '03:40:00', '04:25:00', '2018-07-26');

And here's your query, it's easy with LAG function implemented in SQL Server, here we have to imitate it (here's how to do it - it will help you understand the query)

select @start := cast('10:00:00' as time), @end := cast('13:00:00' as time),
       @time := cast(null as time), @date := cast(null as date);

select date, to_time_lag, from_time, gap_between_meetings from (
  select id,
         meeting_name,
         from_time,
         @time to_time_lag,
         to_time,
         @date date_lag,
         case when @date = date then timediff(from_time, @time) end gap_between_meetings,
         @time := to_time,
         date,
         @date := date
  from bookings
  where from_time between @start and @end
     or to_time between @start and @end
  order by date, from_time
) a where gap_between_meetings is not null

This is good, but I think you can go further . Date and time should be stored as a single entity — Strawberry, Jul 26 '18 at 19:19

MySQL query to find available slots between two time

2 Answers2