Difference between two dates in MySQL
SELECT TIMEDIFF('2007-12-31 10:02:00','2007-12-30 12:01:01');-- result: 22:00:59, the difference in HH:MM:SS formatSELECT 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
Get the date difference in days using DATEDIFF
SELECT DATEDIFF('2010-10-08 18:23:13', '2010-09-21 21:40:36') AS days;+------+| days |+------+| 17 |+------+
OR
Refer the below linkMySql difference between two timestamps in days?