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! 👍

Take your skills to the next level ⚡️

I'm sending out an occasional email with the latest tutorials on programming, web development, and statistics. Drop your email in the box below and I'll send new stuff straight into your inbox!

No spam. Unsubscribe anytime.