关于Mysql时间相减问题的bug

        今天在写程序时，当想挑选出（当前时间-最后通信时间超过2分钟)的服务器时，发现一个问提，减出来的结果不是我所预期的那样。然后，通过测试，发现，原来mysql会有一个隐形的装换，将时间戳转换成整形类型，再进行相减，而不是以unix_timestamp类型进行加减。特地做个记录，防止以后再发生类似事件。

例如，我创建一个表，time-test  创建两列，time1，time2.以timestamp为时间类型

mysql> desc time_test;
+-------+-------------+------+-----+---------------------+-------+
| Field | Type        | Null | Key | Default             | Extra |
+-------+-------------+------+-----+---------------------+-------+
| time1 | timestamp   | NO   |     | CURRENT_TIMESTAMP   |       |
| time2 | timestamp   | NO   |     | 0000-00-00 00:00:00 |       |
| ps    | varchar(20) | NO   |     | 添加备注信息        |       |
+-------+-------------+------+-----+---------------------+-------+
3 rows in set (0.00 sec)

然后插入3行数据

mysql> insert into time_test(time1,time2) values('2015-05-07 09:19:20','2015-05-07 09:19:50');
Query OK, 1 row affected (0.01 sec)

mysql> insert into time_test(time1,time2) values('2015-05-07 09:19:20','2015-05-07 09:21:50');
Query OK, 1 row affected (0.00 sec)

mysql> insert into time_test(time1,time2) values('2015-05-07 09:19:20','2015-05-07 09:21:20');
Query OK, 1 row affected (0.00 sec)

让我们来验证一下结果：

mysql> select time1,time2,(time2-time1)from time_test;
+---------------------+---------------------+---------------+
| time1               | time2               | (time2-time1) |
+---------------------+---------------------+---------------+
| 2015-05-07 09:19:20 | 2015-05-07 09:19:50 |            30 |
| 2015-05-07 09:19:20 | 2015-05-07 09:21:50 |           230 |
| 2015-05-07 09:19:20 | 2015-05-07 09:21:20 |           200 |
+---------------------+---------------------+---------------+
3 rows in set (0.00 sec)

你会发现，如果在60s内的时间差，是正确的，当时间差>60s后，你会发现他的计算方式：(20150507092150-20150507091920=230) (20150507092120-20150507091920=200s)，而不是按照时间类型以60进制进行计算。这里非常容易产生误差，导致程序出错

解决方案：
要按照正确的时间戳类型进行减，可以使用mysql 支持的函数”

         1. time_to_sec(timediff(time2,time1)),

         2. timestampdiff(second,time2,time1),

         3. unix_timestamp(time2) - unix_timestamp(time1)

mysql> select time1 , time2  , time2-time1  ,  time_to_sec(timediff(time2,time1))  diff1 , 

              timestampdiff(second,time1,time2) diff2   ,   unix_timestamp(time2)-unix_timestamp(time1) diff3    from time_test;
+---------------------+---------------------+-------------+-------+-------+-------+
| time1               | time2               | time2-time1 | diff1 | diff2 | diff3 |
+---------------------+---------------------+-------------+-------+-------+-------+
| 2015-05-07 09:19:20 | 2015-05-07 09:19:50 |          30 |    30 |    30 |    30 |
| 2015-05-07 09:19:20 | 2015-05-07 09:21:50 |         230 |   150 |   150 |   150 |
| 2015-05-07 09:19:20 | 2015-05-07 09:21:20 |         200 |   120 |   120 |   120 |
+---------------------+---------------------+-------------+-------+-------+-------+
3 rows in set (0.00 sec)

从上面的结果可以看出当使用3个函数时，mysql会以unix_timestamp类型进行相减，而不是已整形形式进行相减。