MySQL查询以查找两个时间之间的可用插槽

Question

我有一个名为 booked meeting 的表，下面是表结构

session table

id | meeting_name | from_time  | to_time | date

结构如下

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

我在这里尝试找到两个时间之间的可用时段假设如果我输入 from time 作为 10:00:00 和 to time作为 01:00:00 那么它应该返回这段时间之间的所有 session ，并且需要找出它们之间的可用时间

我是 mysql 的新手，所以尝试了一些查询但没有得到预期的结果

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"

预期结果如果我在 10:00:00.00 到 13:00:00 之间度过时间，那么它应该返回

还有像

这样的可用插槽

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

Answer 1

首先，我使用的是 24 小时格式，您也应该使用。

这是您的数据，增加了一天，以表明该查询将封装同一天 session 之间的间隔:

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

这是您的查询，使用 SQL Server 中实现的 LAG 函数很容易，这里我们必须模仿它(here's how to do it - 这将帮助您理解查询)

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