Introduction

MySQL gives you several ways to calculate the difference between two DATETIME values, and the right choice depends on the format you want back. Sometimes you want a count of hours or days. Other times you want a clock-style duration such as 01:45:30.

Use TIMESTAMPDIFF() for Numeric Units

TIMESTAMPDIFF() is the most direct choice when you want an integer difference in a specific unit such as seconds, minutes, hours, or days.

SELECT TIMESTAMPDIFF(MINUTE, '2026-03-11 08:00:00', '2026-03-11 10:45:00') AS minutes_diff;

This returns 165. The function takes three arguments:

• the unit to measure in
• the start datetime
• the end datetime

Common units include SECOND, MINUTE, HOUR, DAY, MONTH, and YEAR.

Example with Table Data

Suppose you store job start and finish times:

1CREATE TABLE jobs (
2    id INT PRIMARY KEY,
3    started_at DATETIME NOT NULL,
4    finished_at DATETIME NOT NULL
5);
6
7INSERT INTO jobs (id, started_at, finished_at) VALUES
8    (1, '2026-03-11 09:00:00', '2026-03-11 11:30:15'),
9    (2, '2026-03-11 12:10:00', '2026-03-12 09:10:00');
10
11SELECT
12    id,
13    TIMESTAMPDIFF(SECOND, started_at, finished_at) AS duration_seconds,
14    TIMESTAMPDIFF(HOUR, started_at, finished_at) AS duration_hours
15FROM jobs;

This is usually the best pattern for reports, filtering, and aggregations because the result is numeric and easy to compare.

Use TIMEDIFF() for Clock-Style Output

If you want a time value rather than an integer count of units, use TIMEDIFF():

SELECT TIMEDIFF('2026-03-11 10:45:00', '2026-03-11 08:00:00') AS time_diff;

This returns a result such as 02:45:00. That can be useful for display, but it is less convenient than TIMESTAMPDIFF() when you need arithmetic or conditions such as "all rows longer than 90 minutes."

Negative Results and Argument Order

The order of arguments matters. MySQL subtracts the first datetime from the second one in TIMESTAMPDIFF(unit, start, end). If end is earlier than start, the result is negative.

SELECT TIMESTAMPDIFF(HOUR, '2026-03-11 18:00:00', '2026-03-11 12:00:00') AS diff_hours;

That returns -6. Negative values are often useful because they preserve direction instead of hiding bad data.

Working with Dates Larger Than One Day

One subtlety is that TIMEDIFF() is presentation-oriented. If you are analyzing durations over long periods, numeric units from TIMESTAMPDIFF() are usually clearer. For example, a job that spans several days is easier to reason about as total minutes or total seconds than as a formatted time value.

When you need mixed formatting, calculate numerically first and then format deliberately in application code or in SQL with explicit arithmetic.

Nulls and Time Zone Considerations

If either datetime is NULL, the result is NULL. In real queries, it is common to protect against unfinished rows:

1SELECT
2    id,
3    TIMESTAMPDIFF(MINUTE, started_at, finished_at) AS duration_minutes
4FROM jobs
5WHERE finished_at IS NOT NULL;

Also remember that DATETIME stores a calendar value without automatic time zone conversion. If your application mixes time zones, make sure both values were recorded consistently before comparing them.

Common Pitfalls

• Using TIMEDIFF() when you really need an integer for filtering or aggregation.
• Reversing the argument order and being surprised by negative results.
• Forgetting that TIMESTAMPDIFF() returns whole units, not fractional ones.
• Comparing datetimes recorded in inconsistent time zones.
• Ignoring NULL end times in partially completed records.

Summary

• Use TIMESTAMPDIFF() when you want a numeric difference in a chosen unit.
• Use TIMEDIFF() when you want a clock-style duration value.
• Argument order matters and can produce negative results.
• Numeric durations are usually easier to filter, sort, and aggregate.
• Check for NULL values and time zone consistency before trusting the result.