在 mysql 中查找最近空闲时间段的查询 - 为什么它不起作用? [英] A query for finding the nearest free time slot in mysql - why it doesn't work?

问题描述

我有一个名为 notes 的表格，里面有三个字段:

I have a table called notes and there I have three fields:

id  |      start_time       |  duration
 1  |  2015-10-21 19:41:35  |    15
 2  |  2015-10-21 19:41:50  |    15
 3  |  2015-10-21 19:42:05  |    15
 4  |  2015-10-21 19:42:35  |    15
etc.

id 是 INT 字段

start_time 是一个时间戳字段

duration 是 INT 字段，表示每个事件持续的秒数.

duration is INT field that tells the number of seconds of how long each event is.

我正在编写一个 SQL 查询，它将获取 3 个字段作为输入:duration、begin_time 和 end_time 并将返回给我一个 timestamp 字段，它甚至可以适合新的地方.

I'm writing an SQL query that will get the 3 fields as an input: duration, begin_time and end_time and will return me a timestamp field of where exactly new even can fit.

基于 StackOverflow 上许多与我类似的问题(主要是这个特定的问题 MySQL/PHP - 查找可用时间段 ) 我创建了一个查询:

Based on lots of questions similar to mine on StackOverflow (mostly this particular one MySQL / PHP - Find available time slots ) I created a query:

SELECT (a.start_time + a.duration) AS free_after FROM notes a
WHERE NOT EXISTS ( SELECT 1 FROM notes b
WHERE b.start_time BETWEEN (a.start_time+a.duration) AND
(a.start_time+a.duration) + INTERVAL '$duration' SECOND) AND 
(a.start_time+a.duration) BETWEEN '$begin_time' AND '$end_time'

但是当我按如下方式运行时:

But when I run it as follows:

SELECT (a.start_time + a.duration) AS free_after FROM notes a
WHERE NOT EXISTS ( SELECT 1 FROM notes b WHERE b.start_time
BETWEEN (a.start_time+a.duration) AND (a.start_time+a.duration) + INTERVAL 15 SECOND )
AND (a.start_time+a.duration) BETWEEN '2015-11-21 19:41:30' AND '2015-11-21 19:43:50'

我没有找到任何记录，尽管 - 查看上面提到的表格 - 我应该得到一个结果:

I get no records found, even though - looking at the table mentioned above - I should get a result:

     free_after 
2015-11-21 19:42:20

(因为在这两个记录之间:

(because between those two records:

 3  |  2015-10-21 19:42:05  |    15
 4  |  2015-10-21 19:42:35  |    15

有一个 15 秒的空闲时间).那么为什么我的查询不能正常工作?

there is a free slot of 15 seconds). So why does my query not work properly?

====

在遵循 Richard 的建议之后，我做了一个 show warnings 查询，它返回了我的值:

After following Richard's advice I did a show warnings query and it returned me the values:

Truncated incorrect DOUBLE value: '2015-11-21 19:4...
Truncated incorrect DOUBLE value: '2015-11-21 19:4...
Truncated incorrect DOUBLE value: '2015-11-21 19:4...
Truncated incorrect DOUBLE value: '2015-11-21 19:4...
Incorrect datetime value: '0'

我不确定是什么原因造成的，你能帮我解决这个问题吗?

and I'm not sure what causes it, could you help me with that?

====== 编辑 2

===== EDIT 2

好的，在遵循另一个@Richard 的建议之后(顺便说一句，谢谢！)我将查询更改为以下内容:

Okay, after following another @Richard's advice (thanks btw!) I changed my query to the following one:

SELECT (a.start_time + INTERVAL a.duration SECOND) AS free_after FROM notes a
WHERE
NOT EXISTS ( SELECT 1 FROM notes b WHERE b.start_time
BETWEEN (a.start_time + INTERVAL a.duration SECOND) AND
(a.start_time + INTERVAL a.duration SECOND) + INTERVAL 15 SECOND ) AND
(a.start_time + INTERVAL a.duration SECOND) BETWEEN '2015-11-21 19:41:30' AND '2015-11-21 19:43:50'

这次我得到了一个结果(这是个好消息):

and this time I got a result (Which is a good message):

2015-10-21 19:42:50

但这是一个糟糕的价值(这是一个更糟糕的信息).应该是 2015-10-21 19:42:20... 还有什么我在这里做错了吗?当我在它之后立即执行 show warnings 时 - 这里没有更多内容:(

but it's a bad value (which is a worse message). It should be 2015-10-21 19:42:20... Is there anything else that I'm doing wrong here? When I do show warnings right after it - there's nothing more here :(

推荐答案

BETWEEN 子句完全匹配结束日期.对于您的样本数据，一旦您添加 15 秒的持续时间和固定持续时间(也是 15 秒)，它就会匹配上一个 30 秒的样本数据，因此排除在外.将您的查询修改为

The BETWEEN clause matches the end dates exactly. With your sample data, once you add the duration of 15 seconds and the fixed duration, also of 15 seconds, it matches your last sample data that is 30 seconds on, and hence rules out. Modify your query to

SELECT (a.start_time + INTERVAL a.duration SECOND) AS free_after FROM notes a
WHERE
NOT EXISTS ( SELECT 1 FROM notes b WHERE b.start_time
BETWEEN (a.start_time + INTERVAL a.duration SECOND) AND
(a.start_time + INTERVAL a.duration SECOND) + INTERVAL 15 SECOND - INTERVAL 1 MICROSECOND) AND
(a.start_time + INTERVAL a.duration SECOND) BETWEEN '2015-10-21 19:41:30' AND '2015-10-21 19:43:50'

(即减去 1 微秒作为调整)

(i.e. taking off 1 microsecond as an adjustment)

你就会得到答案:

+---------------------+
| free_after          |
+---------------------+
| 2015-10-21 19:42:20 |
| 2015-10-21 19:42:50 |
+---------------------+
2 rows in set (0.00 sec)

这篇关于在 mysql 中查找最近空闲时间段的查询 - 为什么它不起作用?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！