MySQL TIMESTAMPDIFF function is one of the Date methods, which is useful to find the interval difference between two dates or DateTime expressions.
Let us see how to use this TIMESTAMPDIFF function to find the difference in Year, Months, Days, Hours, etc., with an example. The basic syntax of the TIMESTAMPDIFF Function is as shown below:
TIMESTAMPDIFF(Unit, DateTime_expression1, DateTime_expression2);
MySQL TIMESTAMPDIFF function Example
The below-shown queries help you understand the use of this TIMESTAMP DIFF method.
Here, First, we are finding the Minutes difference between 2018-12-31 and 2019-03-22 23:15:59. Next, the difference in months between the two dates. Within the third statement, we are finding the total number of years between two dates.
TIP: I suggest you refer to the methods article to understand the Units after the Interval in MySQL.
SELECT TIMESTAMPDIFF(MINUTE, '2018-12-31', '2019-03-22 23:15:59');
SELECT TIMESTAMPDIFF(MONTH, '2018-12-31', '2019-03-22');
SELECT TIMESTAMPDIFF(YEAR, '2012-12-31', '2019-03-22');
In this TIMESTAMPDIFF Function example, we are finding the difference in Days, Weeks, and Hours.
SELECT TIMESTAMPDIFF(DAY, '2012-12-31', '2019-03-22');
SELECT TIMESTAMPDIFF(WEEK, '2018-12-31', '2019-03-22');
SELECT TIMESTAMPDIFF(HOUR, '2018-12-31', '2019-03-22 12:05:22');
This example shows you how to use this Timestamp diff function on table data. Here, we are finding the difference in the number of years and months between the Today and HireDate column.
TIMESTAMPDIFF(YEAR, HireDate, NOW()) AS 'Diff 1',
TIMESTAMPDIFF(MONTH, NOW(), Hiredate) AS 'Diff2'
FROM `MySQL Tutorial`.customer;