计算特定用户在mysql中的总登出时间 [英] calculating total login-logout time of a particular user in mysql

问题描述

mysql>从时间选择*;

mysql> select * from timing;

+--------------+---------------------+-----------------+
| employeeIdNo | employeeLogTime     | employeeLogType |
+--------------+---------------------+-----------------+
|            1 | 2011-08-16 09:53:29 | login           |
|            1 | 2011-08-16 10:45:42 | logout          |
|            1 | 2011-08-16 10:55:29 | login           |
|            1 | 2011-08-16 17:55:39 | logout          |
+--------------+---------------------+-----------------+

我想使用上述数据显示员工的总工作时间.所以我想要一个mysql查询来计算employeeIdNo = 1的总时间.我的表名称是计时，而employeeIdNo是表employee的外键引用.计算后，它应该返回给我totalLogTime值= 7:52:23.请向我提供适当的查询.预先感谢.

i want to display the total working time of employee using above data. so i want a mysql query to calculate the total time of employeeIdNo=1. my table name is timing and employeeIdNo is the foreign key references from table employee. after calculation it should return me the totalLogTime value = 7:52:23. please provide me with appropriate query. thanks in advance.

推荐答案

a'r的建议很有意义.同样，注销时计算每个会话的持续时间也是一种更好的方法-尽管没有严格规范化该方法，但它分散了计算的负担.

a'r's recommendation makes a lot of sense. Also calculating the duration for each session at logout is a much better approach - although this is not strictly normalized, it spreads the load of calculating....

SELECT ilv.employee, 
SUM(UNIX_TIMESTAMP(logouttime) 
  - IF(logintime IS NULL, 
        UNIX_TIMESTAMP(logouttime), 
        UNIX_TIMESTAMP(logintime)))
FROM (
    SELECT a.employeeId, a.time AS logouttime,
    (SELECT MAX(b.time)
      FROM log b
      WHERE b.employeeId=a.employeeId
      AND b.time<a.time
      AND b.type='login')  as logintime
    FROM log a 
    WHERE a.type='logout'
    AND a.time BETWEEN <range start> AND <range end>
    AND employeeId=1 /* optional */
) ilv
GROUP BY ilv.employeeId

这篇关于计算特定用户在mysql中的总登出时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！