How to find difference between two dates in MySQL

Last updated on September 11th, 2020 at 11:36 am

Calculating difference between two dates in MySQL is really easy. Here’s how you can do it. Let’s say dt1 and dt2 are 2 columns in table table_name. Just replace db1, db2 and table_name in the queries below with your values.

A simple use case is a table called user with 2 fields called signup_date and last_login_date. You can calculate difference between two dates to find how recently users have logged in after signup.

Difference between two dates present as columns in a table

SELECT UNIX_TIMESTAMP( dt2 ) - UNIX_TIMESTAMP( dt1 ) from table_name;

Difference between two dates stored as variables

SET @dt1='2013-11-09 00:00:00';
SET @dt2='2012-10-07 00:00:00';
SELECT UNIX_TIMESTAMP( @dt2 ) - UNIX_TIMESTAMP( @dt1 );

Difference between two dates in minutes

If you want to find out the difference in terms on minutes, just divide the above result by 60

SELECT (UNIX_TIMESTAMP( dt2 ) - UNIX_TIMESTAMP( dt1 ))/60 from table_name;

Difference between two dates in hours

If you want to find out the difference in terms on minutes, just divide the above result by 3600

SELECT (UNIX_TIMESTAMP( dt2 ) - UNIX_TIMESTAMP( dt1 ))/3600 from table_name;

Difference between two dates in days

If you want to find out the difference in terms on minutes, just divide the above result by 3600*24

SELECT (UNIX_TIMESTAMP( dt2 ) - UNIX_TIMESTAMP( dt1 ))/(3600*24) from table_name;