Mysql - Query using Between to know if item is reserved on date/time

Question

I would like to know if the item is booked on an X date in my BD. For example:

I have the following bank: I want to book the item with Resid = 5 on the date of 02/26/2019 from 14h30 to 16h30. But since he’s booked from 2:00 to 4:00, he couldn’t register the reservation.

I am trying to use BETWEEN, but returns the query blank. But I should return the id 19, since there is a reservation between these dates.

SELECT * FROM intranet_reuniao 
WHERE resId = 5 
AND (reuDataInicio BETWEEN '2019-02-26 14:30:00' AND '2019-02-26 16:30:00')

What logic should be used in this case?

Answer 1

It’s not enough you add the clause OR? Staying that way:

SELECT * 
  FROM intranet_reuniao 
 WHERE resId = 5 
   AND ((reuDataInicio BETWEEN '2019-02-26 14:30:00' AND '2019-02-26 16:30:00')OR
        (reuDataTermino BETWEEN '2019-02-26 14:30:00' AND '2019-02-26 16:30:00'))

Therefore, if you return any record it is because there is already a reservation with start or end between the Start and Finish dates.

Answer 2

 SELECT * FROM intranet_reuniao 
  WHERE resId = 5 
  AND '2019-02-26 14:30:00' > reuDataInicio AND reuDataTermino > '2019-02-26 14:30:00';

I didn’t get to test if the value before the > would work, but the logic would be that if your date you’re trying to mark is higher than the start of what is already, and if the one you’re scheduled to end after what you want to schedule, you can’t.

Answer 3

I understand you want to check if there are any reservations at the specified time. There are 4 hypotheses for such a condition to be true.

1. There is a reservation prior to start date of the specification and precedes the end date of the specification, i.e., hereafter of the specifications.
2. There is a reservation prior to start date of the specification and before the end date of the specification, i.e., amid of the specifications.
3. There is a reservation prior to start date of the specification and precedes the start date of the specification, i.e., ends among the specification.
4. There is a reservation prior to end date of the specification and precedes the end date of the specification, i.e., begins among the specification.

Seen that the where condition would be as follows (among many others):

SELECT * 
  FROM intranet_reuniao 
    WHERE resId = 5 
      AND (  (    reuDataInicio >= '2019-02-26 14:30:00' 
              AND reuDataInicio <= '2019-02-26 16:30:00')
           OR
             (    reuDataTermino >= '2019-02-26 14:30:00' 
              AND reuDataTermino <= '2019-02-26 16:30:00')
           OR
             (     reuDataInicio <= '2019-02-26 14:30:00' 
              AND reuDataTermino >= '2019-02-26 16:30:00')         
          )
;

Thus, the specification it has starting value before of reuDataInicio and end value hereafter of reuDataInicio, fits condition 1 and 3 of the above list. The other condition is that if the specification has starting value before of reuDataTermine and end value hereafter of reuDataTermine, fits condition 1 and 4 of the above list. The last condition refers to condition 2 of the above list.

This will bring the reservations that exist between the specified time.