如何在MySQL中存储日期和时间范围而不会重叠 [英] How to store date and time ranges without overlap in MySQL

问题描述

我正在尝试找到正确的查询，以检查日期和时间范围在MySQL表中是否重叠，这是该表:

I'm trying to find the right query to check if date and time ranges overlap in the MySQL table, here is the table:

id      pickup_date     pickup_time     return_date     return_time
1       2016-05-01      12:00:00        2016-05-31      13:00:00
2       2016-07-01      12:00:00        2016-07-04      15:00:00

以下是有关每个即将到来的预订的数据，需要根据预订"表进行检查:

Here are the data about every reservation which is coming and need to be checked against the "Reservations" table:

pickup_date = '2016-04-01';
pickup_time = '12:00:00'
return_date = '2016-05-01';
return_time = '13:00:00'

使用此数据，保留与数据库中的保留重叠.请注意:新的保留可以是过去的，也可以是将来的.

with this data the reservation overlap the one in the database. Take a note: the new reservation can be in the past or in the future.

编辑(如spencer7593所建议，这是目前的工作版本):

    SET @new_booking_pickup_date = '2016-04-01';
    SET @new_booking_pickup_time = '12:00:00';
    SET @new_booking_return_date = '2016-05-01';
    SET @new_booking_return_time = '13:00:00';

    SELECT * FROM Reservation WHERE NOT 
( CONCAT(@new_booking_pickup_date,' ',@new_booking_pickup_time) > CONCAT(return_date,' ',return_time) + INTERVAL 0 DAY OR CONCAT(@new_booking_return_date,' ',@new_booking_return_time) < CONCAT(pickup_date,' ',pickup_time) + INTERVAL 0 DAY);

，因此此查询将导致:

id      pickup_date     pickup_time     return_date     return_time
1       2016-05-01      12:00:00        2016-05-31      13:00:00

推荐答案

很容易确定给定时间段是否与另一个时间段不重叠.

It's pretty easy to determine if a given period doesn't overlap with another period.

为了便于表示比较，对于时期1，我们将b1和e1表示为开始和结束.对于期间2，b2和e2.

For ease of expressing the comparison, for period 1, we'll let the begin and end be represented by b1 and e1. For period 2, b2 and e2.

如果满足以下条件，则没有重叠:

There is no overlap if the following is true:

  b1 > e2 OR e1 < b2

(我们可以质疑b1和e2的相等性是否被视为重叠，并根据需要进行调整.)

(We can quibble whether equality of b1 and e2 would be considered an overlap or not, and adjust as necessary.)

如果存在重叠，则该测试的否定将返回TRUE ...

The negation of that test would return TRUE if there was an overlap...

 NOT (b1 > e2 OR e1 < b2)

因此，要确定是否存在与建议的期间重叠的行，我们需要一个查询来测试是否返回了一行...

So, to find out if there is a row that overlaps with the proposed period, we would need a query that tests whether a row is returned...

让我们假设我们要检查的表的列st和et(DATETIME)代表每个周期的开始和结束.

Let's assume that table we are going to check has columns st and et (DATETIME) representing the beginning and ending of each period.

查找与建议期间以b1和e1

  SELECT t.* FROM t WHERE NOT (b1 > t.et OR e1 < t.st)

因此，对于仅检查重叠行是否存在的查询，我们可以执行以下操作:

So for a query to just check for the existence of an overlapping row, we could do something like this:

  SELECT EXISTS (SELECT 1 FROM t WHERE NOT (b1 > t.et OR e1 < t.st))

那很简单.

当我们将日期时间的date和time分量的(无法解释的)拆分调整为单独的列时，它将看起来更加复杂.(如问题所示).

It's going to look a lot more complicated when we make the adjustment for the (inexplicable) split of the date and time components of a datetime into separate columns (as shown in the question).

将单独的日期和时间值组合在一起成为一个单独的DATETIME数据类型值，很简单.

It's just a straightforward matter of combining the separate date and time values together into a single value of DATETIME datatype.

我们需要做的就是在适当的转换上方将其替换为查询，例如

All we need to do is substitute into our query above an appropriate conversion, e.g.

  st =>  CONCAT(pickup_date,' ',pickup_time) + INTERVAL 0 DAY
  et =>  CONCAT(return_date,' ',return_time) + INTERVAL 0 DAY

与b1和e1相同.

对于最终决定将日期和时间存储为单独的列的人来说，这是最后一个查询所要做的替换工作.

Doing that substitution, coming up with the final query, is left as an exercise for whoever decided that storing date and time as separate columns was a good idea.

这篇关于如何在MySQL中存储日期和时间范围而不会重叠的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！