使用MYSQL TIMESTAMP字段進行時間加減運算問題
MYSQL TIMESTAMP字段進行時間加減運算
在數(shù)據(jù)分析過程中,想當然地對TIMESTAMP字段進行運算,導致結(jié)果謬之千里
計算公式如下
-- create_time與week_time的聲明都是TIMESTAMP(), 要求精確到分鐘 -- SELECT (sa.create_time - sa.week_time)/(1000 * 60) from alarm_sla_1 sa
當然正確的解法是利用timestampdiff函數(shù),如下:
SELECT timestampdiff(minute, sa.create_time, sa.week_time) from alarm_sla_1 sa
但有意思的問題在于,MYSQL明明支持減法操作,為何操作的結(jié)果又大相徑庭?
類似的問題還有,TIMESTAMP字段的時間精度是什么?
從MYSQL的官方實例中可以看到(請見后續(xù)的參考文檔),TIMESTAMP字段的小數(shù)部分確定了秒的經(jīng)度,3位小數(shù)精確到毫秒,6位小數(shù)精確到微秒,如下:
聲明方式 | 小數(shù)長度 | 精度 |
---|---|---|
TIMESTAMP(3) | 3 | 毫秒 |
TIMESTAMP(6) | 6 | 微秒 |
按照上面的推論,那么默認的聲明TIMESTAMP應該精確到秒,那么應該相減的結(jié)果應該得到秒,測試語句如下:
SELECT sa.week_time - sa.create_time, timestampdiff(second, sa.create_time, sa.week_time) from alarm_sla_1 sa
但最后的結(jié)果見下表:
相減結(jié)果 | 函數(shù)結(jié)果 |
---|---|
1000012 | 86412 |
顯然,并不存在相關性,差異何止里計?
后來繼續(xù)進行了指定經(jīng)度的操作運算,結(jié)論依舊如此。
DATETIME 與 TIMESTAMP的區(qū)別
特性 | DATETIME | TIMESTAMP |
---|---|---|
時間范圍 | 1000-01-01 00:00:00到9999-12-31 23:59:59 | 1970-01-01 00:00:01到2038-01-09 03:14:07 |
存儲空間 | 8+3(秒的精度) | 4+3(秒的精度) |
格式轉(zhuǎn)換 | 不支持 | 支持UTC |
多時區(qū)支持 | 不支持,固定時區(qū) | 不支持 |
創(chuàng)建索引 | 不能 | 能 |
查詢后緩存結(jié)果 | 否 | 是 |
結(jié)論
MYSQL中TIMESTAMP字段直接進行相減操作,可能得到難以理解的結(jié)果,請慎用。
以上為個人經(jīng)驗,希望能給大家一個參考,也希望大家多多支持腳本之家。
參考文檔
相關文章
mysql 導出select語句結(jié)果到excel文件遇到問題及解決方法
這篇文章主要介紹了mysql 導出select語句結(jié)果到excel文件遇到問題及解決方法的相關資料,非常不錯,具有參考借鑒價值,需要的朋友可以參考下2016-09-09Mysql中replace與replace into的用法講解
今天小編就為大家分享一篇關于Mysql中replace與replace into的用法講解,小編覺得內(nèi)容挺不錯的,現(xiàn)在分享給大家,具有很好的參考價值,需要的朋友一起跟隨小編來看看吧2019-03-03MySQL 不用存儲過程循環(huán)插入數(shù)據(jù)的方法
在MySQL中,使用INSERT INTO VALUES語句可以一次性插入多行數(shù)據(jù),提高插入效率,還可通過Python的pymysql庫生成和執(zhí)行插入語句,這不僅減少了操作時間,還提高了代碼的簡潔性和執(zhí)行效率2024-09-09