zend-blog-3-backend

1
CREATE PROCEDURE tracking_to_archive()
2
  BEGIN
3
    DECLARE max_date DATETIME;
4

5
    SET max_date = (SELECT DATE_SUB(CURRENT_TIMESTAMP(), INTERVAL 15 DAY));
6

7
    INSERT INTO `tracking_archive` (`post_id`, `user_agent_id`, `ip_addr`, `time_created`)
8
      SELECT
9
        CASE WHEN `post_id` IS NOT NULL THEN `post_id` ELSE 0 END AS post_id,
10
        `user_agent_id`,
11
        `ip_addr`,
12
        `time_created`
13
      FROM `tracking`
14
      WHERE `time_created` < max_date ORDER BY `id`;
15

16
    DELETE FROM `tracking` WHERE `time_created` < max_date;
17
  END
18