面試官：你知道mysql的時間直接相減有一個bug嗎？

概述

今天主要介紹一下mysql時間日期轉換會涉及的一些函數及日期相減的問題，這裡做個總結。

一、將時間轉換為時間戳

 select unix_timestamp('2020-01-05 10-06-07');

如果參數為空，則處理為當前時間

二、將時間戳轉換為時間

 select from_unixtime(1578189967);

有些應用生成的時間戳是比這個多出三位，是毫秒表示，如果要轉換，需要先將最後三位去掉，否則返回NULL

三、mysql時間相減

mysql的時間相減是做了一個隱式轉換操作，將時間轉換為整數，但並不是用unix_timestamp轉換，而是直接把年月日時分秒拼起來，如2019-12-21 16:59:33 直接轉換為20191221165933，由於時間不是十進位，所以直接時間相減得到的結果沒有意義。

要得到正確的時間相減秒值，有以下3種方法：
1、time_to_sec(timediff(t2, t1)),

測試腳本如下：

--創建表  
CREATE TABLE mytest (  
  t1 datetime,  
  t2 datetime );  
--插入測試記錄  
insert into mytest(t1,t2) values('2020-01-05 16:59:33','2020-01-05 16:59:43');  
insert into mytest(t1,t2) values('2020-01-05 16:59:33','2020-01-05 17:00:33');  
insert into mytest(t1,t2) values('2020-01-05 16:59:33','2020-01-05 17:59:35');  
--測試
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;  

其實這個問題2003年就有人在mysql4.0的版本時反饋，但mysql官方並不認為是bug，因為他們認為mysql並不支持時間直接相減操作，應該用專用函數處理，所以一直沒有修正。

四、計算兩個日期相差的秒數、分鐘數、小時數、天數、周數、季度數、月數、年數

MySQL自帶的日期函數TIMESTAMPDIFF計算兩個日期相差的秒數、分鐘數、小時數、天數、周數、季度數、月數、年數，當前日期增加或者減少一天、一周等等。

 SELECT TIMESTAMPDIFF(類型,開始時間,結束時間)

實例：

SELECT '相差的秒數' as '相差時間',TIMESTAMPDIFF(SECOND,'1993-03-23 00:00:00',DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%S')) as 'TIMESTAMPDIFF(類型,開始時間,結束時間)'
union all 
SELECT '相差的分鐘數',TIMESTAMPDIFF(MINUTE,'1993-03-23 00:00:00',DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%S'))
union all
SELECT '相差的小時數',TIMESTAMPDIFF(HOUR,'1993-03-23 00:00:00',DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%S'))
union all
SELECT '相差的天數',TIMESTAMPDIFF(DAY,'1993-03-23 00:00:00',DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%S'))
union all
SELECT '相差的周數',TIMESTAMPDIFF(WEEK,'1993-03-23 00:00:00',DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%S'))
union all
SELECT '相差的周數',TIMESTAMPDIFF(WEEK,'1993-03-23 00:00:00',DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%S'))
union all
SELECT '相差的季度數',TIMESTAMPDIFF(QUARTER,'1993-03-23 00:00:00',DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%S'))
union all
SELECT '相差的月數',TIMESTAMPDIFF(MONTH,'1993-03-23 00:00:00',DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%S'))
union all
SELECT '相差的年數',TIMESTAMPDIFF(YEAR,'1993-03-23 00:00:00',DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%S'))

五、日期增加減指定時間間隔

DATE_SUB() 函數用於從日期減去指定的時間間隔。

語法

DATE_SUB(date,INTERVAL expr type)

date 參數是合法的日期表達式。expr 參數是您希望添加的時間間隔。

type 參數可以是下列值：

實例：

--當前日期增加一天
SELECT DATE_SUB(CURDATE(),INTERVAL -1 DAY);
--當前日期減少一天
SELECT DATE_SUB(CURDATE(),INTERVAL 1 DAY);
--當前日期增加一周
SELECT DATE_SUB(CURDATE(),INTERVAL -1 WEEK);
當前日期增加一月
SELECT DATE_SUB(NOW(),INTERVAL -1 MONTH);

覺得有用的朋友多幫忙轉發哦！後面會分享更多devops和DBA方面的內容，感興趣的朋友可以關注下~