MySQL 日期時間與時間戳互轉函數大全：FROM_UNIXTIME / UNIX_TIMESTAMP 實戰

MySQL 日期時間與時間戳互轉函數大全：FROM_UNIXTIME / UNIX_TIMESTAMP 實戰

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

FROM_UNIXTIME(unix_timestamp[, format])

實戰示例

表 crm_invest_apply 中 invest_time 存儲時間戳 (如 1429063399)：

SELECT FROM_UNIXTIME(invest_time, '%Y年%m月%d日 %H:%i:%s')
FROM crm_invest_apply;

常用格式化符號

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

UNIX_TIMESTAMP([date])

實戰示例

-- 字符串轉時間戳
SELECT UNIX_TIMESTAMP('2015-04-29');        -- 1430236800
SELECT UNIX_TIMESTAMP('2015-04-29 12:30:45'); -- 1430281845

-- 當前時間戳
SELECT UNIX_TIMESTAMP();  -- 等同 UNIX_TIMESTAMP(NOW())

三、進階組合技

1. 日期運算 + 格式化

-- 3天前的日期時間戳
SELECT UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 3 DAY));

2. 時區處理

-- 會話級時區
SET time_zone = '+08:00';
SELECT FROM_UNIXTIME(1429063399);  -- 按東八區顯示

3. Java 端互轉 (補充)

// Java: Date -> Unix時間戳(秒)
long timestamp = System.currentTimeMillis() / 1000;

// MySQL: 時間戳 -> Date
SELECT FROM_UNIXTIME(1429063399);

四、性能與避坑指南

• 索引友好：時間戳 (INT/BIGINT) 佔 4/8 字節，比 DATETIME (8字節) 更緊湊，范圍查詢走索引極快
• 時區一致性：應用層、MySQL 會話、系統時區保持一致，避免「少/多 8 小時」離譜 Bug
• 2038 年問題：32 位有符號 INT 最大 2147483647 (2038-01-19)，長期項目請用 BIGINT 或 DATETIME
• 避免函數列索引失效：WHERE FROM_UNIXTIME(ts) > '2024-01-01' 不走索引，改寫為 WHERE ts > UNIX_TIMESTAMP('2024-01-01')