MySql 中时间差值的计算

使用 DATEDIFF()

DATEDIFF() 方法计算两时间（只含时间的日期部分，不包含时刻（时、分、秒））相减得到的天数值

格式：DATEDIFF(expr1,expr2)

e.g:

mysql> SELECT DATEDIFF('2017-03-24 01:03:44','2017-03-23 14:03:44');
-> 1
mysql> SELECT DATEDIFF('2017-03-25 01:03:44','2017-03-25 23:59:44');
-> 0

官方文档对 DATEDIFF() 方法的说明：

DATEDIFF() returns expr1 − expr2 expressed as a value in days from one date to the other.

expr1 and expr2 are date or date-and-time expressions. Only the date parts of the values are used in the calculation.

使用 TIMESTAMPDIFF()

如果要计算两时间之间相差的天数/秒数/分钟数/周数/小时数，可通过TIMESTAMPDIFF()方法实现

格式：TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)

e.g:

mysql> SELECT TIMESTAMPDIFF(MONTH,'2003-02-01','2003-05-01');
-> 3
mysql> SELECT TIMESTAMPDIFF(YEAR,'2002-05-01','2001-01-01');
-> -1
mysql> SELECT TIMESTAMPDIFF(MINUTE,'2003-02-01','2003-05-01 12:05:55');
-> 128885

官方文档对 TIMESTAMPDIFF() 方法的说明：

Returns datetime_expr2 − datetime_expr1, where datetime_expr1 and datetime_expr2 are date or datetime expressions. One expression may be a date and the other a datetime; a date value is treated as a datetime having the time part ‘00:00:00’ where necessary. The unit for the result (an integer) is given by the unit argument. The legal values for unit are the same as those listed in the description of the TIMESTAMPADD() function.

The unit for interval is given by the unit argument, which should be one of the following values: MICROSECOND (microseconds), SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or YEAR.