[MySQL]MySQL計算時間相差秒數

摘要:[MySQL]MySQL計算時間相差秒數

最近再弄記錄IP的活,在撰寫的過程中就想到,要是我要跟資料庫比對,直接在SQL語法內做判斷的話,不知可不可以?
 
查詢Google大神,看到有人提供語法TIMESTAMPDIFF
 
查詢了一下使用方法
TIMESTAMPDIFF():TIMESTAMPDIFF(unit,datetime1,datetime2)
unit:所要取得的單位(SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR)
datetime1:扣除時間的值
datetime2:將『被』扣除時間的值
 
直接來個Example比較有感覺,也比較可以理解
資料表欄位&範例資料



以秒計算

SELECT TIMESTAMPDIFF(second,`datetime1`,`datetime2`) AS timestampdiff_view  FROM `date_table`


以分計算

SELECT TIMESTAMPDIFF(minute,`datetime1`,`datetime2`) AS timestampdiff_view FROM `date_table`


以時計算

SELECT TIMESTAMPDIFF(hour,`datetime1`,`datetime2`) AS timestampdiff_view FROM `date_table`


以天計算

SELECT TIMESTAMPDIFF(day,`datetime1`,`datetime2`) AS timestampdiff_view FROM `date_table`


加入if判斷

SELECT if(TIMESTAMPDIFF(day,`datetime1`,`datetime2`) > 3, '有超過3天', '沒超過3天') AS timestampdiff_view FROM `date_table`