Categories:
Date & Time Functions

TIMEDIFF

Calculates the difference between two date, time, or timestamp expressions based on the specified date or time part.

Alternative for DATEDIFF.

Syntax

TIMEDIFF( <date_or_time_part> , <date_or_time_expr1> , <date_or time_expr2> )

Arguments

  • date_or_time_part must be one of the values listed in Supported Date and Time Parts.
  • date_or_time_expr1, date_or_time_expr2 must be a date, a time, a timestamp, or an expression that can be evaluated to one of those. The first value is subtracted from the second value.

Usage Notes

Examples

This shows the result of subtracting two dates, in which the second is two years later than the first:

SELECT TIMEDIFF(YEAR, '2017-01-01', '2019-01-01') AS Years;

Output:

+--------+
| YEARS  |
+========+
|     2  |
+--------+

This shows that we don’t round to the nearest value. The difference is closer to 12 months than to 11, but we calculate the difference as 11 months:

SELECT TIMEDIFF(MONTH, '2017-01-1', '2017-12-31') AS Months;

Output:

+--------+
| MONTHS |
+========+
|    11  |
+--------+

There are additional examples in DATEDIFF.