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.