Difference between two dates in MySQL

How to calculate the difference between two dates, in the format YYYY-MM-DD hh: mm: ss and to get the result in seconds or milliseconds?

SELECT TIMEDIFF('2007-12-31 10:02:00','2007-12-30 12:01:01');
-- result: 22:00:59, the difference in HH:MM:SS format


SELECT TIMESTAMPDIFF(SECOND,'2007-12-30 12:01:01','2007-12-31 10:02:00'); 
-- result: 79259  the difference in seconds

So, you can use TIMESTAMPDIFF for your purpose.

If you are working with DATE columns (or can cast them as date columns), try DATEDIFF() and then multiply by 24 hours, 60 min, 60 secs (since DATEDIFF returns diff in days). From MySQL:

http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html

for example:

mysql> SELECT DATEDIFF('2007-12-31 23:59:59','2007-12-30 00:00:00') * 24*60*60