MySQL TIMEDIFF() Function

The TIMEDIFF() function in MySQL is used to return the difference between two time or datetime expressions.

Definition and Usage

• Purpose: The function calculates the difference between two time values, time1 and time2, and returns the result.
• Syntax: TIMEDIFF(time1, time2)

Syntax

TIMEDIFF(time1, time2)

• time1: The first time or datetime value.
• time2: The second time or datetime value.
  • Both time1 and time2 should be in the same format (either TIME or DATETIME).

Technical Details

• Works in: From MySQL 4.0 onwards.
• Return Type: The result is returned as a TIME value. If time1 is later than time2, the result will be positive. Otherwise, it will be negative.

Examples

Example 1: Calculate the difference between two times

SELECT TIMEDIFF("13:10:11", "13:10:10");

Result:

00:00:01

Explanation: The difference between 13:10:11 and 13:10:10 is 1 second.

Example 2: Calculate the difference between two datetime values

SELECT TIMEDIFF("2017-06-25 13:10:11", "2017-06-15 13:10:10");

Result:

10 00:00:01

Explanation: The difference between 2017-06-25 13:10:11 and 2017-06-15 13:10:10 is 10 days, 1 second.

Use Cases

• Event Duration: To find the duration between two datetime values (e.g., start and end times of an event).
• Time Differences: When comparing timestamps or time values in applications to track time elapsed or remaining.
• Scheduled Jobs: To calculate intervals between scheduled tasks or execution times.

Key Notes

• The TIMEDIFF() function calculates the difference by subtracting time2 from time1 (time1 - time2).
• This function can work with both TIME and DATETIME values, but the results are always in the TIME format (i.e., HH:MM:SS).