DATE_FORMAT 格式化符號完整表
| 符號 | 含義 | 示例輸出 |
|---|
| %Y | 4位年份 | 2024 |
| %y | 2位年份 | 24 |
| %M | 月份全名 | January |
| %b | 月份縮寫 | Jan |
| %m | 月份 01-12 | 01 |
| %c | 月份 1-12 | 1 |
| %D | 帶後綴日期 | 1st, 2nd |
| %d | 日期 01-31 | 01 |
| %e | 日期 1-31 | 1 |
| %W | 星期全名 | Sunday |
| %a | 星期縮寫 | Sun |
| %w | 星期 0(日)-6(六) | 0 |
| %H | 小時 00-23 | 14 |
| %k | 小時 0-23 | 14 |
| %h/%I | 小時 01-12 | 02 |
| %l | 小時 1-12 | 2 |
| %i | 分鐘 00-59 | 05 |
| %S/%s | 秒 00-59 | 09 |
| %r | 12小時制時間 | 02:05:09 PM |
| %T | 24小時制時間 | 14:05:09 |
| %p | AM/PM | PM |
| %j | 年內天數 001-366 | 001 |
| %U | 週數 00-53 (周日為週首) | 01 |
| %u | 週數 00-53 (周一為週首) | 01 |
| %% | 字面 % 符號 | % |
核心日期時間函數
| 函數 | 說明 | 示例 |
|---|
| NOW() | 當前日期時間 | 2024-01-15 14:30:00 |
| CURDATE() | 當前日期 | 2024-01-15 |
| CURTIME() | 當前時間 | 14:30:00 |
| DATE() | 提取日期部分 | DATE(NOW()) -> 2024-01-15 |
| TIME() | 提取時間部分 | TIME(NOW()) -> 14:30:00 |
| DATE_FORMAT(d,f) | 格式化輸出 | DATE_FORMAT(NOW(),'%Y-%m-%d') |
| TIME_FORMAT(t,f) | 時間格式化 | TIME_FORMAT(NOW(),'%H:%i') |
| DATE_ADD(d,INTERVAL) | 日期加法 | DATE_ADD(NOW(),INTERVAL 1 DAY) |
| DATE_SUB(d,INTERVAL) | 日期減法 | DATE_SUB(NOW(),INTERVAL 1 MONTH) |
| DATEDIFF(d1,d2) | 日期差天數 | DATEDIFF('2024-01-15','2024-01-01')=14 |
| TIMESTAMPDIFF(unit,d1,d2) | 精確差值 | TIMESTAMPDIFF(SECOND, t1, t2) |
| STR_TO_DATE(str,fmt) | 字符串解析為日期 | STR_TO_DATE('15/01/2024','%d/%m/%Y') |
| UNIX_TIMESTAMP() | 轉時間戳 | UNIX_TIMESTAMP('2024-01-15') |
| FROM_UNIXTIME(ts) | 時間戳轉日期 | FROM_UNIXTIME(1705312200) |
| LAST_DAY(d) | 月份最後一天 | LAST_DAY('2024-02-01')=2024-02-29 |
| DAYOFWEEK(d) | 星期索引 1(日)-7(六) | DAYOFWEEK(NOW()) |
| QUARTER(d) | 季度 1-4 | QUARTER(NOW()) |
實戰示例
-- 1. 常用格式化
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s'); -- 2024-01-15 14:30:09
SELECT DATE_FORMAT(NOW(), '%Y年%m月%d日 %H點%i分'); -- 2024年01月15日 14點30分
SELECT DATE_FORMAT(NOW(), '%W, %M %D, %Y'); -- Monday, January 15th, 2024
-- 2. 日期運算
SELECT DATE_ADD(CURDATE(), INTERVAL 30 DAY); -- 30天后
SELECT DATE_SUB(NOW(), INTERVAL 2 HOUR); -- 2小時前
SELECT DATEDIFF('2024-12-31', CURDATE()); -- 今年剩余天數
-- 3. 統計按天/月/年分組
SELECT DATE_FORMAT(create_time, '%Y-%m') AS month, COUNT(*)
FROM orders GROUP BY month;
-- 4. 解析非標準格式字符串
SELECT STR_TO_DATE('2024/01/15 14:30', '%Y/%m/%d %H:%i');
-- 5. 時間戳互轉
SELECT UNIX_TIMESTAMP('2024-01-15 14:30:00');
SELECT FROM_UNIXTIME(1705312200, '%Y-%m-%d %H:%i:%s');
時區處理
-- 會話級設置
SET time_zone = '+08:00'; -- 或 'Asia/Shanghai'
-- 查看當前時區
SELECT @@time_zone, @@system_time_zone;
-- 轉換時區 (MySQL 8.0+)
SELECT CONVERT_TZ(NOW(), '+00:00', '+08:00');
性能提示
- 避免在 WHERE 條件中對列使用函數：`WHERE DATE_FORMAT(create_time, '%Y-%m') = '2024-01'` → 無法用索引
- 改用範圍查詢：`WHERE create_time >= '2024-01-01' AND create_time < '2024-02-01'`
- 大量格式化輸出考慮在應用層處理，減少數據庫 CPU
