mysql - 登录时间和注销时间的总时间差-MYSQL

Question

输入

 userid           login time                               logout time
----------------------------------------------------------------------------
   1          2012-08-01 16:08:26                     2012-08-01 16:08:29
   2          2012-08-01 16:22:49                     2012-08-01 16:25:44
   3          2012-08-01 16:08:26                     2012-08-01 16:08:29
   3          2012-08-01 16:22:49                     2012-08-01 16:25:44
   3          2012-08-01 16:08:26                     2012-08-01 16:08:29
   4          2012-08-01 16:22:49                     2012-08-01 16:25:44

输出:

 userid        date         total time difference b/w login time and logout time
---------------------------------------------------------------------------------
   1        2012-08-01                         00:08:29
   2        2012-08-01                         1:25:44
   3        2012-08-01                         00;55;5
   4        2012-08-01                         1:25:44

我试过的查询是:

SELECT distinct t.user_id, DATE_FORMAT(t.login_time,\'%d %b %Y\') AS datez,
       SEC_TO_TIME(SUM(TIME_TO_SEC(t.logout_time) - TIME_TO_SEC(t.login_time))) AS timediffe
from login_log t 
where user_id=5 
  AND login_time between '2012-08-01' AND '2012-08-2' 
GROUP BY t.user_id,datez

Answer 1

我想你只需要使用 TIMEDIFF用作:

SELECT t.user_id,
       DATE_FORMAT(t.login_time,'%d %b %Y') AS datez,
       SEC_TO_TIME(SUM(TIME_TO_SEC(TIMEDIFF(t.logout_time, t.login_time)))) AS timediffe
FROM tic_login_log t
WHERE user_id = 5
      AND login_time between '2012-08-01' AND '2012-08-2'
GROUP BY t.user_id,datez;