How to Use the TIMEDIFF() Function in MySQL

08/05/2021

Contents

In this article, you will learn how to use the TIMEDIFF() function in MySQL.

Using the TIMEDIFF() function in MySQL

The TIMEDIFF() function in MySQL is used to calculate the difference between two time values. It returns the difference between two time values as a time value.

Syntax

TIMEDIFF(time1, time2);

Examples

Here are some examples of using the TIMEDIFF() function in MySQL:

Calculating the difference between two time values

To calculate the difference between two time values, you can use the TIMEDIFF() function.

Syntax
SELECT TIMEDIFF(time1, time2);
Example
SELECT TIMEDIFF('08:30:00', '12:00:00');

Output:

+------------------------------+
| TIMEDIFF('08:30:00', '12:00:00') |
+------------------------------+
| -03:30:00                    |
+------------------------------+

In this example, the TIMEDIFF() function calculates the difference between the time values ’08:30:00′ and ’12:00:00′.

Calculating the time difference between two dateTime values

You can also use the TIMEDIFF() function to calculate the time difference between two datetime values.

Syntax
SELECT TIMEDIFF(datetime1, datetime2);
Example
SELECT TIMEDIFF('2022-05-15 08:30:00', '2022-05-15 12:00:00');

Output:

+-------------------------------------------------+
| TIMEDIFF('2022-05-15 08:30:00', '2022-05-15 12:00:00') |
+-------------------------------------------------+
| -03:30:00                                       |
+-------------------------------------------------+

In this example, the TIMEDIFF() function calculates the difference between the datetime values ‘2022-05-15 08:30:00’ and ‘2022-05-15 12:00:00’.

Calculating the time difference between two time values in seconds

You can use the TIME_TO_SEC() function to convert the time difference returned by the TIMEDIFF() function to seconds.

Syntax
SELECT TIME_TO_SEC(TIMEDIFF(time1, time2));
Example
SELECT TIME_TO_SEC(TIMEDIFF('08:30:00', '12:00:00'));

Output:

+-----------------------------------+
| TIME_TO_SEC(TIMEDIFF('08:30:00', '12:00:00')) |
+-----------------------------------+
| -12600                           |
+-----------------------------------+

In this example, the TIMEDIFF() function calculates the difference between the time values ’08:30:00′ and ’12:00:00′, and the TIME_TO_SEC() function converts the result to seconds.

#MySQL