摘要:[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`