MySQL: how to get the difference between two times

Question

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

Answer 1

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 2

UNIX_TIMESTAMP(ts1) - UNIX_TIMESTAMP(ts2)

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

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

Answer 3

You could use the TIMEDIFF() and the 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 could also use the UNIX_TIMESTAMP() function as @Amber suggested in an other 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 type, I guess that the UNIX_TIMESTAMP() solution would be slightly faster, since TIMESTAMP values are already stored as an integer representing the number of seconds since the epoch (Source). Quoting the docs:

When UNIX_TIMESTAMP() is used on a TIMESTAMP column, the function returns the internal timestamp value directly, with no implicit “string-to-Unix-timestamp” conversion.

Keep in mind that TIMEDIFF() return data type of TIME. TIME values may range from '-838:59:59' to '838:59:59' (roughly 34.96 days)

Answer 4

Note that the TIMEDIFF() solution only works when the datetimes are less than 35 days apart! TIMEDIFF() returns a TIME datatype, and the max value for TIME is 838:59:59 hours (=34,96 days)