一、目的

判断两个时间范围是否有交叉。

二、思路

固定一个时间范围的两边，找另一个时间范围与两边的关系。可以发现4种可能情况：

MySql时间段交叉函数_sql

三、函数

1定义和用法

判断两个时间断是否有交叉,如果有则返回1,否则返回0

GetDateCross(fromDate,toDate,startDate,endDate)

2函数脚本

DROP FUNCTION
IF
  EXISTS GetDateCross;
CREATE FUNCTION GetDateCross ( fromDate DATE, toDate DATE, startDate DATE, endDate DATE ) RETURNS INT ( 11 ) BEGIN
  DECLARE
    rs INT;
  
  SET rs = 0;
  IF
    ( fromDate <= endDate AND fromDate >= startDate ) THEN
      
      SET rs = 1;
    
    ELSEIF ( toDate <= endDate AND toDate >= startDate ) THEN
    
    SET rs = 1;
    
    ELSEIF ( fromDate >= startDate AND toDate <= endDate ) THEN
    
    SET rs = 1;
    
    ELSEIF ( fromDate <= startDate AND toDate >= endDate ) THEN
    
    SET rs = 1;
    
  END IF;
  RETURN rs;

END

3测试

select GetDateCross('2022-05-11','2022-05-17','2022-05-09','2022-05-10') 
union all
select GetDateCross('2022-05-11','2022-05-17','2022-04-17','2022-05-11') 
union all
select GetDateCross('2022-05-11','2022-05-17','2022-05-17','2022-05-19') 
union all
select GetDateCross('2022-05-11','2022-05-17','2022-05-09','2022-05-19') 
union all
select GetDateCross('2022-05-11','2022-05-17','2022-05-12','2022-05-14') 
union all
select GetDateCross('2022-05-11','2022-05-17','2022-05-18','2022-05-19')

 MySql时间段交叉函数_mysql_02