SQL/PHP:获取时间 X 到 Y 内的所有结果，并检测其间是否有可用时间

问题描述

如果标题有点含糊，请见谅..

Sorry if the title is a bit vague..

我有一个看起来像这样的数据库:

I have a database looking like this:

orderid | roomname | date(DATE) | from(TIME) | to(TIME)

示例数据:

1231 | E12 | 2013-04-05 | 07:00:00 | 10:00:00
1671 | E12 | 2013-04-05 | 13:00:00 | 14:00:00

例如，我正在搜索某个日期并显然获得了当天的所有预订.正如您在示例数据中看到的，房间在 10:00:00 和 13:00:00 之间可用.我怎么能抓住这个?

I'm for example searching up a certain date and obviously getting all reservations on that day.As you can see on the example-data, the room is available between 10:00:00 and 13:00:00. How can I catch this?

我正在考虑遍历时间 07:00:00-16:00:00(每个查询一个)并检查是否从 sql 中得到任何结果.如果我确实得到结果，我会知道房间很忙，但由于这里有未知数(例如 08:00:00 和 09:00:00 不存在)，我会得到误报.

I was thinking about looping through time 07:00:00-16:00:00 (with one query for each) and check if I get any results from sql. If I do get results, I will know that the room is busy, but since there are unknowns here (ex. 08:00:00 and 09:00:00 doesn't exists), I will get false-positives on this.

这里有什么提示吗?

推荐答案

一种方法是使用日历表"，或者如果您只对某一天感兴趣，则可以使用时钟表".以下内容(大致)说明了您将如何使用它.

One way would be using a 'calendar table', or if you're only ever interested in one day a 'clock table' would do. The following illustrates (roughly) how you'd use it.

SELECT clock.time AS available
FROM clock
LEFT JOIN bookings ON clock.time BETWEEN bookings.from AND bookings.to
                  AND bookings.date = '2013-01-01'
WHERE bookings.id IS NULL

http://www.brianshowalter.com/calendar_tables 是如何创建日历的示例在 MySQL 中

http://www.brianshowalter.com/calendar_tables is an example of how to create a calendar in MySQL

这篇关于SQL/PHP:获取时间 X 到 Y 内的所有结果，并检测其间是否有可用时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！