MySQL时间操作与转换完全指南

t_asset_check_result 基线
t_vul_found          脆弱性时间
SELECT * FROM t_asset_check_result_bak
SELECT ADDDATE(NOW(), INTERVAL 24 HOUR)
SELECT SUBDATE(NOW(), INTERVAL 1  HOUR)
SELECT SUBDATE(NOW(), INTERVAL 1  WEEK)
SELECT DATE_FORMAT(NOW(),'%Y-%m-%d')
SELECT DATE_FORMAT(NOW(),'%Y-%u')
/**基线天**/ 
SELECT  CONCAT (DATE_FORMAT(model.ENTER_DATE,'%Y-%m-%d %H'),':00:00') label,
COUNT(1) VALUE
FROM    t_asset_check_result_bak model
LEFT  JOIN  t_asset_dict asset ON asset.ASSET_ID =  model.ASSET_ID
WHERE   1 = 1
AND     asset.asset_status = 1
AND     model.is_security = 1
AND     model.ENTER_DATE BETWEEN SUBDATE(NOW(), INTERVAL 25  HOUR)  AND  SUBDATE(NOW(), INTERVAL 1 HOUR)
GROUP   BY  label
/**脆弱性事件天**/
SELECT CONCAT (DATE_FORMAT(model.ENTER_DATE,'%Y-%m-%d %H'),':00:00') label,
       COUNT(1)  VALUE
FROM   t_vul_found_bak  model
LEFT  JOIN  t_asset_dict asset ON asset.ASSET_ID =  model.ASSET_ID
WHERE  asset.asset_status = 1
AND    model.ENTER_DATE BETWEEN SUBDATE(NOW(),  INTERVAL 25  HOUR)  AND  SUBDATE(NOW(), INTERVAL 1 HOUR)
GROUP  BY label
/**基线周**/
SELECT  DATE_FORMAT(model.ENTER_DATE,'%Y-%m-%d') label,
COUNT(1) VALUE
FROM    t_asset_check_result_bak model