TIMESTAMPDIFF() function
MySQL the TIMESTAMPDIFF() returns a value after subtracting a datetime expression from another.
It is not necessary that both the expression are of the same type. One may be a date and another is datetime. A date value is treated as a datetime with a default time part '00:00:00'. The unit for the result is given by another argument.
The unit should be one of the following : FRAC_SECOND (microseconds), SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or YEAR.
Syntax :
TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2);
Arguments :
Name | Description |
---|---|
datetime_expr1 | A datetime expression. |
datetime_expr1 | A datetime expression. |
unit | An unit, as described in the description. |
代码实例:
测试数据:
1 | 0 | 310211111 | test | 021-111111 | test1 | 10012551111 | 1 | 111 | 2017-04-11 18:41:13 | 34111 | 2017-07-21 13:56:58 |
1 | 1 | 2323 | 2 | 211 | 2016-11-08 18:54:01 | 111 | 2017-05-18 20:25:43 |
测试代码:
1.计算相差的天数
SELECTuid,subtime,chktime,TIMESTAMPDIFF(DAY,subtime,chktime) FROM `user_info` whereuid=1
1 | 2017-04-11 18:41:13 | 2017-07-21 13:56:58 | 100 |
1 | 2016-11-08 18:54:01 | 2017-05-18 20:25:43 | 191 |
2.计算相差的年数
SELECTuid,subtime,chktime,TIMESTAMPDIFF(YEAR,subtime,chktime) FROM `user_info` whereuid=1
1 | 2017-04-11 18:41:13 | 2017-07-21 13:56:58 | 0 |
1 | 2016-11-08 18:54:01 | 2017-05-18 20:25:43 | 0 |
3.计算相差的月数
SELECTuid,subtime,chktime,TIMESTAMPDIFF(MONTH,subtime,chktime) FROM `user_info` whereuid=1
1 | 2017-04-11 18:41:13 | 2017-07-21 13:56:58 | 3 |
1 | 2016-11-08 18:54:01 | 2017-05-18 20:25:43 | 6 |