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

字数970 大约花费5分钟

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
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 点),单位为秒。测试用例没有覆盖所有情况,有兴趣的同学帮忙补一补。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
-- 同一天,两段时间相交,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'))
)

谈谈 IT 的文章均为原创或翻译(翻译会注明外文来源),转载请以链接形式标明本文地址: http://tantanit.com/shi-yong-mysql-han-shu-ji-suan-yi-duan-shi-jian-nei-de-you-xiao-shi-chang/

谈谈IT

欢迎关注官方微信公众号获取最新原创文章