MySQL 時間戳與日期時間互轉：UNIX_TIMESTAMP 與 FROM_UNIXTIME 詳解

MySQL 時間戳與日期時間互轉

MySQL 提供兩個核心函數實現 Unix 時間戳（秒數）與 DATETIME/TIMESTAMP 類型的雙向轉換，廣泛用於前端交互、日志分析、跨時區存儲等場景。

核心函數

常用範例

-- 當前時間雙向轉換
SELECT 
  NOW() AS current_datetime,
  UNIX_TIMESTAMP(NOW()) AS current_timestamp,
  FROM_UNIXTIME(UNIX_TIMESTAMP(NOW())) AS back_to_datetime;

-- 指定日期轉換
SELECT UNIX_TIMESTAMP('2024-01-15 12:30:45');  -- 1705302645
SELECT FROM_UNIXTIME(1705302645);              -- 2024-01-15 12:30:45

-- 帶格式化輸出
SELECT FROM_UNIXTIME(1705302645, '%Y年%m月%d日 %H:%i:%s');

時區陷阱與注意事項

1. 時區敏感：UNIX_TIMESTAMP 將輸入視為當前會話時區（@@session.time_zone）的時間，轉為 UTC 秒數。FROM_UNIXTIME 則將 UTC 秒數轉回會話時區時間。
2. TIMESTAMP 列寫入時自動轉 UTC，讀出時轉回會話時區；DATETIME 不做時區轉換，原樣存取。
3. 跨時區應用建議：統一存儲 UTC（SET time_zone='+00:00'），應用層按用戶時區展示。
4. 2038 年問題：32 位有符號整數上限 2147483647 對應 2038-01-19 03:14:07 UTC。MySQL 8.0+ TIMESTAMP 已擴展至 64 位，但 FROM_UNIXTIME 參數若超出 32 位範圍在舊版本會返回 NULL。

進階用法

-- 批量更新：將 timestamp 列轉為 datetime 存儲
UPDATE logs SET dt_col = FROM_UNIXTIME(ts_col);

-- 日期運算配合
SELECT DATE_ADD(FROM_UNIXTIME(ts), INTERVAL 1 DAY);

-- 分組統計：按天聚合
SELECT DATE(FROM_UNIXTIME(create_ts)) AS day, COUNT(*) 
FROM orders GROUP BY day;

效能提示

• 兩函數均為確定性函數，可用於索引條件（需配合函數索引或生成列）
• 大量轉換時，應用層批量處理通常比 SQL 逐行轉換更快