1 2 3 4 5 6 7 8 9 10 11 12 13 14
| DELIMITER $$
DROP PROCEDURE IF EXISTS updateCache$$
CREATE PROCEDURE updateCache()
SQL SECURITY DEFINER
COMMENT 'Updates cache totals every night'
BEGIN
REPLACE INTO cache (name,value,updatetime)
select 'msg_sum', SUM(counter), NOW()
FROM logs;
REPLACE INTO cache (name,value,updatetime)
select CONCAT('chart_mpd_',DATE_FORMAT(NOW() - INTERVAL 1 DAY, '%Y-%m-%d_%a')), SUM(counter), NOW()
FROM logs
WHERE lo BETWEEN DATE_SUB(CONCAT(CURDATE(), ' 00:00:00'), INTERVAL 1 DAY) AND DATE_SUB(CONCAT(CURDATE(), ' 23:59:59'), INTERVAL 1 DAY);
END$$ |
Partager