MySQL 時間戳轉換函數 unix_timestamp 與 from_unixtime 完整指南

MySQL 提供 unix_timestamp() 與 from_unixtime() 兩大核心函數，實現「日期時間 ⇄ Unix 時間戳（秒數）」雙向轉換。本文詳細說明語法、參數、格式化輸出、範圍限制，並補充 PHP 對應函數供跨語言參考。

一、unix_timestamp：日期時間 → 時間戳

1. 無參數：取得當前時間戳

SELECT unix_timestamp();
-- 回傳例：1436775578

2. 帶參數：指定日期時間字串轉換

SELECT unix_timestamp('2015-07-13 16:20:20');
-- 回傳：1436775620

• 支援格式：'YYYY-MM-DD HH:MM:SS'、'YYYY-MM-DD'、'YYYYMMDDHHMMSS' 等。
• 若輸入無效，回傳 NULL。

二、from_unixtime：時間戳 → 日期時間

1. 基本用法：預設格式 YYYY-MM-DD HH:MM:SS

SELECT from_unixtime(1436775620);
-- 回傳：2015-07-13 16:20:20

2. 自訂輸出格式（第二參數）

SELECT from_unixtime(1436775620, '%Y-%m-%d');
-- 回傳：2015-07-13

SELECT from_unixtime(1436775620, '%Y%m%d %H:%i:%s');
-- 回傳：20150713 16:20:20

常用格式符號：

三、MySQL TIMESTAMP 型別的範圍限制（重要）

MySQL TIMESTAMP 型別儲存範圍為 1970-01-01 00:00:01 UTC 至 2038-01-19 03:14:07 UTC（32 位元有號整數上限）。

• from_unixtime(0) → 1970-01-01 08:00:00（依伺服器時區，預設 CST +8小時）
• from_unixtime(2147483647) → 2038-01-19 11:14:07
• 超出範圍的時間戳轉換會回傳 NULL。

建議：需儲存 2038 年後或 1970 年前日期，請改用 DATETIME 型別（範圍 1000-01-01 至 9999-12-31），或在應用層用 64 位元整數（BIGINT）儲存毫秒級時間戳。

四、PHP 對應函數速查（跨語言開發參考）

1. 時間戳 → 日期字串：date()

// 完整格式
date('Y-m-d H:i:s', 1156219870);  // 2006-06-22 18:31:10

2. 日期字串 → 時間戳：strtotime() / mktime()

// strtotime：解析英文文字日期
strtotime('2010-03-24 08:15:42');   // 1269418542
strtotime('10 September 2008');     // 1220976000
strtotime('+1 day');                // 明天此時時間戳

// mktime：從分量建立時間戳
mktime(21, 50, 55, 7, 14, 2010);    // 1279115455 (2010-07-14 21:50:55)
// 參數順序：時、分、秒、月、日、年（可從右向左省略，預設為當前）

3. 進階：mktime 日期運算與驗證

// 下個月最後一天 = 下個月第 0 天
$lastday = mktime(0, 0, 0, 3, 0, 2008);  // 2008-02-29 00:00:00 (閏年)
echo date('Y-m-d', $lastday);              // 2008-02-29

// 自動修正超出範圍輸入
date('Y-m-d', mktime(0, 0, 0, 12, 32, 2007)); // 2008-01-01
date('Y-m-d', mktime(0, 0, 0, 13, 1, 2007));  // 2008-01-01

4. 自訂格式解析函數範例（正則驗證 + gmmktime）

function str_format_time($timestamp = '') {
    if (preg_match('/^[0-9]{4}-[0-9]{1,2}-[0-9]{1,2} (?:[01][0-9]|2[0-3]):[0-5][0-9]:[0-5][0-9]$/', $timestamp)) {
        list($date, $time) = explode(' ', $timestamp);
        list($year, $month, $day) = explode('-', $date);
        list($hour, $minute, $second) = explode(':', $time);
        return gmmktime($hour, $minute, $second, $month, $day, $year);
    }
    return time(); // 格式不符回傳當前時間戳
}

// 使用
$date_str = "2011-09-11 17:00:00";
$time_str = str_format_time($date_str);
echo date("Y-m-d H:i:s", $time_str); // 2011-09-11 17:00:00

五、常見坑與最佳實踐

1. 時區一致性：MySQL 伺服器、連線會話、應用程式三端時區必須一致（建議統一用 UTC，儲存時轉換）。
2. 避免隱含轉換：WHERE create_time > unix_timestamp('2023-01-01') 會導致索引失效，應改為 WHERE create_time > '2023-01-01' 讓優化器使用索引。
3. 毫秒級需求：MySQL 5.6.4+ 支援 DATETIME(3)、TIMESTAMP(3) 毫秒精度；PHP 用 microtime(true) 取得浮點數秒。

掌握 unix_timestamp/from_unixtime 及 PHP 對應函數，並注意 2038 年問題與時區一致性，即能在跨語言專案中正確處理時間資料。