How to use the MySQL TIMEDIFF() function

In this article, we will learn how to use the MySQL TIMEDIFF() function, which returns the difference between two time values.

Posted on

In this article, we will learn how to use the MySQL TIMEDIFF() function, which returns the difference between two time values. We will also see some examples of how to use this function in different situations, and explore some related functions that can be helpful for working with times and differences.

Syntax

The syntax of the TIMEDIFF() function is as follows:

TIMEDIFF(expr1, expr2)

The expr1 and expr2 parameters can be any valid time or datetime expressions, or strings that can be converted to time or datetime values. If either parameter is NULL, the function returns NULL. The TIMEDIFF() function returns a time value that represents the difference between the two given expressions. The format of the time value is ‘HH:MM:SS’. For example, TIMEDIFF('10:02:34', '09:00:00') returns ‘01:02:34’, which is the difference between the two time values.

Examples

Let’s see some examples of how to use the TIMEDIFF() function in MySQL.

Example 1: Get the difference between two time values

We can use the TIMEDIFF() function to get the difference between two time values. For example:

SELECT TIMEDIFF('10:02:34', '09:00:00') AS result;

This query will return the difference between the two time values ‘10:02:34’ and ‘09:00:00’. The query will return ‘01:02:34’, which is the difference between the two time values.

Example 2: Get the difference between two datetime values

We can use the TIMEDIFF() function to get the difference between two datetime values. For example:

SELECT TIMEDIFF('2023-01-15 10:02:34', '2023-01-14 09:00:00') AS result;

This query will return the difference between the two datetime values ‘2023-01-15 10:02:34’ and ‘2023-01-14 09:00:00’. The query will return ‘25:02:34’, which is the difference between the two datetime values.

Example 3: Get the difference between a time value and a datetime value

We can use the TIMEDIFF() function to get the difference between a time value and a datetime value. For example:

SELECT TIMEDIFF('10:02:34', '2023-01-15 09:00:00') AS result;

This query will return the difference between the time value ‘10:02:34’ and the datetime value ‘2023-01-15 09:00:00’. The query will return ‘-838:59:59’, which is the minimum time value that can be represented in MySQL. This is because the time value is assumed to have the same date as the datetime value, and the difference is negative.

Example 4: Get the difference between a fractional time value and another time value

We can use the TIMEDIFF() function to get the difference between a fractional time value and another time value, such as a time value with milliseconds or microseconds. For example:

SELECT TIMEDIFF('10:02:34.123456', '10:00:00') AS result;

This query will return the difference between the fractional time value ‘10:02:34.123456’ and the time value ‘10:00:00’. The query will return ‘00:02:34.123456’, which is the difference between the two time values.

Example 5: Get the difference between a negative time value and another time value

We can use the TIMEDIFF() function to get the difference between a negative time value and another time value, which is equivalent to adding the positive time value to the negative time value. For example:

SELECT TIMEDIFF('-10:02:34', '01:00:00') AS result;

This query will return the difference between the negative time value ‘-10:02:34’ and the time value ‘01:00:00’. The query will return ‘-11:02:34’, which is the difference between the two time values.

There are some other functions that are related to the TIMEDIFF() function, and can be useful for working with times and differences. Here are some of them:

  • TIME(): This function returns the time part of a date or datetime value, or a string that can be converted to a time value. For example, TIME('2023-01-15 10:02:34') returns ‘10:02:34’.
  • TIME_TO_SEC(): This function converts a time value to a number of seconds. For example, TIME_TO_SEC('10:02:34') returns 36154.
  • SEC_TO_TIME(): This function converts a number of seconds to a time value. For example, SEC_TO_TIME(36154) returns ‘10:02:34’.
  • TIME_FORMAT(): This function returns the time value formatted according to a specified format. For example, TIME_FORMAT('10:02:34', '%H:%i:%s') returns ‘10:02:34’.
  • TIME_ADD(): This function adds a time value to another time value. For example, TIME_ADD('10:02:34', '01:00:00') returns ‘11:02:34’.

Conclusion

In this article, we learned how to use the MySQL TIMEDIFF() function, which returns the difference between two time values. We also saw some examples of how to use this function in different situations, and explored some related functions that can be helpful for working with times and differences.