time_to_sec()函数仅仅计算了时间部分的秒数，没有把年月日的秒数计算进去。因为这个特点，导致了我程序的一个bug。

    这段代码本来想统计客服的响应时间。但是碰到这种极品数据的时候，例如， responseTime：2013-1-8 00:00:01 ，firstMsgTime: 2013-1-7 23:59:50，程序就返回负数了。

        SELECT service as pin, AVG(time_to_sec(responseTime) - time_to_sec(firstMsgTime)) as num
		FROM(	
			SELECT service, responseTime, firstMsgTime	
			FROM analysis_session
			WHERE 
				responseTime is not NULL
				AND created	>= DATE_ADD(DATE_FORMAT(now(),'%Y-%m-%d'),INTERVAL -1 DAY)
				AND created	< DATE_FORMAT(now() ,'%Y-%m-%d')
			GROUP BY cid
			) t
		GROUP BY service

     实际项目中只想将最近7天的记录保存在MySQL数据库中，使用程序通过SQL指令的方式删除比较麻烦且效率低，用Mysql 提供的事件调度器（event scheduler)可轻松实现。

具体步骤如下：

1：超级用户方式登陆MySQL console

# mysql　-uroot

2：打开event_scheduler(默认是关掉的）

mysql> set global event_scheduler = ON;

3：创建我们的事件（本例中命名为delete_old_record)

mysql> CREATE EVENT delete_old_record
　　　ON SCHEDULE EVERY 1 DAY STARTS NOW()
　　　DO
　　　　-- delete the old records of demo_1_table
　　　　DELETE FROM demo_1_table WHERE datediff(NOW(),log_timestamp)>=7;
　　　　-- delete the old records of puma_2_table
　　　　DELETE FROM demo_2_table WHERE datediff(NOW(),log_timestamp)>=7;
　　　　-- delete the old records of puma_3_table
　　　　DELETE FROM demo_3_table WHERE datediff(NOW(),log_timestamp)>=7;
　　　　-- delete the old records of puma_4_table
　　　　DELETE FROM demo_4_table WHERE datediff(NOW(),log_timestamp)>=7;

4：启动创建的事件

mysql> ALTER EVENT delete_old_record ENABLE;

这样以后，数据库就会每天执行DO后面的作业，删除各个table中7天之前的记录。

附录：CREATE EVENT的格式如下：

CREATE
    [DEFINER = { user | CURRENT_USER }]
    EVENT
    [IF NOT EXISTS]
    event_name
    ON SCHEDULE schedule
    [ON COMPLETION [NOT] PRESERVE]
    [ENABLE | DISABLE | DISABLE ON SLAVE]
    [COMMENT 'comment']
    DO event_body;

schedule:
    AT timestamp [+ INTERVAL interval] ...
    | EVERY interval
    [STARTS timestamp [+ INTERVAL interval] ...]
    [ENDS timestamp [+ INTERVAL interval] ...]

interval:
    quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
              WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
              DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}