Usage of MySQL time difference functions TIMESTAMPDIFF and DATEDIFF

Usage of time difference functions TIMESTAMPDIFF and DATEDIFF

When we write SQL statements, especially stored procedures, we frequently use comparisons and judgments on dates and times. Here is an example of how to use these two time difference comparison functions.

The datediff function returns the difference in days and cannot be localized to hours, minutes, and seconds.

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

The TIMESTAMPDIFF function has parameter settings and can be accurate to days (DAY), hours (HOUR), minutes (MINUTE), and seconds (SECOND). It is more flexible to use than the datediff function. When comparing two times, put the smaller one in front and the larger one in the back.

--Difference of 1 day select TIMESTAMPDIFF(DAY, '2018-03-20 23:59:00', '2015-03-22 00:00:00');
--The difference is 49 hours 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');

--The difference is 176400 seconds select TIMESTAMPDIFF(SECOND, '2018-03-20 09:00:00', '2018-03-22 10:00:00');

How to write in a stored procedure:

Note: var_committime is the time required for comparison obtained in the previous business.

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

 
-- Determine whether the unreviewed contract has not been processed for more than 48 hours. If so, perform subsequent logical processing, otherwise do not process it.
IF (var_time_diff > 2880) THEN

-- Related business logic processing END IF;

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.