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 31 DAY));
6

7
    INSERT INTO `tracking_archive` (`post_id`, `user_agent_id`, `ip_addr`,
8
                                    `time_created`, `is_cdn`, `request_uri`,
9
                                    `status_code`)
10
      SELECT
11
        COALESCE(`post_id`, 0) AS post_id,
12
        `user_agent_id`,
13
        `ip_addr`,
14
        `time_created`,
15
        `is_cdn`,
16
        `request_uri`,
17
        `status_code`
18
      FROM `tracking`
19
      WHERE `time_created` < max_date ORDER BY `id`;
20

21
    DELETE FROM `tracking` WHERE `time_created` < max_date;
22
  END
23