核心函數對照
| 函數 | 方向 | 說明 |
|---|
UNIX_TIMESTAMP() | 日期 → 時間戳 | 無參數返回當前秒數；有參數轉換指定日期 |
FROM_UNIXTIME() | 時間戳 → 日期 | 預設格式 'YYYY-MM-DD HH:MM:SS'；可指定格式 |
UNIX_TIMESTAMP() 詳解
-- 當前時間戳
SELECT UNIX_TIMESTAMP();
-- 882226357
-- 指定日期轉換 (支援多種格式)
SELECT UNIX_TIMESTAMP('1997-10-04 22:23:00');
-- 875996580
SELECT UNIX_TIMESTAMP('2024-01-15');
SELECT UNIX_TIMESTAMP(20240115103000); -- 數字格式 YYYYMMDDHHMMSS
-- 作用於 TIMESTAMP 欄位時直接取值，無隱式轉換
SELECT UNIX_TIMESTAMP(ts_col) FROM tbl;
FROM_UNIXTIME() 詳解
-- 預設格式
SELECT FROM_UNIXTIME(875996580);
-- '1997-10-04 22:23:00'
-- 數字格式輸出
SELECT FROM_UNIXTIME(875996580) + 0;
-- 19971004222300
-- 自定義格式 (同 DATE_FORMAT 修飾符)
SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(), '%Y %D %M %h:%i:%s %x');
-- '2024 15th January 10:30:00 x'
-- 常用格式範例
SELECT FROM_UNIXTIME(ts_col, '%Y-%m-%d %H:%i:%s') FROM tbl; -- 標準日期時間
SELECT FROM_UNIXTIME(ts_col, '%Y-%m-%d') FROM tbl; -- 僅日期
SELECT FROM_UNIXTIME(ts_col, '%H:%i:%s') FROM tbl; -- 僅時間
格式修飾符速查
| 修飾符 | 含義 | 範例 |
|---|
| %Y | 4 位年份 | 2024 |
| %y | 2 位年份 | 24 |
| %m | 月份 (01-12) | 01 |
| %c | 月份 (1-12) | 1 |
| %d | 日期 (01-31) | 15 |
| %e | 日期 (1-31) | 15 |
| %H | 小時 24制 (00-23) | 14 |
| %h/%I | 小時 12制 (01-12) | 02 |
| %i | 分鐘 (00-59) | 30 |
| %s/%S | 秒 (00-59) | 45 |
| %W | 星期名稱 | Monday |
| %a | 星期縮寫 | Mon |
| %M | 月份名稱 | January |
| %b | 月份縮寫 | Jan |
| %j | 年內天數 (001-366) | 015 |
| %r | 12制時間 (hh:mm:ss AM/PM) | 02:30:45 PM |
| %T | 24制時間 (hh:mm:ss) | 14:30:45 |
| %% | 字面 % | % |
毫秒級時間戳處理
部分語言 (Java/JS) 產生 13 位毫秒戳，MySQL 函數需秒級：
-- 截取前 10 位
SELECT FROM_UNIXTIME(1705324245123 / 1000);
-- 或
SELECT FROM_UNIXTIME(FLOOR(1705324245123 / 1000));
實戰：資料寫入與查詢
-- 寫入：應用層傳入日期字串，資料庫轉時間戳存 INT/BIGINT
INSERT INTO events (event_time) VALUES (UNIX_TIMESTAMP('2024-01-15 14:30:00'));
-- 查詢：轉回可讀日期
SELECT id, FROM_UNIXTIME(event_time, '%Y-%m-%d %H:%i:%s') AS event_dt FROM events;
-- 範圍查詢：利用索引 (event_time 為 INT 索引)
SELECT * FROM events
WHERE event_time BETWEEN UNIX_TIMESTAMP('2024-01-01') AND UNIX_TIMESTAMP('2024-01-31 23:59:59');
時區陷阱提醒
UNIX_TIMESTAMP() 受 Session time_zone 影響- 應用層建議統一用 UTC 存儲，顯示層按用戶時區轉換
- MySQL 8.0+ 建議用
TIMESTAMPADD/TIMESTAMPDIFF 做運算