lTIMESTAMPDIFF实现预警功能

select
tt.prjId,
TT.prjName,
TT.warningTime,
TIMESTAMPDIFF(DAY,TT.warningTime,DATE_FORMAT(NOW(),’%Y-%m-%d %H:%i:%S’)) as aa,
– TIMESTAMPDIFF(DAY,TT.warningTime,DATE_FORMAT(NOW(), ‘%Y-%m-%d %H:%i:%S’)),
if(TIMESTAMPDIFF(DAY,TT.warningTime,DATE_FORMAT(NOW(), ‘%Y-%m-%d %H:%i:%S’)) >= 30,
30 - TIMESTAMPDIFF(DAY,TT.warningTime,DATE_FORMAT(NOW(), ‘%Y-%m-%d %H:%i:%S’)), TIMESTAMPDIFF(DAY,TT.warningTime,DATE_FORMAT(NOW(), ‘%Y-%m-%d %H:%i:%S’)) -27) as warningNumber
from (
SELECT
T.PRJ_ID as prjId,
T.PRJ_NAME as prjName,
IF
( T3.SUPERVISION_ID IS NULL, T.AUDIT_DATE, max( T3.CREATE_TIME ) ) AS warningTime
FROM
project_build_info T
LEFT JOIN project_ctrl_ent T2 ON T.PRJ_CTRL_ENT = T2.CTRL_ENT_ID
LEFT JOIN project_daily_supervision T3 ON T3.PRJ_ID = T.PRJ_ID
WHERE
1 = 1
GROUP BY
T.PRJ_ID )tt
where (TIMESTAMPDIFF(DAY,TT.warningTime,DATE_FORMAT(NOW(), ‘%Y-%m-%d %H:%i:%S’)) >27)