MySQL

我在 之前的文章 提過 MySQL 裡面，協助我們處理時間資料的 functions，現在來講一下該注意的事。

可能很多人還沒發現到 Year 2038 Problem 。
2007 年 12 月，我在開發某個網頁系統時碰上了這個問題。

在 PHP5 裡面， mktime(11, 14, 7, 1, 19, 2038) 傳回 2147483647，mktime(11, 14, 8, 1, 19, 2038) 卻丟出空白值，而 2147483647 正好是 C 語言中，signed long int 的最大值。
而且，MySQL 的 UNIX timestamp 也有這種問題，以下這串語法傳回來的數值是 0 。

SELECT UNIX_TIMESTAMP( NOW( ) + INTERVAL 50 YEAR );

目前，就我測試過的部份，PHP4 與 JAVA 都沒有這種問題。
可是，我們不知道 PHP4 開發出來的系統，會不會被拿到 PHP5 上面跑。
所以，在 MySQL 裡面使用 datetime 或 timestamp 來紀錄時間資料，並搭配處理時間資料的 functions 來處理時間型資料，不僅方便我們辨識時間，也可以省去不必要的麻煩。

有些人可能會覺得，使用 datetime 或 timestamp 來紀錄時間資料，取出來的資料型態是字串，如果只需要年、月、日，就得作字串分割。
其實，MySQL 的 DATE_FORMAT 就足以應付這種狀況了。

最後，用 MySQL 處理 datetime 或 timestamp 型態的資料，請多注意一件事：

SELECT '2008-01-31 00:00:00' + INTERVAL 1 MONTH;

其結果為 2008-02-29 00:00:00。

SELECT '2008-01-31 00:00:00' + INTERVAL 30 DAY;

其結果則是 2008-03-01 00:00:00。

以上，我的報告完畢，謝謝收看。

以前，我會習慣用 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 資料型態的說明 。