CODEWITHSUNDEEP
mysqlsql
mithun3010
mithun3010

Reputation: 41

MySql query to find records NOT between time range

I am working on a project where the from and to time are given and I have to find out if any other vehicles are allocated on that time period.

For EG: Vehicle 1 is allocated FROM 2017-04-29 10:00:00 TO 2017-04-29 12:00:00 in the case table.

My use case: I should not be able to allocate the same vehicle in that time range.

So, How would I check if a vehicle is allocated / clashes with the time range? (IN mySQL)

Here is where I am so far:

 SELECT 'vehicle_code' 
 FROM   'case' 
 WHERE  'from_time' <= '2017-04-29 11:00:00'
 AND    'to_time' >= '2017-04-29 13:00:00'

Upvotes: 0

Views: 1308

Answers (2)

Manuel Schmidt
Manuel Schmidt

Reputation: 2417

I guess the specific vehicle with %VEHICLE_CODE% you are trying to allocate cannot be already allocated within that time range, so you are not interested in any vehicle but in a specific one.

You have an overlap if at least of this three things occurs:

  1. The from_time of an allocation falls within your requested period
  2. The to_time of an allocation falls within your requested period
  3. The whole allocation falls into your requested period

Then you can use this query

SELECT count(*) 
FROM `case`  
WHERE vehicle_code = %VEHICLE_CODE% 
AND (from_time between '2017-04-29 11:00:00' AND '2017-04-29 13:00:00'
OR to_time BETWEEN '2017-04-29 11:00:00' AND '2017-04-29 13:00:00'
OR from_time < '2017-04-29 11:00:00' AND to_time > '2017-04-29 13:00:00');

With this query you ensure there isn't any allocation for this vehicle where the from_time or the to_time fall into the queried period or the whole period does.

If the query returns 0 you don't have an overlap. If it returns more than 0, you do have such an overlap.

Similarly to what @dnoeth told you, you could also exclude all entries where, either the to_time is less than the start of the queried interval or the from_time is greater than the end of queried interval. If any entry remains, this is an overlap. So you would have

SELECT count(*) 
FROM `case`  
WHERE vehicle_code = %VEHICLE_CODE% 
AND NOT (from_time > '2017-04-29 13:00:00'
OR to_time < '2017-04-29 11:00:00');

Upvotes: 1

Darshan Mehta
Darshan Mehta

Reputation: 30809

You need to check whether either of these times fall into time range of any other vehicle, e.g.:

SELECT vehicle_code
FROM `case`
WHERE from_time >= '2017-04-29 11:00:00' AND to_time <= '2017-04-29 11:00:00'

UNION

SELECT vehicle_code
FROM `case`
WHERE from_time >= '2017-04-29 13:00:00' AND to_time <= '2017-04-29 13:00:00'

If this query returns any row, it means another vehicle is already booked and hence, you need to show appropriate error message.

Upvotes: 0

Related Questions