MySQL TIMESTAMPDIFF Function

MySQL TIMESTAMPDIFF function is one of the MySQL Date Functions, which is useful to find the interval difference between two dates or DateTime expressions.

Let us see how to use this MySQL TIMESTAMP DIFF 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 1

The below shown queries help you understand the use of this MySQL TIMESTAMP DIFF function.

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 two dates. Within the third statement, we are finding the total number of years between two dates.

TIP: I suggest you refer to the Date Function 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');
MySQL TIMESTAMPDIFF Function 1

MySQL TIMESTAMPDIFF Function Example 2

In this 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');
MySQL TIMESTAMPDIFF Function 2

MySQL TIMESTAMPDIFF Example 3

In this example, we show you how to use this MySQL Timestamp diff function on a table data. Here, we are finding the difference in the number of Year and Months between Today and HireDate column.

SELECT EmpID, 
FirstName,
      LastName,
      Occupation,
      Sales,
      HireDate,
      TIMESTAMPDIFF(YEAR, HireDate, NOW()) AS 'Diff 1',
      TIMESTAMPDIFF(MONTH, NOW(), Hiredate) AS 'Diff2'
FROM `MySQL Tutorial`.customer;
MySQL TIMESTAMPDIFF Function 3