MySQL TIMEDIFF Function

The TIMEDIFF function in MySQL calculates the difference between two time or datetime values and returns the result as a TIME value. This function is particularly useful when finding the duration between two events or timestamps.

Syntax

TIMEDIFF(time1, time2)

time1: The first time or datetime value.
time2: The second time or datetime value to subtract from time1.

How It Works

The function subtracts time2 from time1.
Both inputs must be either TIME or DATETIME values. If only time components are provided, MySQL assumes a default date ('00:00:00').
If either input is NULL, the result is NULL.
The returned value is a TIME object, which can also include negative durations.

Examples

1. Calculate Difference Between Two Times

SELECT TIMEDIFF('12:00:00', '08:30:00') AS time_difference;

Output:

+----------------+
| time_difference|
+----------------+
| 03:30:00       |
+----------------+

2. Calculate Difference Between Two Datetimes

SELECT TIMEDIFF('2025-01-26 14:00:00', '2025-01-26 10:00:00') AS time_difference;

Output:

+----------------+
| time_difference|
+----------------+
| 04:00:00       |
+----------------+

3. Negative Time Difference

SELECT TIMEDIFF('08:30:00', '12:00:00') AS time_difference;

Output:

+----------------+
| time_difference|
+----------------+
| -03:30:00      |
+----------------+

4. Using `TIMEDIFF` with a Table

Suppose you have a table events:

event_id	start_time	end_time
1	2025-01-26 08:00:00	2025-01-26 12:30:00
2	2025-01-26 14:00:00	2025-01-26 15:45:00

Query to calculate the duration for each event:

SELECT event_id, TIMEDIFF(end_time, start_time) AS duration
FROM events;

Output:

+----------+----------+
| event_id | duration |
+----------+----------+
| 1        | 04:30:00 |
| 2        | 01:45:00 |
+----------+----------+

5. Use with `CURTIME` or `NOW`

Calculate the time difference from the current time:

SELECT TIMEDIFF(NOW(), '2025-01-26 08:00:00') AS time_since_start;

Output:
Depends on the current time.

Practical Use Cases

Tracking Durations: Calculate the time spent on tasks or events.
Scheduling Applications: Find gaps or overlaps between events.
Performance Monitoring: Measure execution times for queries or processes.
Calculate Overtime: Determine the time employees worked beyond their scheduled hours.

Notes

The result is always in the format HH:MM:SS. If the difference exceeds 24 hours, it only shows the time component, not the days.
Ensure that the inputs are compatible (TIME or DATETIME). Mixing incompatible types may cause unexpected results.

Conclusion

The TIMEDIFF function is an essential tool in MySQL for calculating time differences. It is versatile and easy to use, making it perfect for a wide range of applications where time intervals are critical.

Souy Soeng

MySQL TIMEDIFF Function