统计在线时长

------表结构------
CREATE TABLE usr_online_log (
id int NOT NULL AUTO_INCREMENT COMMENT '自增id',
usr_id int NOT NULL COMMENT '用户id',
is_online tinyint DEFAULT '1' COMMENT '0-下线，1-上线',
creat_time datetime DEFAULT NULL COMMENT '创建时间',
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户登录在线记录表';

-----sql语句--------
SELECT
usr_id,
DATE_FORMAT(DATE_ADD(
'1900-01-01 00:00:00',
interval sum(interrval_time) second
),'%T') AS total_interval,
creat_time date
FROM
(
SELECT
t1.usr_id,
LEFT (t1.creat_time, 10) AS creat_time,
TIMESTAMPDIFF(SECOND,t1.creat_time,t2.creat_time) AS interrval_time
FROM
usr_online_log t1
INNER JOIN usr_online_log t2 ON t1.usr_id = t2.usr_id
AND t1.is_online = 1
AND t2.is_online = 0
AND t1.creat_time < t2.creat_time
AND t2.id = (
SELECT min(id) FROM usr_online_log
WHERE usr_id = t1.usr_id
AND is_online = 0
AND id > t1.id
)
) a
GROUP BY
usr_id,
creat_time