Usage of time difference functions TIMESTAMPDIFF and DATEDIFF of MySQL

  • 2021-12-21 05:23:47
  • OfStack

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;


Related articles: