mysql timestampdiff()函数

mysql timestampdiff()函数
今天跑批异常，需要去数据库查询核对，开发提供SQL,有个timestampdiff()函数，之前没有接触过，查了下资料，如下：
（一）mysql> help timestampdiff;
Name: ‘TIMESTAMPDIFF’
Description:
Syntax:
TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)

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.

URL: http://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html

Examples:
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(间隔，时间戳1，时间戳2)
举例：
select timestampdiff(hour,‘2020-02-07 8:00’,‘2020-02-08 9:00’);
±--------------------------------------------------------+
| timestampdiff(hour,‘2020-02-07 8:00’,‘2020-02-08 9:00’) |
±--------------------------------------------------------+
| 25 |
±--------------------------------------------------------+
这里的间隔是hour，如果不是整数，只取整数部分。
关于“间隔“的单位：
1，FRAC_SECOND。表示间隔是毫秒
2，SECOND。秒
3，MINUTE。分钟
4，HOUR。小时
5，DAY。天
6，WEEK。星期
7.MONTH。月
8，QUARTER。季度
9，YEAR。年

（二）网上也查了下，接触到了TIMESTAMPADD函数，顺便看了下怎么用，
mysql> help TIMESTAMPADD;
Name: ‘TIMESTAMPADD’
Description:
Syntax:
TIMESTAMPADD(unit ,interval,datetime_expr)

Adds the integer expression interval to the date or datetime expression
datetime_expr. 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.

The unit value may be specified using one of keywords as shown, or with
a prefix of SQL_TSI_. For example, DAY and SQL_TSI_DAY both are legal.

URL: http://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html

Examples:
mysql> SELECT TIMESTAMPADD(MINUTE,1,‘2003-01-02’);
-> ‘2003-01-02 00:01:00’
mysql> SELECT TIMESTAMPADD(WEEK,1,‘2003-01-02’);
-> ‘2003-01-09’

举例：
select timestampadd(second,50,“2020-02-08 08:22:01”);
±----------------------------------------------+
| timestampadd(second,50,“2020-02-08 08:22:01”) |
±----------------------------------------------+
| 2020-02-08 08:22:51 |
±----------------------------------------------+
1 row in set (0.00 sec)
关于这个unit同样有很多取值，小伙伴们可以试试。

（三）最后说说datediff这个函数：
help datediff;
Name: ‘DATEDIFF’
Description:
Syntax:
DATEDIFF(expr1,expr2)

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.

URL: http://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html

举例：
mysql> SELECT DATEDIFF(‘2007-12-31 23:59:59’,‘2007-12-30’);
-> 1
mysql> SELECT DATEDIFF(‘2010-11-30 23:59:59’,‘2010-12-31’);
-> -31
这个用法很简单，不必多说，希望能对小伙伴们起到帮助。