I have a log table with a TIMESTAMP column that records the user's login and logout time. I need a select that compares the user login and logout time to calculate the time that the user logged in.
It sounds simple, but I'm not making progress.
Have you ever tried using TIMESTAMPDIFF (unit, datetime1, datetime2)? It returns the subtraction of datetime2-datetime1 and the unit can be SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or YEAR
follows syntax:
SELECT TIMESTAMPDIFF(MONTH,'2009-05-18','2009-07-29');
will return 2
SELECT TIMESTAMPDIFF(MINUTE,'2003-02-01','2003-05-01 12:05:55');
will return 128885, or in your case
SELECT TIMESTAMPDIFF(MINUTE, t.login, t.logout)
from tabela t
where user_id = id_do_usuario;
This will return in minutes the time it is only dealt with or would give to return already in the query too:
SELECT CONCAT(FLOOR(TIMESTAMPDIFF(MINUTE, t.login, t.logout) /60), 'hr', MOD(TIMESTAMPDIFF(MINUTE, t.login, t.logout), 60),'m') as tempo_logado
from tabela t where user_id = id_do_usuario;
Reference: MySQL 5.5 Reference Manual / Functions and Operators / Date and Time Functions
I hope I have helped.