The usage of time difference function timestampdiff and DateDiff in MySQL

Time:2020-7-8

Usage of time difference function timestampdiff and DateDiff

When we write SQL statements, especially in stored procedures, we will frequently use the comparison and judgment of date and time. Then we will give an example of the usage of these two time difference comparison functions.

DateDiff function, the return value is the difference in days, can not be located to hours, minutes and seconds.

--The difference is 2 days
select datediff('2018-03-22 09:00:00', '2018-03-20 07:00:00');

Timestampdiff function, with parameter settings, can be accurate to day (day), hour (hour), minute (minute) and second (second), which is more flexible than DateDiff function. For the comparison of the two time, the small time is put in the front, and the large time is put in the back.

--One day difference
select TIMESTAMPDIFF(DAY, '2018-03-20 23:59:00', '2015-03-22 00:00:00');
--49 hours difference
select TIMESTAMPDIFF(HOUR, '2018-03-20 09:00:00', '2018-03-22 10:00:00');
--The difference is 2940 minutes
select TIMESTAMPDIFF(MINUTE, '2018-03-20 09:00:00', '2018-03-22 10:00:00');

--176400 seconds

select TIMESTAMPDIFF(SECOND, '2018-03-20 09:00:00', '2018-03-22 10:00:00');

Write in stored procedure:

Note: VAR_ Committime is the time when the previous business gets the time to be compared.

--Get current time
SET var_current_time = CONCAT(CURDATE(),' ',CURTIME());
--Time comparison
SET var_time_diff = TIMESTAMPDIFF(MINUTE, var_committime, var_current_time);

 
--Judge whether the unexamined contract has not been processed for more than 48 hours. If so, the subsequent logical processing will be carried out; otherwise, it will not be processed.
IF (var_time_diff > 2880) THEN

--Related business logic processing

END IF;

The above is the whole content of this article, I hope to help you in your study, and I hope you can support developeppaer more.