在 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 資料型態的說明