Calculate date time in mysql

Asked

Viewed 949 times

-5

I have a log tab with a TIMESTAMP type column that saves the user’s login and logout time. I need a select compare the time of login and logout of the user to calculate the time that the same stayed connected.

It seems simple, but I’m not moving forward.

mysql select
  • 4

    You can show the select you’ve already made?

  • Nelson, good afternoon.

  • Nelson, the problem is that I have multiple user inputs and outputs during the day, I would need to align each t.login with your subsequent t.logout for the query to run and calculate the time difference between login and logout.

  • Dude what you could do is use the SUM(), along with those examples there and there is no clause Where you put one more and data = now() there is to solve

1 answer

5

Have you tried using TIMESTAMPDIFF(Unit, datetime1, datetime2)? It returns the subtraction of datetime2- datetime1 and Unit may 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 there is only treat or would give to return already in the query also:

 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’ve helped.

Browser other questions tagged mysql select

You are not signed in. Login or sign up in order to post.