TIMESTAMPDIFF

Description

The timestampdiff function is used to calculate the difference between two timestamps or dates and returns the time interval between them. The difference can be returned in the specified time unit (such as seconds, minutes, hours, days, months, years, etc.).

Syntax

TIMESTAMPDIFF(<unit>, <datetime_expr1>, <datetime_expr2>)

Parameters

Parameter	Description
unit	Time unit, specifies the unit in which to return the difference, common values include SECOND, MINUTE, HOUR, DAY, MONTH, YEAR
datetime_expr1	The first datetime, a valid target timestamp or date
datetime_expr2	The second datetime, a valid target timestamp or date

Return Value

The return value is the difference between the two date-times, with the unit determined by the unit parameter.

Examples

SELECT TIMESTAMPDIFF(MONTH,'2003-02-01','2003-05-01');

+--------------------------------------------------------------------+
| timestampdiff(MONTH, '2003-02-01 00:00:00', '2003-05-01 00:00:00') |
+--------------------------------------------------------------------+
|                                                                  3 |
+--------------------------------------------------------------------+

SELECT TIMESTAMPDIFF(YEAR,'2002-05-01','2001-01-01');

+-------------------------------------------------------------------+
| timestampdiff(YEAR, '2002-05-01 00:00:00', '2001-01-01 00:00:00') |
+-------------------------------------------------------------------+
|                                                                -1 |
+-------------------------------------------------------------------+

SELECT TIMESTAMPDIFF(MINUTE,'2003-02-01','2003-05-01 12:05:55');

+---------------------------------------------------------------------+
| timestampdiff(MINUTE, '2003-02-01 00:00:00', '2003-05-01 12:05:55') |
+---------------------------------------------------------------------+
|                                                              128885 |
+---------------------------------------------------------------------+