MySQL TIMESTAMPDIFF() function explained

The TIMESTAMPDIFF() function in MySQL is used to subtract a period of time between two datetime values.

The function requires a unit of time value that you want to retrieve and two datetime expressions.

The syntax of TIMESTAMPDIFF is as follows:

TIMESTAMPDIFF(unit, date_1, date_2)

The unit parameter will determine how MySQL returns the value after subtraction. It can be one of the following values:

• FRAC_SECOND (microseconds)
• SECOND
• MINUTE
• HOUR
• DAY
• WEEK
• MONTH
• QUARTER
• YEAR

The date_1 and date_2 expressions can be of DATE, DATETIME, or TIMESTAMP type. You can put literal date expressions or column names of date type.

Here’s an example of how TIMESTAMPDIFF() function works:

SELECT TIMESTAMPDIFF(DAY, "2021-10-05", "2021-10-08");

The returned result set will be as shown below:

+------------------------------------------------+
| TIMESTAMPDIFF(DAY, "2021-10-05", "2021-10-08") |
+------------------------------------------------+
|                                              3 |
+------------------------------------------------+

The function call above calculates the DAY difference between 8th October 2021 - 5th October 2021, returning 3 days.

You can change the unit parameter from DAY to any other valid value as listed above.

The example below calculates the MONTH difference between the two dates:

SELECT TIMESTAMPDIFF(MONTH, "2021-10-05", "2021-10-08");

-- returns 0

The function will return a negative number when the date_1 argument is later than date_2 as follows:

SELECT TIMESTAMPDIFF(MONTH, "2021-12-05", "2021-10-08");

--   +--------------------------------------------------+
--   | TIMESTAMPDIFF(MONTH, "2021-12-05", "2021-10-08") |
--   +--------------------------------------------------+
--   |                                               -1 |
--   +--------------------------------------------------+

You can also mix the arguments between one date type and another.

For example, DATE and DATETIME can still be compared like this:

SELECT TIMESTAMPDIFF(MINUTE, "2021-10-10", "2021-10-10 03:00:00");

-- returns 180

The DATE value will be treated as a DATETIME internally, with 00:00:00 as its time part.

This means that you can use two date columns with different types as arguments for the function.

In the example table below, the join_date is of DATE type while :

+------------+---------------------+
| join_date  | last_update         |
+------------+---------------------+
| 2021-10-15 | 2021-10-19 09:20:10 |
| 2021-10-16 | 2021-10-19 09:20:10 |
+------------+---------------------+

You can query the table columns above to find how many days passed between last_update and join_date as shown below:

SELECT TIMESTAMPDIFF(DAY, join_date, last_update) FROM example;

--   +--------------------------------------------+
--   | TIMESTAMPDIFF(DAY, join_date, last_update) |
--   +--------------------------------------------+
--   |                                          4 |
--   |                                          3 |
--   +--------------------------------------------+

Keep in mind that the function always returns an integer number, so any floating number extracted between the two dates will be rounded down.

For example, 2021-07-25 and 2021-10-15 could return 11.7 when counting the week difference, but TIMESTAMPDIFF() will round down the result to 11 as shown below:

SELECT TIMESTAMPDIFF(WEEK, "2021-07-25", "2021-10-15") as weeks_passed;

--   +--------------+
--   | weeks_passed |
--   +--------------+
--   |           11 |
--   +--------------+

You’ve just learned how to use the TIMESTAMPDIFF() function to count the time differences between two different dates in MySQL.

Nice work! 👍