calculate total login logout time in mysql

Question

mysql> select * from timing;

+--------------+---------------------+-----------------+
| employeeIdNo | employeeLogTime     | employeeLogType |
+--------------+---------------------+-----------------+
|            1 | 2011-08-16 14:59:08 | login           |
|            1 | 2011-08-16 15:00:06 | logout          |
|            1 | 2011-08-16 15:14:51 | login           |
|            2 | 2011-08-16 15:15:00 | login           |
|            1 | 2011-08-16 15:59:01 | logout          |
|            2 | 2011-08-16 15:59:09 | logout          |
+--------------+---------------------+-----------------+

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(say) 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 = 00:45:52(this is approx. value, i didnt calculated it exactly). please provide me with appropriate query. thanks in advance.

i tried this query.:-

mysql> SELECT 
  TIMEDIFF(FIRSTTIME.employeeLogTime,SECONDTIME.employeeLogTime) 
  FROM (
    (SELECT * FROM timing HAVING employeeLogTime = max(employeeLogTime)) as FIRSTTIME,     
    (SELECT * FROM timing ORDER BY employeeLogTime LIMIT 1,1) as SECONDTIME);
Empty set (0.01 sec)

its giving me empty set as shown above.

Answer 1

Couldn't you sum all times for a single user :

• for login (in milliseconds) call this A
• for logout (in milliseconds) call this B

and return B - A ;

It's a bit ackward as a way to see it but gives much simpler sql queries. You also need to check that login and logouts have same number of occurences with this method.

Answer 2

select employeeIdNo,
sum(loginTimeInSec) as loginTimeInSec
from(
    select 
        e1.employeeIdNo, 
        TIME_TO_SEC(TIMEDIFF(e2.employeeLogTime,e1.employeeLogTime)) as loginTimeInSec
    from 
       timing e1 join timing e2
       on e1.employeeIdNo = e2.employeeIdNo
       and e1.employeeLogType = 'login'
       and e2.employeeLogType = 'logout'
       and e1.employeeLogTime < e2.employeeLogTime
       group by e1.id having min(e2.employeeLogTime - e1.employeeLogTime)
) temp
group by employeeIdNo;