Difference between two dates in MySQL

mysql sql date datetime
GeoGo picture GeoGo · Jan 21, 2011 · Viewed 276.7k times · Source

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?

Answer

Devid G picture Devid G · Jan 21, 2011 
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.

Related questions

MySQL Query GROUP BY day / month / year

Is it possible to make a simple query to count how many records I have in a determined period of time like a year, month, or day, having a TIMESTAMP field, like: SELECT COUNT(id) FROM stats WHERE record_date.…

Read More
Error in MySQL when setting default value for DATE or DATETIME

I'm running MySql Server 5.7.11 and this sentence: updated datetime NOT NULL DEFAULT '0000-00-00 00:00:00' is not working. Giving the error: ERROR 1067 (42000): Invalid default value for 'updated' But the following: updated datetime NOT NULL DEFAULT '1000-01-01 00:00:00' …

Read More
Get a list of dates between two dates

Using standard mysql functions is there a way to write a query that will return a list of days between two dates. eg given 2009-01-01 and 2009-01-13 it would return a one column table with the values: 2009-01…

Read More