[SQL與PHP] php mysql date 日期時間儲存與轉換

gswdkimo

在php說到時間，應該都會想到time與date這兩個函式。time函式用來取得程式執行時的unix timestamp，也就是由1970/01/01 00:00:00到執行時累計的秒數。timestamp理論上全世界所有的電腦同時間應該都同值。

1. echo time();
   
2. // 測試時得整數1293602496

複製代碼

echo time();
// 測試時得整數1293602496

date則是取unix timestamp，轉換為一般熟悉的日期格式，例如 2010-12-01 12:00:00。
string date ( string $format [, int $timestamp ] )

1. echo date('Y-m-d H:i:s', time());
   
2. // 2010-12-29 14:01:36
   
3. // time()可省略，執行時的當下unix timestamp代入得結果。

複製代碼

echo date('Y-m-d H:i:s', time());
// 2010-12-29 14:01:36
// time()可省略，執行時的當下unix timestamp代入得結果。

所以，php的時間是以unix timestamp當儲存值(整數)
unix timestamp 在32位元作業系統有2147483647最高值限制，所以只能記錄到2038年1月19日11點14分07秒(UTC+8)，64位元作業系統則無此限制。

MySQL的日期儲存格式常用的有三種 DATETIME, DATE 與TIMESTAMP。
DATETIME 將日期時間以 YYYY-MM-DD HH:MM:SS，等同php date('Y-m-d H:i:s')。DATE 則只記錄日期部份的 YYYY-MM-DD，等同php date('Y-m-d')。TIMESTAMP雖然看起來與unix timestamp類似，但MySQL TIMESTAMP格式與DATETIME相同，欄位值會在新增或修改為當下時間。

所以，如果有一php日期時間，要怎麼儲存到MySQL table欄位呢？

   1. 以unix timestamp整數格式讀寫
          * 優點：簡單，欄位為整數值，php無需做格式轉換(1293602496<->2010-12-29 14:01:36)
          * 缺點：因為欄位是整數，無法直接利用MySQL的日期函式，例如：YEAR(年資料)、MONTH(月資料)、DATEOFWEEK、DATEOFYEAR。
   2. 以Y-m-d H:i:s日期時間格式讀寫
          * 優點：日期時間容易辦視，可利用MySQL日期函式，在資料庫做好資料彙整(排序、年、月、週資料)。
          * 缺點：需在php或MySQL做格式轉換
                o PHP：利用date、strtotime

                  $mysqldate = date( 'Y-m-d H:i:s', $php_unix_timestamp );
                  $php_unix_timestamp = strtotime( $mysqldate );

                o MySQL：利用FROM_UNIXTIME、UNIX_TIMESTAMP

                  $query = "UPDATE table SET datetimefield = FROM_UNIXTIME($php_unix_timestamp ) WHERE...";
                  $query = "SELECT UNIX_TIMESTAMP(datetimefield) FROM table WHERE...";