MySQL--TIMESTAMPDIFF函数

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 :

NameDescriptiondatetime_expr1A datetime expression.datetime_expr1A datetime expression.unitAn 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