MySQL - TIMESTAMPDIFF() Examples

The MySQL TIMESTAMPDIFF() function is used to find the difference between two date or datetime expressions. You need to pass in the two date/datetime values, as well as the unit to use in determining the difference (e.g., day, month, etc). The TIMESTAMPDIFF()function will then return the difference in the specified unit.

Syntax

First, here’s how the syntax goes:

TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)

Here, unit is the unit to use in expressing the difference (e.g. day, month, year, etc). datetime_expr1 is the first date/datetime value, and datetime_expr2 is the second.

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

Valid Units

The unit argument can be any of the following:

• MICROSECOND
•  SECOND
• MINUTE
• HOUR
• DAY
• WEEK
• MONTH
• QUARTER
• YEAR

Example 1 – Difference in Days

Here’s an example to demonstrate the basic usage of this function. Here we compare two date expressions and return the difference between them in days.

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

Result:

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

Example 2 – Difference in Hours

In this example we compare the same values as in the previous example, except here, we return the difference in hours.

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

Result:

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

Example 3 – A ‘datetime’ Example

Here’s an example that returns the difference in minutes. In this case, we compare two datetime values (as opposed to just the date values as in the previous examples).

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

Result:

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

Example 4 – Fractional Seconds

You can go right down to the microsecond (6 digits) if you need to.

SELECT 
  TIMESTAMPDIFF(MICROSECOND,'2022-02-01 10:30:27.000000','2022-02-01 10:30:27.123456') 
  AS 'Difference in Microseconds';

Result:

+----------------------------+
| Difference in Microseconds |
+----------------------------+
|                     123456 |
+----------------------------+

Example 5 – Negative Results

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

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

Result:

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