MySQL查询以找到两个时间之间可用的时间段。

Question

MySQL查询以找到两个时间之间可用的时间段。

3

我有一个名为“已预订会议”的表格，以下是该表格的结构。

会议表格

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

我在这里尝试找到两个时间之间的可用时间段。假设我输入起始时间为10:00:00，结束时间为01:00:00，则应返回该时间段内所有会议，并找出它们之间的可用时间。

我对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

- scott

另外，为什么不将开始/结束时间存储为时间戳或日期/时间？这些时间是以24小时格式还是12小时格式呈现的？ - Sloan Thrasher

第一个很容易，你可以简单地使用BETWEEN。第二个不那么简单，需要另外一条查询语句。 - Sloan Thrasher

SELECT * FROM bookings WHERE bookings.from_time >= "10:00:00" AND to_time <= "13:00:00" // "01:00:00" 表示凌晨1点。我认为您应该相应地格式化时间。 - Madhan Varadhodiyil

为什么不使用查询语句呢？SELECT * FROM bookings WHERE bookings.from_time>="10:00:00" or to_time <="13:00:00" - Madhan Varadhodiyil

@Kunal.更新问题 - scott

显示剩余11条评论

2个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Carsten Massmann · Answer 1

要查找所有在定义的时间段内（从10:00到13:00）至少有一部分时间的会议，您应该执行以下操作：

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

我希望我对于结束时间的问题理解正确。我更喜欢使用24小时制。为了完整起见，您还应检查date值是否与所调查的时间段相同。

在这里尝试一下小演示：http://rextester.com/LNUD45826

- Michał Turczyn · Answer 2

首先，我使用24小时制，你也应该这样做。

这是您的数据增加了一天，以显示查询将封装同一天内会议之间的间隙：

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 函数很容易实现，我们需要模仿它 (这里是如何做到的 - 它将帮助您理解查询)

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