TIMESTAMPDIFF usage and examples

Overview

TIMESTAMPDIFF(unit, start, end) returns end minus start in complete units. Argument order controls the sign, and DAY, MONTH, and YEAR are not interchangeable with a simple seconds conversion. Build boundary tests for age, billing-cycle, and cross-time-zone calculations.

Basic syntax

SELECT TIMESTAMPDIFF(DAY, '2026-01-01', '2026-01-10') AS days;

Examples

TIMESTAMPDIFF example

The example counts nine complete days from 2026-01-01 to 2026-01-10 and makes the start/end argument order explicit.

Run result

result
days
9

Common issues

How does argument order affect TIMESTAMPDIFF?

MySQL calculates the end value minus the start value, so reversing them changes the sign.

Are month or year differences rounded?

They are based on complete unit boundaries rather than ordinary decimal rounding; incomplete units are normally not counted.

What should I check before comparing across time zones?

Normalize inputs to an explicit zone and confirm their data types. Daylight-saving changes and session time zones can alter hour-level results.

Keep reading

DATEDIFF DATE_ADD DATE_FORMAT

TIMESTAMPDIFF