MySQL TIMEDIFF() Function

Summary: in this tutorial, you will learn how to use the MySQL TIMEDIFF() function to calculate the difference between two TIME values.

Introduction to MySQL TIMEDIFF() function

The TIMEDIFF() function calculates the difference between two TIME or DATETIME values.

Here’s the syntax of TIMEDIFF() function:

TIMEDIFF(time1, time2);Code language: SQL (Structured Query Language) (sql)

In this syntax:

• time1: The first TIME or DATETIME value.
• time2: The second TIME or DATETIME value.

The TIMEDIFF() function returns the difference between two TIME values (time1 - time2) in the format 'HH:MM:SS'.

Because the TIMEDIFF() function returns a TIME value, its result is limited to the range allowed for TIME values which are from -838:59:59 to 838:59:59.

If time1 or time2 is NULL, the TIMEDIFF() function returns NULL.

It’s important to note that the TIMEDIFF() function accepts values with TIME or DATETIME types. To calculate the difference between two DATE or DATETIME values, you use the DATEDIFF() function.

MySQL TIMEDIFF function examples

Let’s take an example that calculates the difference between two TIME values.

1) Simple TIMEDIFF function example

The following example uses the TIMEDIFF() function to calculate the difference between two literal time values:

SELECT TIMEDIFF('12:00:00','10:00:00') diff;Code language: JavaScript (javascript)

Output:

+----------+
| diff     |
+----------+
| 02:00:00 |
+----------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

The query returns the time difference between '12:00:00' and '10:00:00', which is '02:00:00'.

2) Using the TIMEDIFF() function with table data

We’ll use the orders table from the sample database:

The following query uses the TIMEDIFF() function to find the time it took to ship each order:

SELECT 
  orderNumber, 
  TIMEDIFF(shippedDate, orderDate) shipTime 
FROM 
  orders;

Output:

+-------------+-----------+
| orderNumber | shipTime  |
+-------------+-----------+
|       10100 | 96:00:00  |
|       10101 | 48:00:00  |
|       10102 | 96:00:00  |
|       10103 | 96:00:00  |
...

The query calculates the time difference between shippedDate and orderDate for each order and displays the result in the shipTime column.

By default, the TIMEDIFF() function returns the time difference in the format "HH:MM:SS".

If you want to format the result, you can use the TIME_FORMAT() function. For example, the following displays the time difference in hours and minutes:

SELECT 
  orderNumber, 
  TIME_FORMAT(
    TIMEDIFF(shippedDate, orderDate), 
    '%H hours %i minutes'
  ) shipTime 
FROM 
  orders;Code language: JavaScript (javascript)

Output:

+-------------+----------------------+
| orderNumber | shipTime             |
+-------------+----------------------+
|       10100 | 96 hours 00 minutes  |
|       10101 | 48 hours 00 minutes  |
|       10102 | 96 hours 00 minutes  |
|       10103 | 96 hours 00 minutes  |
|       10104 | 24 hours 00 minutes  |
...

3) Using the TIMEDIFF() function with NULL

The TIMEDIFF() function returns NULL if either argument is NULL. For example:

SELECT TIMEDIFF('2010-01-01',NULL) diff;Code language: PHP (php)

Output:

+------+
| diff |
+------+
| NULL |
+------+
1 row in set, 1 warning (0.00 sec)Code language: SQL (Structured Query Language) (sql)

Summary

• Use the TIMEDIFF() function to calculate the difference between two TIME values.