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.

EDIT (as proposed by spencer7593, this is the working version so far):

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

, so this query will result:

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.

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

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

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...

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

To find rows with an overlap with a proposed period bounded by b1 and 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))

That's pretty simple.

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).

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

Same for b1 and 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.