＜数据库＞ datediff、timestampdiff函数的区别

对日期范围搜索的大致有三种方式：

1、between and

2、datediff

3、timestampdiff

between and

select * from dat_document where commit_date between '2018-07-01' and '2018-07-04' 

结果是1号到3号的数据，因为时间范围显示的实际上只是‘2018-07-01 00:00:00’到'2018-07-04 00:00:00'之间的数据，而'2018-07-04'的数据无法显示出来,between and对边界还需要进行额外的处理

datediff

返回两个日期之间的天数

SELECT DATEDIFF('2018-07-01','2018-07-04');

运行结果:-3

DATEDIFF(date1,date2)

datediff函数对时间差值的计算方式为date1-date2的差值

其他运行例子：

SELECT DATEDIFF(day,'2008-12-29','2008-12-30') AS DiffDate
//结果 1
SELECT DATEDIFF(day,'2008-12-30','2008-12-29') AS DiffDate
//结果 -1

timestampdiff

timestampdiff函数日期或日期时间表达式之间的整数差。

TIMESTAMPDIFF(interval，datetime1，datetime2)，比较的单位interval可以为以下数值

FRAC_SECOND。表示间隔是毫秒
SECOND。秒
MINUTE。分钟
HOUR。小时
DAY。天
WEEK。星期
MONTH。月
QUARTER。季度
YEAR。年
 

select TIMESTAMPDIFF(DAY,'2018-07-01 09:00:00','2018-07-04 12:00:00');

运行结果:3 

timestampdiff函数对日期差值的计算方式为datetime2-datetime1的差值

其他运行例子：

select timestampdiff(YEAR,"2018-01-01 15:15:16","2019-08-23 15:15:16") as timestamodiff;
//结果1。相差一年。
select timestampdiff(YEAR,"2019-08-22 15:15:19","2018-12-23 15:15:16") as timestamodiff;
//结果 -1，也是相差一年。
select timestampdiff(YEAR,"2019-08-22 15:15:19","2018-08-22 15:15:16") as timestamodiff; 
//结果0，相差不到一年

select timestampdiff(MONTH,"2018-08-22 15:15:16","2018-07-23 15:15:16") as timestamodiff;
// 结果 0 相差不到一个月
select timestampdiff(MONTH,"2018-08-22 15:15:16","2018-07-01 15:15:16") as timestamodiff;
// 结果 -1 相差一个月
select timestampdiff(MONTH,"2018-08-22 15:15:19","2018-09-23 15:15:16") as timestamodiff; 
// 结果 1 想差一个月

select timestampdiff(DAY,"2018-08-22 15:15:16","2018-08-23 15:15:16") as timestamodiff;
// 结果 1 想差一天
select timestampdiff(DAY,"2018-08-22 15:15:19","2018-08-23 15:15:16") as timestamodiff;
// 结果 0 想差不到一天
select timestampdiff(DAY,"2018-08-23 15:15:19","2018-08-22 15:15:16") as timestamodiff;
// 结果 -1 想差一天

注意：DATEDIFF，TIMESTAMPDIFF对日期差值的计算方式是相反的！