Calculate difference between two datetimes in MySQL

mysql
Devesh Agrawal picture Devesh Agrawal · Jun 6, 2012 · Viewed 214.1k times · Source

I am storing the last login time in MySQL in, datetime-type filed. When users logs in, I want to get the difference between the last login time and the current time (which I get using NOW()).

How can I calculate it?

Answer

FSP picture FSP · Jun 6, 2012

USE TIMESTAMPDIFF MySQL function. For example, you can use:

SELECT TIMESTAMPDIFF(SECOND, '2012-06-06 13:13:55', '2012-06-06 15:20:18')

In your case, the third parameter of TIMSTAMPDIFF function would be the current login time (NOW()). Second parameter would be the last login time, which is already in the database.

Related questions

How do I import an SQL file using the command line in MySQL?

I have a .sql file with an export from phpMyAdmin. I want to import it into a different server using the command line. I have a Windows Server 2008 R2 installation. I placed the .sql file on the C drive, and …

Read More
How can I prevent SQL injection in PHP?

If user input is inserted without modification into an SQL query, then the application becomes vulnerable to SQL injection, like in the following example: $unsafe_variable = $_POST['user_input']; mysql_query("INSERT INTO `table` (`column`) VALUES ('$unsafe_variable')"); That's …

Read More
MySQL error code: 1175 during UPDATE in MySQL Workbench

I'm trying to update the column visited to give it the value 1. I use MySQL workbench, and I'm writing the statement in the SQL editor from inside the workbench. I'm writing the following command: UPDATE tablename SET columnname=1; It gives …

Read More