How to query the intersection of time periods in Mysql

Mysql query time period intersection

Usage scenarios

The database table has two fields starttime and endtime. Now given the time period (a, b), find the data that intersects with the time period (starttime, endtime).

sql

select * from TABLENAME where  
    (starttime > a AND starttime < b) OR 
    (starttime < a AND endtime > b) OR
    (endtime > a AND endtime < b) OR
    (starttime = a AND endtime = b);

Mysql query whether two time periods intersect

Database fields start_time, end_time

Input fields a,b

The first

SELECT * FROM test_table
WHERE
    (start_time >= a AND start_time <= b)
    OR (start_time <= a AND end_time >= b)
    OR (end_time >= a AND end_time <= b)

The second

SELECT * FROM test_table
WHERE
    NOT (
        (end_time < a
        OR (start_time > b)
    )

Both results are the same.

The above is my personal experience. I hope it can give you a reference. I also hope that you will support 123WORDPRESS.COM.