MySQL: how to get the difference between two timestamps in seconds

Question

Is there a way to make a query in MySQL that will give me the difference between two timestamps in seconds, or will I need to do this in PHP? And if so, how would I do it?

Answer 1

You can use TIMEDIFF() and TIME_TO_SEC() functions as follows:

 SELECT TIME_TO_SEC(TIMEDIFF('2010-08-20 12:01:00', '2010-08-20 12:00:00')) diff; +------+ | diff | +------+ | 60 | +------+ 1 row in set (0.00 sec) 

You can also use the UNIX_TIMESTAMP() function as @Amber suggested in another answer:

 SELECT UNIX_TIMESTAMP('2010-08-20 12:01:00') - UNIX_TIMESTAMP('2010-08-20 12:00:00') diff; +------+ | diff | +------+ | 60 | +------+ 1 row in set (0.00 sec) 

If you are using the TIMESTAMP data TIMESTAMP , I think the UNIX_TIMESTAMP() solution will be a little faster, since the TIMESTAMP values ​​are already stored as an integer representing the number of seconds since ( Source ). Quoting docs :

When UNIX_TIMESTAMP() used in the TIMESTAMP column, the function returns the value of the internal timestamp directly, without any implicit conversion of "string-to-Unix-timestamp".

Keep in mind that TIMEDIFF() returns a TIME data type . TIME values ​​can vary from '-838: 59: 59' to '838: 59: 59' (approximately 34.96 days)

Answer 2

How about "TIMESTAMPDIFF":

 SELECT TIMESTAMPDIFF(SECOND,'2009-05-18','2009-07-29') from 'post_statistics' 

https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_timestampdiff

Answer 3

 UNIX_TIMESTAMP(ts1) - UNIX_TIMESTAMP(ts2) 

If you want unsigned difference, add ABS() around the expression.

Alternatively, you can use TIMEDIFF(ts1, ts2) and then convert the time result in seconds using TIME_TO_SEC() .

Answer 4

Note that the TIMEDIFF() solution TIMEDIFF() works if datetimes less than 35 days ! TIMEDIFF() returns the data type TIME , and the max value for TIME is 838: 59: 59 hours (= 34.96 days)