您好,登錄后才能下訂單哦!
這篇文章給大家介紹mysql中怎么實(shí)現(xiàn)時(shí)間相減功能,內(nèi)容非常詳細(xì),感興趣的小伙伴們可以參考借鑒,希望對(duì)大家能有所幫助。
先看一下錯(cuò)誤的現(xiàn)象如下,第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
全部測(cè)試腳本如下:
[sql]
--創(chuàng)建表
mysql> CREATE TABLE mytest (
t1 datetime,
t2 datetime
);
Query OK, 0 rows affected
--插入測(cè)試記錄
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
--驗(yàn)證結(jié)果
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
實(shí)際是mysql的時(shí)間相減是做了一個(gè)隱式轉(zhuǎn)換操作,將時(shí)間轉(zhuǎn)換為整數(shù),但并不是用unix_timestamp轉(zhuǎn)換,而是直接把年月日時(shí)分秒 拼起來,如2013-04-21 16:59:33 直接轉(zhuǎn)換為20130421165933,由于時(shí)間不是十進(jìn)制,所以最后得到的結(jié)果沒有意義,這也是導(dǎo)致上面出現(xiàn)坑爹的結(jié)果。
[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
要得到正確的時(shí)間相減秒值,有以下3種方法:
1、time_to_sec(timediff(t2, t1)),
2、timestampdiff(second, t1, t2),
3、unix_timestamp(t2) -unix_timestamp(t1)
[sql]
--測(cè)試腳本
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
這個(gè)問題2003年就有人在mysql4.0的版本時(shí)反饋,但mysql官方并不認(rèn)為是bug,因?yàn)樗麄冋J(rèn)為mysql并不支持時(shí)間直接相減操作,應(yīng)該用專用函數(shù)處理,所以一直沒有修正。但我認(rèn)為這個(gè)很容易導(dǎo)致使用錯(cuò)誤,要么就直接報(bào)錯(cuò),要么顯示正確的結(jié)果。
關(guān)于mysql中怎么實(shí)現(xiàn)時(shí)間相減功能就分享到這里了,希望以上內(nèi)容可以對(duì)大家有一定的幫助,可以學(xué)到更多知識(shí)。如果覺得文章不錯(cuò),可以把它分享出去讓更多的人看到。
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng),如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。