先说明数据库表:
id
simnumber 卡号
locationTime 记录时间
time与上一次记录时间相差的秒数
我想计算记录时间总和
查询一个时间段
SELECT
SimNumber,
SUM(Time)
FROM
jsjk_location
WHERE
1=1
AND LocationTime>='2019-01-08'
AND LocationTime<='2019-01-11'
AND DATE_FORMAT(LocationTime, '%H:%i:%S') >= '09:00:00'
AND DATE_FORMAT(LocationTime, '%H:%i:%S') <= '16:00:00'
GROUP BY SimNumber
2.查询一段时间内每段时间的总和
select
SimNumber,
SUM(CASE WHEN HOUR(LocationTime) BETWEEN 6 AND 7 THEN Time ELSE 0 END) AS '清晨',
SUM(CASE WHEN HOUR(LocationTime) BETWEEN 12 AND 13 THEN Time ELSE 0 END) AS '中午',
SUM(CASE WHEN HOUR(LocationTime) BETWEEN 18 AND 19 THEN Time ELSE 0 END) AS '黄昏',
SUM(CASE WHEN HOUR(LocationTime)