mysql时间相减的问题

ㄣ负二代

今天看到一条微博，提到mysql日期相减的错误结果，以前没有怎么注意，于是测试了一下，发现确实很坑爹，很容易踩雷，于是整理博客提醒一下大家。
　　先看一下错误的现象如下，第1条正确，第2，3条的t2-t1不正确：
　　[sql]
　　mysql> select t1,t2,t2-t1 from mytest;
　　+---------------------+---------------------+-------+
　　| t1                  | t2                  | t2-t1 |
　　+---------------------+---------------------+-------+
　　| 2013-04-21 16:59:33 | 2013-04-21 16:59:43 |    10 |
　　| 2013-04-21 16:59:33 | 2013-04-21 17:00:33 |  4100 |
　　| 2013-04-21 16:59:33 | 2013-04-21 17:59:35 | 10002 |
　　+---------------------+---------------------+-------+
　　3 rows in set
　　全部测试脚本如下：
　　[sql]
　　--创建表
　　mysql> CREATE TABLE mytest (
　　t1 datetime,
　　t2 datetime
　　);
　　Query OK, 0 rows affected
　　--插入测试记录
　　mysql> insert into mytest(t1,t2) values('2013-04-21 16:59:33','2013-04-21 16:59:43');
　　Query OK, 1 row affected
　　mysql> insert into mytest(t1,t2) values('2013-04-21 16:59:33','2013-04-21 17:00:33');
　　Query OK, 1 row affected
　　mysql> insert into mytest(t1,t2) values('2013-04-21 16:59:33','2013-04-21 17:59:35');
　　Query OK, 1 row affected
　　--验证结果
　　mysql> select t1,t2,t2-t1 from mytest;
　　+---------------------+---------------------+-------+
　　| t1                  | t2                  | t2-t1 |
　　+---------------------+---------------------+-------+
　　| 2013-04-21 16:59:33 | 2013-04-21 16:59:43 |    10 |
　　| 2013-04-21 16:59:33 | 2013-04-21 17:00:33 |  4100 |
　　| 2013-04-21 16:59:33 | 2013-04-21 17:59:35 | 10002 |
　　+---------------------+---------------------+-------+
　　3 rows in set
　　实际是mysql的时间相减是做了一个隐式转换操作，将时间转换为整数，但并不是用unix_timestamp转换，而是直接把年月日时分秒 拼起来，如2013-04-21 16:59:33 直接转换为20130421165933，由于时间不是十进制，所以最后得到的结果没有意义，这也是导致上面出现坑爹的结果。
　　[sql]
　　mysql> select t1,
　　t2,
　　convert(t1, UNSIGNED INTEGER) ct1,
　　convert(t2, UNSIGNED INTEGER) ct2,
　　t2-t1,
　　convert(t2, UNSIGNED INTEGER) -convert(t1, UNSIGNED INTEGER) diff0
　　from mytest;
　　+-------------------+-------------------+--------------+--------------+-----+-----+
　　|t1                 |t2                 |ct1           |ct2           |t2-t1|diff0|
　　+-------------------+-------------------+--------------+--------------+-----+-----+
　　|2013-04-21 16:59:33|2013-04-21 16:59:43|20130421165933|20130421165943|   10|   10|
　　|2013-04-21 16:59:33|2013-04-21 17:00:33|20130421165933|20130421170033| 4100| 4100|
　　|2013-04-21 16:59:33|2013-04-21 17:59:35|20130421165933|20130421175935|10002|10002|
　　+-------------------+-------------------+--------------+--------------+-----+-----+
　　3 rows in set
　　要得到正确的时间相减秒值，有以下3种方法：
　　1、time_to_sec(timediff(t2, t1)),
　　2、timestampdiff(second, t1, t2),
　　3、unix_timestamp(t2) -unix_timestamp(t1)
　　[sql]
　　--测试脚本
　　mysql> select t1,
　　t2,
　　t2-t1,
　　time_to_sec(timediff(t2, t1)) diff1,
　　timestampdiff(second, t1, t2) diff2,
　　unix_timestamp(t2) -unix_timestamp(t1) diff3
　　from mytest;
　　+---------------------+---------------------+-------+-------+-------+-------+
　　| t1                  | t2                  | t2-t1 | diff1 | diff2 | diff3 |
　　+---------------------+---------------------+-------+-------+-------+-------+
　　| 2013-04-21 16:59:33 | 2013-04-21 16:59:43 |    10 |    10 |    10 |    10 |
　　| 2013-04-21 16:59:33 | 2013-04-21 17:00:33 |  4100 |    60 |    60 |    60 |
　　| 2013-04-21 16:59:33 | 2013-04-21 17:59:35 | 10002 |  3602 |  3602 |  3602 |
　　+---------------------+---------------------+-------+-------+-------+-------+
　　3 rows in set
　　这个问题2003年就有人在mysql4.0的版本时反馈，但mysql官方并不认为是bug，因为他们认为mysql并不支持时间直接相减操作，应该用专用函数处理，所以一直没有修正。但我认为这个很容易导致使用错误，要么就直接报错，要么显示正确的结果。

亦惜风流

太神熬了  直接没看懂

宇智波·佐助

亦惜风流 发表于 2013-5-2 19:38
太神熬了  直接没看懂

我还有板凳

1170079

xiaoyuifeifa

回复一下看看