使用MYSQL TIMESTAMP字段進行時間加減運算問題

目錄

• MYSQL TIMESTAMP字段進行時間加減運算
• 計算公式如下
• DATETIME 與 TIMESTAMP的區別
• 結論
• 參考文檔

MYSQL TIMESTAMP字段進行時間加減運算

在數據分析過程中，想當然地對TIMESTAMP字段進行運算，導致結果謬之千里

計算公式如下

-- create_time與week_time的聲明都是TIMESTAMP(), 要求精確到分鐘 --
SELECT (sa.create_time - sa.week_time)/(1000 * 60)
from alarm_sla_1 sa

當然正確的解法是利用timestampdiff函數，如下：

SELECT timestampdiff(minute, sa.create_time, sa.week_time)
from alarm_sla_1 sa

但有意思的問題在于，MYSQL明明支持減法操作，為何操作的結果又大相徑庭？

類似的問題還有，TIMESTAMP字段的時間精度是什么？

從MYSQL的官方實例中可以看到（請見后續的參考文檔），TIMESTAMP字段的小數部分確定了秒的經度，3位小數精確到毫秒，6位小數精確到微秒，如下：

聲明方式小數長度精度TIMESTAMP(3)3毫秒TIMESTAMP(6)6微秒

按照上面的推論，那么默認的聲明TIMESTAMP應該精確到秒，那么應該相減的結果應該得到秒，測試語句如下：

SELECT sa.week_time - sa.create_time, timestampdiff(second, sa.create_time, sa.week_time)
from alarm_sla_1 sa

但最后的結果見下表：

相減結果函數結果100001286412

顯然，并不存在相關性，差異何止里計？

后來繼續進行了指定經度的操作運算，結論依舊如此。

DATETIME 與 TIMESTAMP的區別

特性DATETIMETIMESTAMP時間范圍1000-01-01 00:00:00到9999-12-31 23:59:591970-01-01 00:00:01到2038-01-09 03:14:07存儲空間8+3(秒的精度)4+3(秒的精度)格式轉換不支持支持UTC多時區支持不支持，固定時區不支持創建索引不能能查詢后緩存結果否是

結論

MYSQL中TIMESTAMP字段直接進行相減操作，可能得到難以理解的結果，請慎用。

以上為個人經驗，希望能給大家一個參考，也希望大家多多支持。

參考文檔

• 日期與時間函數
• 秒的精度說明