MySQL TIMESTAMPDIFF() Function with Example

MySQL TIMESTAMPDIFF() function; In this tutorial, we would love to share with you how to use MySQL timestampdiff() function with the help of examples.

MySQL TIMESTAMPDIFF() Function

The MySQL TIMESTAMPDIFF () function is used to find the difference between two date or DateTime expressions.

Note: You need to pass two date / datetime values along with the unit (eg, day, month, etc.) to use for determining the difference. The TIMESTAMPDIFF () function will then return the difference in the unit specified.

Syntax

The syntax is MySQL timestampdiff() is:

TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)

This function reduces datetime_expr1 from datetime_expr2 and returns results in units. The result is returned as an integer.

Valid Units

The unit an argument can be any of the following:

MICROSECOND
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR

Example-1

Here’s an example, we will display the usage of this function. Here we compare the two dates and return the differences between them in days.

  SELECT
  TIMESTAMPDIFF(DAY,'2022-02-01','2022-02-21')
  AS 'Difference in Days';

Output-1

+--------------------+
| Difference in Days |
+--------------------+
|                 20 |
+--------------------+

Example-2

In this example, we compare the values of the previous examples, except by leaving here, we return the difference in hours.

  SELECT
  TIMESTAMPDIFF(HOUR,'2022-02-01','2022-02-21')
  AS 'Difference in Hours';

Output-2

+---------------------+
| Difference in Hours |
+---------------------+
|                 480 |
+---------------------+

Example-3

Here’s an example that returns a difference in minutes. In this case, we compare two data-time values.

  SELECT
  TIMESTAMPDIFF(MINUTE,'2022-02-01 10:30:27','2022-02-01 10:45:27')
  AS 'Difference in Minutes';

Output-3

+-----------------------+
| Difference in Minutes |
+-----------------------+
|                    15 |
+-----------------------+

Example-4

As expected, if the first date/time argument is greater than the second, the result would be a negative integer.

  SELECT
  TIMESTAMPDIFF(DAY,'2022-02-21','2022-02-01')
  AS 'Difference in Days';

Output-4

+--------------------+
| Difference in Days |
+--------------------+
|                -20 |
+--------------------+

Example-5

In this statement, We take database table example, here we calculated the ages. See the below example:

 SELECT id, name, date_of_birth, TIMESTAMPDIFF(YEAR, date_of_birth, NOW()) age
 FROM
 employees;

Conclusion

Here, you have learned how to use MySQL TIMESTAMPDIFF() function with various examples.

Subscribe to Blog via Email