MySQL TIMESTAMPDIFF Function
Summary: in this tutorial, you will learn how to use the MySQL TIMESTAMPDIFF function to calculate the difference between two DATE or DATETIME values.
Introduction to MySQL TIMESTAMPDIFF function
The following illustrates the syntax of the TIMESTAMPDIFF function.
TIMESTAMPDIFF(unit,begin,end);
Code language: SQL (Structured Query Language) (sql)
The TIMESTAMPDIFF function returns the result of begin - end, where begin and end are DATE or DATETIME expressions.
The TIMESTAMPDIFF function allows its arguments to have mixed types e.g., begin is a DATE value and end is a DATETIME value. In case you use a DATE value, the TIMESTAMPDIFF function treats it as a DATETIME value whose time part is '00:00:00'.
The unit argument determines the unit of the result of (end - begin) represented as an integer. The following are valid units:
- MICROSECOND
- SECOND
- MINUTE
- HOUR
- DAY
- WEEK
- MONTH
- QUARTER
- YEAR
MySQL TIMESTAMPDIFF function examples
The following example returns a difference of 2010-01-01 and 2010-06-01 in months:
SELECT
TIMESTAMPDIFF(MONTH, '2010-01-01', '2010-06-01') result;| result |
+——–+
| 5 |
+——–+
1 row in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)
If you want to see the difference in days, you just need to change the unit argument from MONTH to DAY as follows:
SELECT
TIMESTAMPDIFF(DAY, '2010-01-01', '2010-06-01') result;
+--------+
| result |
+--------+
| 151 |
+--------+
1 row in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)
The following statement returns a difference of two DATETIME values in minutes:
SELECT
TIMESTAMPDIFF(MINUTE, '2010-01-01 10:00:00', '2010-01-01 10:45:00') result;
+--------+
| result |
+--------+
| 45 |
+--------+
1 row in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)
Note that the TIMESTAMPDIFF only considers the time part that is relevant to the unit argument. See the following example:
SELECT TIMESTAMPDIFF(MINUTE, '2010-01-01 10:00:00', '2010-01-01 10:45:59') result;
+--------+
| result |
+--------+
| 45 |
+--------+
1 row in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)
The difference should be 45 minutes 59 seconds. However, we pass the unit argument as MINUTE, therefore, the function returns 45 minutes as expected.
If you use SECOND instead of MINUTE, then the TIMESTAMPDIFF function will consider the SECOND part as shown in the following example:
SELECT
TIMESTAMPDIFF(SECOND, '2010-01-01 10:00:00', '2010-01-01 10:45:59') result;
+--------+
| result |
+--------+
| 2759 |
+--------+
1 row in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)
45 minutes 59 second = 45 x 60 + 59 (seconds) = 2759 seconds
Calculating ages using MySQL TIMESTAMPDIFF function
First, we create a new table named persons for the demonstration.
CREATE TABLE persons (
id INT AUTO_INCREMENT PRIMARY KEY,
full_name VARCHAR(255) NOT NULL,
date_of_birth DATE NOT NULL
);
Code language: SQL (Structured Query Language) (sql)
Second, we insert some rows into the persons table:
INSERT INTO persons(full_name, date_of_birth)
VALUES('John Doe', '1990-01-01'),
('David Taylor', '1989-06-06'),
('Peter Drucker', '1985-03-02'),
('Lily Smith', '1992-05-05'),
('Mary William', '1995-12-01');
Code language: SQL (Structured Query Language) (sql)
Third, we use the TIMESTAMPDIFF to calculate the ages of each person in the persons table:
SELECT
id,
full_name,
date_of_birth,
TIMESTAMPDIFF(YEAR,
date_of_birth,
'2017-01-01') age
FROM
persons;
Code language: SQL (Structured Query Language) (sql)
In this statement, we calculated the ages on January 1st 2017. If you want to calculate the current ages, you can replace the literal value 2017-01-01 by the NOW function as follows:
SELECT
id,
full_name,
date_of_birth,
TIMESTAMPDIFF(YEAR,
date_of_birth,
NOW()) age
FROM
persons;
Code language: SQL (Structured Query Language) (sql)
In this tutorial, you have learned how to use the MySQL TIMESTAMPDIFF function to calculate the difference between two DATE or DATETIME values.