核心函數對照
| 方向 | 函數 | 語法 | 範例 |
| 時間戳 → 日期 | FROM_UNIXTIME() | FROM_UNIXTIME(unix_ts, format) | SELECT FROM_UNIXTIME(1700000000, '%Y-%m-%d %H:%i:%s'); |
| 日期 → 時間戳 | UNIX_TIMESTAMP() | UNIX_TIMESTAMP([date]) | SELECT UNIX_TIMESTAMP('2024-01-01 00:00:00'); |
FROM_UNIXTIME 格式化符號完整表
| 符號 | 含義 | 範例 |
%Y | 4位年 | 2024 |
%m | 月(01-12) | 05 |
%d | 日(01-31) | 20 |
%H | 時24制(00-23) | 14 |
%i | 分(00-59) | 30 |
%s | 秒(00-59) | 45 |
%T | 時間(HH:MM:SS) | 14:30:45 |
%r | 時間12制(AM/PM) | 02:30:45 PM |
%W | 星期全寫 | Monday |
%w | 星期數(0=Sun) | 1 |
%j | 年間天數(001-366) | 141 |
%% | 字面% | % |
實戰範例
1. 當前時間雙向轉換
SELECT
UNIX_TIMESTAMP() AS ts,
FROM_UNIXTIME(UNIX_TIMESTAMP()) AS dt,
FROM_UNIXTIME(UNIX_TIMESTAMP(), '%Y-%m-%d %H:%i:%s') AS dt_fmt;
2. 指定日期轉時間戳
SELECT UNIX_TIMESTAMP('2024-05-20 14:30:00'); -- 1716186600
3. 時間戳轉指定格式
SELECT FROM_UNIXTIME(1716186600, '%Y年%m月%d日 %H時%i分'); -- 2024年05月20日 14時30分
4. 查詢當天資料(高效寫法)
-- 避免函數導致索引失效
SELECT * FROM message
WHERE chattime >= UNIX_TIMESTAMP(CURDATE())
AND chattime < UNIX_TIMESTAMP(CURDATE() + INTERVAL 1 DAY);
5. 時區處理
UNIX_TIMESTAMP 回傳 **UTC 時間戳**(無時區)。
FROM_UNIXTIME 輸出 **Session 時區** 時間(受 time_zone 影響)。
- 跨時區應用建議:存儲 UTC 時間戳(BIGINT),應用層按用戶時區格式化顯示。
常見坑點
| 坑點 | 現象 | 解決 |
| 32 位系統 2038 年問題 | 有符號 32 位整數上限 2038-01-19 | 使用 BIGINT(64位) 存儲,MySQL 內部已 64 位 |
| 時區不一致導致日期偏移 | Session time_zone 不同 | 統一設定 SET time_zone = '+08:00' 或應用層處理 |
| WHERE 使用 FROM_UNIXTIME 導致全掃 | 函數作用在欄位上 | 改用範圍查詢(見範例 4) |
| 毫秒/微秒精度缺失 | UNIX_TIMESTAMP 只精確到秒 | MySQL 5.6.4+ 支援 UNIX_TIMESTAMP(3) 返回毫秒,或存 DATETIME(3) |
總結
UNIX_TIMESTAMP / FROM_UNIXTIME 是 MySQL 日期與時間戳互轉的核心組合。**存儲建議用 BIGINT 存 UTC 秒級時間戳**,查詢用範圍條件利用索引,顯示時再 FROM_UNIXTIME 格式化。時區統一在 Session 或應用層處理,避免「夏令時/跨時區」Bug。