[MySQL]시간 올림, 내림

DB 배치 스케쥴링 때문에 찾아본 팁.

참고 : https://stackoverflow.com/questions/14626982/rounding-time-up-to-nearest-6-minute-with-mysql

1. 내림 : 정각 분 구하기 - 2020-07-14 09:13:10 => 2020-07-14 09:13:00  

SELECT NOW(), DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i') + INTERVAL
   CASE
	WHEN TIME_FORMAT(NOW(),'%s') <=  59 THEN 0  -- round DOWN
   END MINUTE AS rnd_dt

응용.

DROP FUNCTION IF EXISTS FNC_BEFORE_MIN;

DELIMITER $$

CREATE FUNCTION FNC_BEFORE_MIN(before_min INT) RETURNS DATETIME
 
BEGIN
   DECLARE returnVal DATETIME;
 
   SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i') - INTERVAL
   CASE
	WHEN TIME_FORMAT(NOW(),'%s') <=  59 THEN before_min
   END MINUTE INTO returnVal;
   
   RETURN returnVal;
END $$
 
DELIMITER ;

SELECT *
FROM IOT_MCHN_INFO
WHERE cre_dt BETWEEN FNC_BEFORE_MIN(5) AND FNC_BEFORE_MIN(1) + INTERVAL 59 SECOND
 AND id IN ( SELECT MAX(id) FROM IOT_MCHN_INFO GROUP BY mchn_id )

2. 올림 : 정각 시 구하기 - 2020-07-14 09:13:10 => 2020-07-14 10:00:00

SELECT NOW(), DATE_FORMAT(NOW(),'%Y-%m-%d %H') + INTERVAL
   CASE
	WHEN TIME_FORMAT(NOW(),'%s') <=  59 THEN 1  -- round DOWN
   END HOUR AS rnd_dt

3. 10초 단위로 내림 : 2020-07-14 09:13:12 => 2020-07-14 09:13:10

SELECT NOW(), DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i') + INTERVAL
   CASE
	WHEN TIME_FORMAT(NOW(),'%s') <  10 THEN 0 
	WHEN TIME_FORMAT(NOW(),'%s') <  20 THEN 10
	WHEN TIME_FORMAT(NOW(),'%s') <  30 THEN 20
	WHEN TIME_FORMAT(NOW(),'%s') <  40 THEN 30
	WHEN TIME_FORMAT(NOW(),'%s') <  50 THEN 40
	WHEN TIME_FORMAT(NOW(),'%s') <=  59 THEN 50
   END SECOND AS rnd_dt