查找时隙在数据库表中是否可用 [英] find if time slots are available in database table

问题描述

我有以下数据库表与列。

i have the following database table with columns.

+----+---------------------+---------------------+------------------+
| id | start_datetime      | end_datetime        | arena_surface_id |
+----+---------------------+---------------------+------------------+
|  1 | 2012-11-22 02:30:00 | 2012-11-22 05:00:00 |                2 |
|  2 | 2012-11-22 00:00:00 | 2012-11-22 02:30:00 |                2 |
|  3 | 2012-11-22 05:00:00 | 2012-11-22 08:00:00 |                2 |
|  4 | 2012-11-22 08:00:00 | 2012-11-22 11:00:00 |                2 |
|  5 | 2012-11-22 11:00:00 | 2012-11-22 17:00:00 |                2 |
+----+---------------------+---------------------+------------------+

用用户输入的日期，开始时间和结束时间，我想知道在给定的时间段内是否有为特定arena_surface_id预订的槽。

with the user input of date, start time, and end time, i want to find out if there are any slots booked for particular arena_surface_id within the given period of time.

例如，如果用户输入是。

for example if user input is.

start_datetime : 2012-11-22 6:00
end_datetime : 2012-11-22 9:00
arena_surface_id : 2

我想知道任何匹配给定日期时间（在上面的例子中它应该匹配）的6:00和9:00之间的时隙的行。

i want to know if there are any rows which matches time slot between 6:00 and 9:00 for the given date time (in the above example it should match).

什么是正确的mysql查询这个？

what would be the proper mysql query for this?

这实际上是一个跟进问题如何检测两个时间段的非重叠？

This is actually a follow up question for this how would I detect a non-overlap of two time periods?

UPDATE

@March这是我使用您的查询时返回的内容。

@March this is what it returns when i use your query.

SELECT 
    id, 
    start_datetime, 
    end_datetime, 
    arena_surface_id 
FROM 
    gce_arena_surface_booking 
WHERE 
    (start_datetime BETWEEN '2012-11-22 04:00:00' AND '2012-11-22 09:00:00')
OR 
    (end_datetime BETWEEN '2012-11-22 4:00:00' AND '2012-11-22 9:00:00')


+----+---------------------+---------------------+------------------+
| id | start_datetime      | end_datetime        | arena_surface_id |
+----+---------------------+---------------------+------------------+
|  1 | 2012-11-22 02:30:00 | 2012-11-22 05:00:00 |                2 |
|  3 | 2012-11-22 05:00:00 | 2012-11-22 08:00:00 |                2 |
|  4 | 2012-11-22 08:00:00 | 2012-11-22 11:00:00 |                2 |
+----+---------------------+---------------------+------------------+

<

the middle record does not match the condition, and still fetches it.

推荐答案

SELECT * 
FROM yourTable
WHERE (startTime BETWEEN 'yourStartImput' AND 'yourEndImput') OR (endTime BETWEEN 'yourStartImput' AND 'yourEndImput')

因此，您可以检查输入之间是否有匹配开始或结束。

So you check if there's a match that starts or ends between your input.

对于您的评论，您的最终查询应该如下所示：

For your comment, your final query should look like this:

SELECT * 
FROM yourTable
WHERE ((startTime BETWEEN 'yourStartImput' AND 'yourEndImput') 
  OR (endTime BETWEEN 'yourStartImput' AND 'yourEndImput')) 
  AND arena_surface_id = 1

这篇关于查找时隙在数据库表中是否可用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！