在 MySQL 中處理時間資料

以前，我會習慣用 PHP 的 date() 把時間字串整理好再放進資料庫，不然就是直接存 UNIX timestamp 。
直到前一陣子，我才發現 MySQL 有些巧妙的函式，方便我們處理理時間型態的資料。

如果在 TABLE_NAME 中，有個名為 TIME_COL 的欄位，其型態為 (unsigned) int，儲存的資料為 UNIX timestamp 。
那麼，在 MySQL 中，有些好用的 function 可供我們進行資料的操作：

• 新增一筆資料，其 TIME_COL 為現在時間：

  INSERT INTO `TABLE_NAME` (`TIME_COL`) VALUES ( UNIX_TIMESTAMP( NOW() ) );

• 這串 SQL 語法可以找出 TIME_COL 所紀錄的時間在五分鐘內的資料：

  SELECT * FROM `TABLE_NAME` WHERE `TIME_COL` > UNIX_TIMESTAMP( NOW() - INTERVAL 5 MINUTE );

• 這串 SQL 語法可以讓所有資料的 TIME_COL 值往後延 2 個月：

  UPDATE `TABLE_NAME` SET `TIME_COL`=UNIX_TIMESTAMP( FROM_UNIXTIME(`TIME_COL`, '%Y-%m-%d %H:%i:%s') + INTERVAL 2 MONTH ) WHERE 1;

如果 TIME_COL 欄位的型態為 datetime ，事情處理起來會更容易：

• 新增一筆資料，其 TIME_COL 為現在時間：

  INSERT INTO `TABLE_NAME` (`TIME_COL`) VALUES ( NOW() );

• 這串 SQL 語法可以找出 TIME_COL 所紀錄的時間在五分鐘內的資料：

  SELECT * FROM `TABLE_NAME` WHERE `TIME_COL` > NOW() - INTERVAL 5 MINUTE;

• 這串 SQL 語法可以讓所有資料的 TIME_COL 值往後延 2 個月：

  UPDATE `TABLE_NAME` SET `TIME_COL`=(`TIME_COL` + INTERVAL 2 MONTH) WHERE 1;

雖然 datetime 跟 timestamp 的資料顯示出來都是一樣的，但兩者是不同的，詳情可以參考 MySQL 官方手冊對 timestamp 資料型態的說明 。