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.
代码实例：
测试数据：
uid
type
code
address地址
phone
bank
bankacct
status
subuid
subtime
chkuid
chktime
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
uid
subtime
chktime
TIMESTAMPDIFF(DAY,subtime,chktime)
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
uid
subtime
chktime
TIMESTAMPDIFF(YEAR,subtime,chktime)
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
uid
subtime
chktime
TIMESTAMPDIFF(MONTH,subtime,chktime)
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
