TIMESTAMPDIFF

Description

The timestampdiff function is used to calculate the difference between two 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 to return the difference in, supports SECOND, MINUTE, HOUR, DAY, MONTH, QUARTER, YEAR

tip

QUARTER is supported since version 3.0.8 and 3.1.0.

| datetime_expr1 | The first datetime, a valid target date | | datetime_expr2 | The second datetime, a valid target date |

Return Value

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

If the input parameters are invalid, NULL is returned.

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(QUARTER,'2002-05-01','2001-01-01');

+--------------------------------------------------+
| TIMESTAMPDIFF(QUARTER,'2002-05-01','2001-01-01') |
+--------------------------------------------------+
|                                               -5 |
+--------------------------------------------------+

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 |
+---------------------------------------------------------------------+

SELECT  TIMESTAMPDIFF(MINUTE,'2003-02-01','1196440219');

+-----------------------------------------------------------------------------------+
| timestampdiff(MINUTE, '2003-02-01 00:00:00', CAST('1196440219' AS datetimev2(6))) |
+-----------------------------------------------------------------------------------+
|                                                                              NULL |
+-----------------------------------------------------------------------------------+