MySQL 時間戳與日期互轉函數大全
核心函數對照表
| 轉換方向 | MySQL 函數 | PHP 對應函數 | 示例 |
|---|
| Unix 時間戳 → 日期時間 | FROM_UNIXTIME(timestamp) | date('Y-m-d H:i:s', $ts) | SELECT FROM_UNIXTIME(1156219870); → 2006-06-22 12:31:10 |
| 日期時間 → Unix 時間戳 | UNIX_TIMESTAMP(datetime) | strtotime('2006-11-04 12:23:00') | SELECT UNIX_TIMESTAMP('2006-11-04 12:23:00'); → 1162633380 |
| 日期格式化 | DATE_FORMAT(date, format) | date('Y-m-d', $ts) | DATE_FORMAT(NOW(), '%Y-%m-%d') |
常用查詢場景
1. 查詢當天記錄（聊天時間存 Unix 時間戳 chattime）
SELECT * FROM message
WHERE DATE_FORMAT(FROM_UNIXTIME(chattime), '%Y-%m-%d') = DATE_FORMAT(NOW(), '%Y-%m-%d')
ORDER BY id DESC;
2. 查詢最近 7 天記錄
SELECT * FROM message
WHERE FROM_UNIXTIME(chattime) >= DATE_SUB(NOW(), INTERVAL 7 DAY)
ORDER BY chattime DESC;
3. 日期時間運算
-- 當前時間加 1 天
SELECT DATE_ADD(NOW(), INTERVAL 1 DAY);
-- 兩時間差（秒）
SELECT TIMESTAMPDIFF(SECOND, '2023-01-01 00:00:00', NOW());
格式化符號速查
| 符號 | 含義 | 示例 |
|---|
%Y | 四位年份 | 2024 |
%m | 兩位月份 | 01-12 |
%d | 兩位日期 | 01-31 |
%H | 24制小時 | 00-23 |
%i | 分鐘 | 00-59 |
%s | 秒 | 00-59 |
%W | 星期名稱 | Monday |
最佳實踐建議
- 存儲建議：優先用
DATETIME 或 TIMESTAMP 類型，避免用 INT 存 Unix 時間戳（失去類型語義、時區處理麻煩）
- 時區一致性：應用層、資料庫層、系統層時區保持一致，推薦統一 UTC 存儲，展示層轉換
- 索引友好：
WHERE create_time >= '2024-01-01' 可用索引，WHERE DATE(create_time) = '2024-01-01' 會導致全表掃描
- 5.7+ 支援：
DATETIME(3) 毫秒精度，NOW(3) 獲取毫秒時間
