Usage of time difference functions TIMESTAMPDIFF and DATEDIFF of MySQL
- 2021-12-21 05:23:47
Usage of time difference functions TIMESTAMPDIFF and DATEDIFF
When we write sql statements, especially stored procedures, we frequently use the comparison and judgment of date and time, so give an example to introduce the usage of these two time difference comparison functions.
datediff function, return value is the difference of days, cannot navigate to hours, minutes, and seconds.
-- Difference 2 Days select datediff('2018-03-22 09:00:00', '2018-03-20 07:00:00');
The TIMESTAMPDIFF function, with parameter settings, can be accurate to days (DAY), hours (HOUR), minutes (MINUTE) and seconds (SECOND), and is more flexible to use than the datediff function. For the two comparative times, the smaller one is put in front and the larger one is put in the back.
-- Difference 1 Days select TIMESTAMPDIFF(DAY, '2018-03-20 23:59:00', '2015-03-22 00:00:00'); -- Difference 49 Hours select TIMESTAMPDIFF(HOUR, '2018-03-20 09:00:00', '2018-03-22 10:00:00'); -- Difference 2940 Minutes select TIMESTAMPDIFF(MINUTE, '2018-03-20 09:00:00', '2018-03-22 10:00:00'); -- Difference 176400 Seconds select TIMESTAMPDIFF(SECOND, '2018-03-20 09:00:00', '2018-03-22 10:00:00');
How to write in stored procedures:
Note: var_committime is the time obtained in the previous business that needs to be compared.
-- Get the 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 unaudited contract exceeds 48 Hours are not processed. If it exceeds, 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;