How to Use TIMESTAMPDIFF in MySQL: Syntax and Examples

Use the TIMESTAMPDIFF(unit, datetime_expr1, datetime_expr2) function in MySQL to find the difference between two date or datetime values. It returns the difference as an integer in the specified unit such as SECOND, MINUTE, HOUR, DAY, MONTH, QUARTER, or YEAR.

Syntax

The TIMESTAMPDIFF function calculates the difference between two date or datetime expressions in the specified unit.

• unit: The unit for the result (e.g., SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR).
• datetime_expr1: The start date or datetime.
• datetime_expr2: The end date or datetime.

sql

TIMESTAMPDIFF(unit, datetime_expr1, datetime_expr2)

Example

This example shows how to calculate the number of days between two dates and the number of hours between two datetime values.

sql

SELECT
  TIMESTAMPDIFF(DAY, '2024-01-01', '2024-01-10') AS days_diff,
  TIMESTAMPDIFF(HOUR, '2024-01-01 08:00:00', '2024-01-01 20:00:00') AS hours_diff;

Output

days_diff | hours_diff
----------|-----------
9         | 12

Common Pitfalls

Common mistakes include:

• Mixing the order of dates: datetime_expr1 should be the start date, and datetime_expr2 the end date; otherwise, the result can be negative.
• Using unsupported units or misspelling unit names.
• Expecting fractional results: TIMESTAMPDIFF returns an integer, so partial units are truncated.

sql

/* Wrong: end date first, result negative */
SELECT TIMESTAMPDIFF(DAY, '2024-01-10', '2024-01-01') AS diff_wrong;

/* Correct: start date first */
SELECT TIMESTAMPDIFF(DAY, '2024-01-01', '2024-01-10') AS diff_correct;

Output

diff_wrong
----------
-9

diff_correct
------------
9

Quick Reference

Unit	Description
SECOND	Difference in seconds
MINUTE	Difference in minutes
HOUR	Difference in hours
DAY	Difference in days
WEEK	Difference in weeks
MONTH	Difference in months
QUARTER	Difference in quarters (3 months)
YEAR	Difference in years

Key Takeaways

TIMESTAMPDIFF returns the difference between two dates or datetimes as an integer in the specified unit.

Always put the earlier date as the first argument and the later date as the second to avoid negative results.

Supported units include SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, and YEAR among others.

The function truncates partial units; it does not return fractional values.

Use TIMESTAMPDIFF to easily calculate elapsed time or date differences in MySQL queries.