老废物乐园

Personal Website of Hisashi Egawa


Mysql时间相减的问题

今天弄mySQL的时候需要计算两个时间点的间隔,于是就上网搜了一下计算方法,没想到里面藏着个小坑……
原文:mysql时间相减的问题(bug)
先看一下错误的现象如下,第1条正确,第2,3条的t2-t1不正确:

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,由于时间不是十进制,所以最后得到的结果没有意义,这也是导致上面出现坑爹的结果。

要得到正确的时间相减秒值,有以下3种方法:

  • 1、time_to_sec(timediff(t2, t1)),
  • 2、timestampdiff(second, t1, t2),
  • 3、unix_timestamp(t2) -unix_timestamp(t1)

      --测试脚本
      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并不支持时间直接相减操作,应该用专用函数处理,所以一直没有修正。但我认为这个很容易导致使用错误,要么就直接报错,要么显示正确的结果。

这个世界上并没有BUG,只有不能输出预期结果的代码。

最近的文章

Python初始化字典时指定键

newdic = dict().fromkeys(['key1', 'key2','key3','key4'])…

继续阅读
更早的文章

十一月四日夜杂谈

甲午之战,不是日本必胜,而是清朝必败。从生产力的角度说,日本并没有比中国先进到哪里去。即使是军备上存在代际差距日本也不可能完全战胜中国。甲午之战日本胜中国败最碰巧的就是日本成功建立起了中央集权,而清朝的中央集权恰好因为太平天国战争大大削弱了。一个反面例子是万历朝鲜之战:一边是腐朽的中央集权大国,另一边是刚刚统一的封建小国,双方短时间内打成平手,但是即使丰臣秀吉不死打下去日本也必败。比起白村江之战,万里之战中日两国科技水平是比较接近的,即在同样的科技水平,封建制不可能打垮中央集权。黄海之战北...…

继续阅读