date_diff

Description

Returns the difference between two date values in the specified unit. This function returns the value of expr1 - expr2 expressed in terms of unit.

For example, date_diff('second', '2010-11-30 23:59:59', '2010-11-30 20:58:59') returns the difference between the two DATETIME values in unit of seconds.

The difference between date_diff and datediff lies in that datediff() does not support the unit parameter.

Syntax

BIGINT DATE_DIFF(VARCHAR unit, DATETIME expr1, DATETIME expr2)

Parameters

expr1 and expr2: the two datetime values you want to compare, required. Supported data types are DATETIME and DATE.
unit: the unit used to express the time difference, required. The following unit values are supported: year, quarter, month, week, day, hour, minute, second, millisecond.

Return value

Returns a BIGINT value.

Usage notes

If expr1 is earlier than expr2, a negative value is returned.
If unit is invalid, an error is returned.
If any input value is NULL, NULL is returned.
If the specified date does not exist, for example, 2022-11-31, NULL is returned.

Examples

mysql> select date_diff('second', '2010-11-30 23:59:59', '2010-11-30 20:58:59');
+-------------------------------------------------------------------+
| date_diff('second', '2010-11-30 23:59:59', '2010-11-30 20:58:59') |
+-------------------------------------------------------------------+
|                                                             10860 |
+-------------------------------------------------------------------+

mysql> select date_diff('minute', '2010-11-30 23:59:59', '2010-11-30 20:58:59');
+-------------------------------------------------------------------+
| date_diff('minute', '2010-11-30 23:59:59', '2010-11-30 20:58:59') |
+-------------------------------------------------------------------+
|                                                               181 |
+-------------------------------------------------------------------+

mysql> select date_diff('hour', '2010-11-30 23:59:59', '2010-11-30 20:58:59');
+-----------------------------------------------------------------+
| date_diff('hour', '2010-11-30 23:59:59', '2010-11-30 20:58:59') |
+-----------------------------------------------------------------+
|                                                               3 |
+-----------------------------------------------------------------+

mysql> select date_diff('millisecond', '2010-11-30 23:59:59', '2010-11-30 20:58:59');
+------------------------------------------------------------------------+
| date_diff('millisecond', '2010-11-30 23:59:59', '2010-11-30 20:58:59') |
+------------------------------------------------------------------------+
|                                                               10860000 |
+------------------------------------------------------------------------+

References

datediff