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.
This function is supported from v3.1.
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
andexpr2
: 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 followingunit
values are supported: year, quarter, month, week, day, hour, minute, second, millisecond.
Return value
Returns a BIGINT value.
Usage notes
- If
expr1
is earlier thanexpr2
, 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 |
+------------------------------------------------------------------------+