MySQL 时间区间判断是否重复

Question

如何判断时间区间是否重复
开始时间:2020-05-12 AM
结束时间:2020-05-12 PM

SELECT
    request.LEAVE_ID leaveId 
FROM
    T_VACATION_REQUEST_LEAVE request 
WHERE
    request.is_valid = 'Y' 
    AND request.state != '99' 
    AND request.LEAVE_ID != '9bc428ef-c210-4732-ada0-f7f5d48b8d81' 
    AND request.PROPOSER_ID = '110' 
    AND (
        (
            ( DATE_FORMAT( request.LEAVE_START_TIME, '%Y-%m-%d' ) >= DATE_FORMAT( '2020-06-03T00:00:00.000+0800', '%Y-%m-%d' ) AND request.LEAVE_START_CHRONO >= 'AM' ) 
            AND ( DATE_FORMAT( request.LEAVE_START_TIME, '%Y-%m-%d' ) <= DATE_FORMAT( '2020-06-03T00:00:00.000+0800', '%Y-%m-%d' ) AND request.LEAVE_START_CHRONO <= 'AM' ) 
        ) 
        OR (
            ( DATE_FORMAT( request.LEAVE_START_TIME, '%Y-%m-%d' ) <= DATE_FORMAT( '2020-06-03T00:00:00.000+0800', '%Y-%m-%d' ) AND request.LEAVE_START_CHRONO <= 'AM' ) 
            AND ( DATE_FORMAT( request.LEAVE_END_TIME, '%Y-%m-%d' ) >= DATE_FORMAT( '2020-06-03T00:00:00.000+0800', '%Y-%m-%d' ) AND request.LEAVE_END_CHRONO >= 'PM' ) 
        ) 
        OR (
            ( DATE_FORMAT( request.LEAVE_END_TIME, '%Y-%m-%d' ) >= DATE_FORMAT( '2020-06-03T00:00:00.000+0800', '%Y-%m-%d' ) AND 'PM' >= request.LEAVE_START_CHRONO ) 
            AND ( DATE_FORMAT( request.LEAVE_END_TIME, '%Y-%m-%d' ) <= DATE_FORMAT( '2020-06-03T00:00:00.000+0800', '%Y-%m-%d' ) AND 'PM' <= request.LEAVE_END_CHRONO ) 
        ) 
    )
    

Answer 1

问题描述有点不清晰，如果是求两个区间是否有交集，已知区间[a_start, a_end]和[b_start, b_end]可以简化判断为：

max(a_start, b_start) <= min(a_end, b_end)

这段不难理解，可以画个图把三种情况都试试，希望能帮助到你。