2021-01-29

我们在写sql语句，尤其是存储过程中，会频繁用到对于日期、时间的比较和判断，下面对于这三个时间差比较函数用法做一个举例介绍.

一、timestampdiff

语法： timestampdiff(interval, datetime1,datetime2)
结果： 返回（时间2-时间1）的时间差，结果单位由interval参数给出。

SELECT * FROM student WHERE TIMESTAMPDIFF(YEAR,student.Sage,CURDATE()) < 10;
SELECT TIMESTAMPDIFF(SECOND,'2020-03-20 09:00:00','2020-03-22 07:00:00'); # 165600
SELECT TIMESTAMPDIFF(MINUTE,'2020-03-20 09:00:00','2020-03-22 07:00:00'); # 2760
SELECT TIMESTAMPDIFF(HOUR,'2020-03-20 09:00:00','2020-03-22 07:00:00'); # 46
SELECT TIMESTAMPDIFF(DAY,'2020-03-20 09:00:00','2020-03-22 07:00:00'); # 1
SELECT TIMESTAMPDIFF(WEEK,'2012-10-01','2013-01-13'); # 14
SELECT TIMESTAMPDIFF(MONTH,'2012-10-01','2013-01-13'); # 3
SELECT TIMESTAMPDIFF(QUARTER,'2012-10-01','2013-01-13'); # 1
SELECT TIMESTAMPDIFF(YEAR,'2012-10-01','2013-01-13'); # 0

二、timediff

语法： timediff(time1,time2)

**结果：**返回两个时间相减得到的差值，time1-time2

SELECT TIMEDIFF('2018-08-21 14:51:43','2018-08-19 12:54:43'); #49:57:00

三、datediff

datediff函数，返回值是相差的天数，不能定位到小时、分钟和秒
语法： 传入两个日期参数，比较DAY天数，第一个参数减去第二个参数的天数值。

select datediff('2020-03-20 09:00:00','2020-03-22 07:00:00'); # -2