MySQL - Check if 2 start and end parameter is not between start and end in database record-CodePudding

I have this db design:

Tasks table
- id
- start (datetime)
- end (datetime)

In my application, I passed 2 start and end date parameters so that it will check if these parameters are not between those start and end in my db record.

Example, in my DB record I have the following data:

id  |       start         |        end
001 | 2022-01-19 07:00:00 | 2022-01-19 09:00:00

Then the parameter that I passed into my application are:

start = 2022-01-19 09:00:00 
end = 2022-01-19 10:00:00

I want to check if these start and end time parameters are between those records in my DB. Ideally, this should return 0 rows because the start time can be equal to the end time in the record in the DB.

Another scenario is that when I passed these parameters to my application:

start = 2022-01-19 06:00:00
end = 2022-01-19 07:00:00

It should also return 0 rows because the end time can be equal to the start time in the record in the DB.

This is mySQL query, but this is wrong because it returns a result:

select * from tasks WHERE (start >= '2022-01-19 09:00:00' AND start <= '2022-01-19 10:00:00') OR (end >= '2022-01-19 09:00:00' AND end <= '2022-01-19 10:00:00')

Your help is greatly appreciated. Thanks!

CodePudding user response：

It seems that you wish to detect collisions between intervals and return a final count if any with the passed parameters.

So, we would be checking if any event occurs after the passed in params' event has ended or if any event only occurs and ends before the passed in params' event starts. This ensures no collision. We will negate(!) the result to say there is a collision(if any).

You can follow the below format:

select * 
from tasks 
WHERE !(start >= {end_parameter_time} OR end <= {start_parameter_time})

More precisely for your case/example, it would be:

select * 
from tasks 
WHERE !(start >= '2022-01-19 07:00:00' OR end <= '2022-01-19 06:00:00')

Online Demo

CodePudding user response：

The query to test if date range [@d1, @d2) overlaps date range [start, end) is as follows:

SELECT *
FROM tasks
WHERE @d2 > start
AND   @d1 < end

CodePudding user response：

Kumusta kapwa pinoy! You can use BETWEEN to check if the data you are passing is between the start and end. You can modify your query like this:

select * from tasks WHERE (start BETWEEN '2022-01-19 09:00:00' AND '2022-01-19 10:00:00') OR (end BETWEEN '2022-01-19 09:00:00' AND '2022-01-19 10:00:00')

You may check Sql BETWEEN Operator

The BETWEEN operator selects values within a given range. The values can be numbers, text, or dates.

The BETWEEN operator is inclusive: begin and end values are included.

BETWEEN SYNTAX

SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2;