MySQL 日期時間格式化完全指南：DATE_FORMAT、FROM_UNIXTIME 實戰用法

DATE_FORMAT(date, format) 核心格式符

常用格式化示例

SELECT DATE_FORMAT(NOW(), '%Y-%m-%d');              -- 2024-01-15
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s');     -- 2024-01-15 14:30:45
SELECT DATE_FORMAT(NOW(), '%b %d, %Y');             -- Jan 15, 2024
SELECT DATE_FORMAT(NOW(), '%Y年%m月%d日 %H時%i分');   -- 2024年01月15日 14時30分
SELECT DATE_FORMAT(NOW(), '%Y-%m-%dT%H:%i:%sZ');    -- ISO 8601: 2024-01-15T14:30:45Z

FROM_UNIXTIME(unix_timestamp, format) 時間戳轉換

無格式參數 (返回 DATETIME 類型)

SELECT FROM_UNIXTIME(1705312245);
-- 2024-01-15 14:30:45

帶格式參數 (返回字符串)

SELECT FROM_UNIXTIME(1705312245, '%Y-%m-%d %H:%i:%s');
-- 2024-01-15 14:30:45

SELECT FROM_UNIXTIME(create_time, '%Y-%m-%d') AS create_date
FROM orders;

UNIX_TIMESTAMP() 反向轉換

SELECT UNIX_TIMESTAMP();                    -- 當前時間戳
SELECT UNIX_TIMESTAMP('2024-01-15 14:30:45'); -- 指定時間轉時間戳
SELECT UNIX_TIMESTAMP(create_time) FROM orders;

實戰場景：判斷同一天數據

-- 方案 1: TO_DAYS 比較 (推薦，跨月/跨年安全)
SELECT * FROM game_log 
WHERE TO_DAYS(FROM_UNIXTIME(start_time)) = TO_DAYS(NOW());

-- 方案 2: DATE 比較 (5.7+ 支持隱式轉換)
SELECT * FROM game_log 
WHERE DATE(FROM_UNIXTIME(start_time)) = CURDATE();

-- 方案 3: 字符串比較 (效能較差)
SELECT * FROM game_log 
WHERE DATE_FORMAT(FROM_UNIXTIME(start_time), '%Y-%m-%d') = DATE_FORMAT(NOW(), '%Y-%m-%d');

時區處理最佳實踐

-- 會話級設置時區
SET time_zone = '+08:00';  -- 或 'Asia/Shanghai'

-- 存儲統一用 UTC 時間戳 (INT/BIGINT)
-- 顯示時按用戶時區轉換
SELECT FROM_UNIXTIME(create_time, '%Y-%m-%d %H:%i:%s') AS local_time
FROM orders;

效能優化建議

• WHERE 條件中避免對列使用函數 (如 DATE_FORMAT(col) = ...)，導致索引失效
• 改用範圍查詢：col >= '2024-01-01' AND col < '2024-01-02'
• 時間戳存儲用 BIGINT，查詢時應用層轉換或用 FROM_UNIXTIME 只在 SELECT 列表中
• 大表按時間分區 (PARTITION BY RANGE (TO_DAYS(create_time))) 實現分區裁剪

總結

DATE_FORMAT 用於任意日期時間格式化輸出，FROM_UNIXTIME 專為時間戳轉換設計。核心原則：存儲用 UTC 時間戳/UTC DATETIME，顯示時按業務時區格式化，WHERE 條件避免函數包裹列。