How to Use TIMEDIFF in MySQL: Syntax and Examples

In MySQL, use the TIMEDIFF(expr1, expr2) function to find the difference between two time or datetime values. It returns the result as a time value showing the hours, minutes, and seconds difference.

Syntax

The TIMEDIFF function takes two arguments: expr1 and expr2, which are time or datetime expressions. It returns the difference as a time value calculated as expr1 - expr2.

• expr1: The first time or datetime value.
• expr2: The second time or datetime value to subtract from the first.

If the result is negative, the returned time will have a negative sign.

sql

TIMEDIFF(expr1, expr2)

Example

This example shows how to calculate the difference between two time values and two datetime values using TIMEDIFF.

sql

SELECT TIMEDIFF('2024-06-01 15:30:00', '2024-06-01 12:00:00') AS diff_datetime,
       TIMEDIFF('18:45:00', '14:15:00') AS diff_time;

Output

diff_datetime | diff_time
-------------|----------
03:30:00     | 04:30:00

Common Pitfalls

Common mistakes when using TIMEDIFF include:

• Passing non-time or non-datetime values, which causes errors or unexpected results.
• Confusing the order of arguments; TIMEDIFF(expr1, expr2) returns expr1 - expr2, so reversing them changes the sign.
• Expecting the result as a numeric value instead of a time format.

sql

/* Wrong: reversed arguments */
SELECT TIMEDIFF('12:00:00', '18:00:00') AS wrong_diff;

/* Correct: expr1 - expr2 */
SELECT TIMEDIFF('18:00:00', '12:00:00') AS correct_diff;

Output

wrong_diff
-06:00:00

correct_diff
06:00:00

Quick Reference

Function	Description	Returns
TIMEDIFF(expr1, expr2)	Subtracts expr2 from expr1 (time or datetime)	Time value (HH:MM:SS)
expr1, expr2	Time or datetime expressions	Input values
Result sign	Positive if expr1 > expr2, negative if expr1 < expr2	Signed time

Key Takeaways

Use TIMEDIFF(expr1, expr2) to get the time difference as expr1 minus expr2.

Both arguments must be time or datetime values for correct results.

The result is a time value showing hours, minutes, and seconds.

Order of arguments matters; reversing them changes the sign of the result.

TIMEDIFF does not return a numeric value but a time formatted string.