zend-blog-3-backend
25 строк · 816.0 Байт
1CREATE PROCEDURE tracking_to_archive()
2BEGIN
3DECLARE max_date DATETIME;
4
5SET max_date = (SELECT DATE_SUB(CURRENT_TIMESTAMP(), INTERVAL 31 DAY));
6
7INSERT INTO `tracking_archive` (`post_id`, `user_agent_id`, `ip_addr`,
8`time_created`, `is_cdn`, `request_uri`,
9`ip_long`, `duration`, `method`, `status_code`)
10SELECT
11COALESCE(`post_id`, 0) AS post_id,
12`user_agent_id`,
13`ip_addr`,
14`time_created`,
15`is_cdn`,
16`request_uri`,
17`ip_long`,
18IF(`duration` > 0, `duration`, NULL) AS duration,
19`method`,
20`status_code`
21FROM `tracking`
22WHERE `time_created` < max_date ORDER BY `id`;
23
24DELETE FROM `tracking` WHERE `time_created` < max_date;
25END
26