使用MySQL函数计算一段时间内的有效时长

有时候需要计算两个时间点之间的有效时间的长度，比如某天上午几点到某天下午几点之间，上班时间的长度，可以使用下面的函数进行计算。

CREATE FUNCTION `get_eff_time_length`(datetime_start datetime,datetime_end datetime,eff_time_start TIME,eff_time_end TIME) RETURNS int(11)
BEGIN
DECLARE dayCount INTEGER;
DECLARE effMiddleSeconds INTEGER;
DECLARE effSecondsFirst INTEGER;
DECLARE effSecondsLast INTEGER;
DECLARE time_start TIME;
DECLARE time_end TIME;
DECLARE result INTEGER;
SET dayCount =  DATEDIFF(datetime_end,datetime_start);
SET time_start=TIME(datetime_start);
SET time_end=TIME(datetime_end);
IF dayCount=0
THEN
	IF eff_time_start>time_start
	THEN
		SET time_start=eff_time_start;
	END IF;
 	IF eff_time_end<time_end
	THEN
		SET time_end=eff_time_end;
	END IF;
	IF time_end<	time_start
	THEN
		SET result=0;
	ELSE
		SET result=TIME_TO_SEC(SUBTIME(time_end,time_start));
	END IF;
ELSE
	SET effMiddleSeconds=(dayCount-1)*TIME_TO_SEC(TIMEDIFF(eff_time_end,eff_time_start));
	IF time_start>eff_time_end THEN
		SET effSecondsFirst=0;
	ELSE
 		IF eff_time_start>time_start
		THEN
		SET time_start=eff_time_start;
		END IF;
		SET effSecondsFirst=TIME_TO_SEC(SUBTIME(eff_time_end,time_start));
	END IF;

	IF time_end<eff_time_start THEN
		SET effSecondsLast=0;
	ELSE
 		IF eff_time_end<time_end
		THEN
		SET time_end=eff_time_end;
		END IF;
		SET effSecondsLast=TIME_TO_SEC(SUBTIME(time_end,eff_time_start));
	END IF;
	SET result=effSecondsFirst+effMiddleSeconds+effSecondsLast;
END IF;

 RETURN result;
END

下面的测试用例计算一段时间内，在9点到21点之间的有效时长（取个对称的时间，方便测试。已经工作了的程序员留个言，看看有没有人经常加班到21点），单位为秒。测试用例没有覆盖所有情况，有兴趣的同学帮忙补一补。

-- 同一天，两段时间相交，11.5小时，41400秒
SELECT get_eff_time_length(STR_TO_DATE('2015-12-21,09:30:00','%Y-%m-%d,%H:%i:%s')
,STR_TO_DATE('2015-12-21,21:30:30','%Y-%m-%d,%H:%i:%s')
, TIME(STR_TO_DATE('09:00:00','%H:%i:%s'))
,  TIME(STR_TO_DATE('21:00:00','%H:%i:%s'))
)

-- 同一天，两段时间不相交，0秒
SELECT get_eff_time_length(STR_TO_DATE('2015-12-21,08:30:00','%Y-%m-%d,%H:%i:%s')
,STR_TO_DATE('2015-12-21,09:00:00','%Y-%m-%d,%H:%i:%s')
, TIME(STR_TO_DATE('09:00:00','%H:%i:%s'))
,  TIME(STR_TO_DATE('21:00:00','%H:%i:%s'))
)

-- 同一天，两段时间不相交，0秒
SELECT get_eff_time_length(STR_TO_DATE('2015-12-21,21:30:00','%Y-%m-%d,%H:%i:%s')
,STR_TO_DATE('2015-12-21,22:00:00','%Y-%m-%d,%H:%i:%s')
, TIME(STR_TO_DATE('09:00:00','%H:%i:%s'))
,  TIME(STR_TO_DATE('21:00:00','%H:%i:%s'))
)

-- 跨天，都不在有效时间，0秒
SELECT get_eff_time_length(STR_TO_DATE('2015-12-21,21:30:00','%Y-%m-%d,%H:%i:%s')
,STR_TO_DATE('2015-12-22,8:30:00','%Y-%m-%d,%H:%i:%s')
, TIME(STR_TO_DATE('09:00:00','%H:%i:%s'))
,  TIME(STR_TO_DATE('21:00:00','%H:%i:%s'))
)

-- 跨天，第一天包含全部有效时间，第二天不在有效时间，12小时，43200秒
SELECT get_eff_time_length(STR_TO_DATE('2015-12-21,8:30:00','%Y-%m-%d,%H:%i:%s')
,STR_TO_DATE('2015-12-22,8:45:00','%Y-%m-%d,%H:%i:%s')
, TIME(STR_TO_DATE('09:00:00','%H:%i:%s'))
,  TIME(STR_TO_DATE('21:00:00','%H:%i:%s'))
)

-- 跨天，第二天包含全部有效时间，第一天不在有效时间，12小时，43200秒
SELECT get_eff_time_length(STR_TO_DATE('2015-12-21,22:00:00','%Y-%m-%d,%H:%i:%s')
,STR_TO_DATE('2015-12-22,21:30:00','%Y-%m-%d,%H:%i:%s')
, TIME(STR_TO_DATE('09:00:00','%H:%i:%s'))
,  TIME(STR_TO_DATE('21:00:00','%H:%i:%s'))
)

-- 跨天，两天都有部分在有效时间，1.5小时，5400秒
SELECT get_eff_time_length(STR_TO_DATE('2015-12-21,20:00:00','%Y-%m-%d,%H:%i:%s')
,STR_TO_DATE('2015-12-22,9:30:00','%Y-%m-%d,%H:%i:%s')
, TIME(STR_TO_DATE('09:00:00','%H:%i:%s'))
,  TIME(STR_TO_DATE('21:00:00','%H:%i:%s'))
)

-- 跨两天，第一天和第三天都有部分在有效时间，13.5小时，48600秒
SELECT get_eff_time_length(STR_TO_DATE('2015-12-21,20:00:00','%Y-%m-%d,%H:%i:%s')
,STR_TO_DATE('2015-12-23,9:30:00','%Y-%m-%d,%H:%i:%s')
, TIME(STR_TO_DATE('09:00:00','%H:%i:%s'))
,  TIME(STR_TO_DATE('21:00:00','%H:%i:%s'))
)