一、日期時間組件提取函數
| 函數 | 返回值 | 示例 | 結果 |
| YEAR(date) | 年份 (1000-9999) | YEAR('2024-01-15') | 2024 |
| MONTH(date) | 月份 (1-12) | MONTH('2024-01-15') | 1 |
| DAY(date) / DAYOFMONTH(date) | 日 (1-31) | DAY('2024-01-15') | 15 |
| HOUR(time) | 小時 (0-23) | HOUR('14:30:00') | 14 |
| MINUTE(time) | 分鐘 (0-59) | MINUTE('14:30:00') | 30 |
| SECOND(time) | 秒 (0-59) | SECOND('14:30:00') | 0 |
| DAYOFWEEK(date) | 星期索引 (1=周日..7=周六) | DAYOFWEEK('2024-01-15') | 2 (周一) |
| WEEKDAY(date) | 星期索引 (0=周一..6=周日) | WEEKDAY('2024-01-15') | 0 (周一) |
| DAYOFYEAR(date) | 年內第幾天 (1-366) | DAYOFYEAR('2024-01-15') | 15 |
| WEEK(date[,mode]) | 年內第幾周 (0-53) | WEEK('2024-01-15', 1) | 3 (mode=1 週一開始) |
| QUARTER(date) | 季度 (1-4) | QUARTER('2024-01-15') | 1 |
| MONTHNAME(date) | 英文月名 | MONTHNAME('2024-01-15') | January |
| DAYNAME(date) | 英文星期名 | DAYNAME('2024-01-15') | Monday |
二、日期時間計算函數
1. 日期加減:DATE_ADD / DATE_SUB / ADDDATE / SUBDATE
DATE_ADD(date, INTERVAL expr unit)
DATE_SUB(date, INTERVAL expr unit)
-- 同義詞
ADDDATE(date, INTERVAL expr unit)
SUBDATE(date, INTERVAL expr unit)
支持的時間單位
MICROSECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR
SECOND_MICROSECOND, MINUTE_MICROSECOND, MINUTE_SECOND, HOUR_MICROSECOND,
HOUR_SECOND, HOUR_MINUTE, DAY_MICROSECOND, DAY_SECOND, DAY_MINUTE, DAY_HOUR,
YEAR_MONTH
實戰示例
SELECT DATE_ADD('2024-01-15', INTERVAL 1 DAY); -- 2024-01-16
SELECT DATE_ADD('2024-01-15', INTERVAL 2 MONTH); -- 2024-03-15
SELECT DATE_ADD('2024-01-15 14:30:00', INTERVAL 3 HOUR); -- 2024-01-15 17:30:00
SELECT DATE_SUB('2024-01-15', INTERVAL 1 YEAR); -- 2023-01-15
SELECT DATE_ADD('2024-01-15', INTERVAL '1-2' YEAR_MONTH); -- 2025-03-15
2. 日期差值:DATEDIFF / TIMEDIFF
DATEDIFF('2024-01-20', '2024-01-15'); -- 5 (天數差,忽略時間部分)
TIMEDIFF('14:30:00', '12:00:00'); -- '02:30:00' (時間差)
3. 週期計算:PERIOD_ADD / PERIOD_DIFF
PERIOD_ADD(202401, 3); -- 202404 (年月格式 YYYYMM)
PERIOD_DIFF(202404, 202401); -- 3 (月數差)
三、格式化與解析
1. DATE_FORMAT:日期 → 字符串
DATE_FORMAT(date, format)
常用格式符
| 符號 | 含義 | 示例 |
| %Y | 4位年份 | 2024 |
| %y | 2位年份 | 24 |
| %m | 月份(01-12) | 01 |
| %c | 月份(1-12) | 1 |
| %d | 日期(01-31) | 15 |
| %e | 日期(1-31) | 15 |
| %H | 小時(00-23) | 14 |
| %h/%I | 小時(01-12) | 02 |
| %i | 分鐘(00-59) | 30 |
| %s/%S | 秒(00-59) | 00 |
| %W | 完整星期名 | Monday |
| %a | 縮寫星期名 | Mon |
| %b | 縮寫月名 | Jan |
| %r | 12小時制時間 | 02:30:00 PM |
| %T | 24小時制時間 | 14:30:00 |
示例
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s'); -- 2024-01-15 14:30:00
SELECT DATE_FORMAT('2024-01-15', '%W, %M %e, %Y'); -- Monday, January 15, 2024
2. STR_TO_DATE:字符串 → 日期
STR_TO_DATE('15/01/2024', '%d/%m/%Y'); -- 2024-01-15
STR_TO_DATE('01/15/2024 02:30 PM', '%m/%d/%Y %h:%i %p'); -- 2024-01-15 14:30:00
四、當前時間與時間戳轉換
| 函數 | 返回類型 | 示例 |
| NOW() / CURRENT_TIMESTAMP() | DATETIME (YYYY-MM-DD HH:MM:SS) | 2024-01-15 14:30:00 |
| CURDATE() / CURRENT_DATE() | DATE (YYYY-MM-DD) | 2024-01-15 |
| CURTIME() / CURRENT_TIME() | TIME (HH:MM:SS) | 14:30:00 |
| UNIX_TIMESTAMP([date]) | 整數 (秒數,1970起) | 1705312200 |
| FROM_UNIXTIME(unix_ts) | DATETIME | 2024-01-15 14:30:00 |
| UTC_TIMESTAMP() | UTC 時間 | 2024-01-15 06:30:00 |
| SYSdate() | 執行瞬間時間(函數調用時) | 與 NOW() 不同,NOW() 為語句開始時間 |
五、進階業務場景實戰
1. 本月第一天/最後一天
-- 本月第一天
SELECT DATE_FORMAT(NOW(), '%Y-%m-01');
-- 或
SELECT DATE_SUB(LAST_DAY(NOW()), INTERVAL DAY(LAST_DAY(NOW()))-1 DAY);
-- 本月最後一天
SELECT LAST_DAY(NOW());
2. 本週一/本週日
-- 本週一 (模式: 週一為週首)
SELECT DATE_SUB(NOW(), INTERVAL WEEKDAY(NOW()) DAY);
-- 本週日
SELECT DATE_ADD(NOW(), INTERVAL (6 - WEEKDAY(NOW())) DAY);
3. 年齡計算
SELECT FLOOR(DATEDIFF(CURDATE(), '1990-05-20') / 365.25); -- 33 歲
4. 相對時間範圍查詢(索引友好)
-- 近 7 天(含今天)
WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL 6 DAY)
AND created_at < DATE_ADD(CURDATE(), INTERVAL 1 DAY)
-- 當月
WHERE created_at >= DATE_FORMAT(NOW(), '%Y-%m-01')
AND created_at < DATE_ADD(DATE_FORMAT(NOW(), '%Y-%m-01'), INTERVAL 1 MONTH)
5. 時區轉換
-- 會話級時區
SET time_zone = '+08:00';
-- 或
CONVERT_TZ('2024-01-15 14:30:00', '+00:00', '+08:00'); -- 需載入時區表
六、性能與最佳實踐
- WHERE 條件避免函數包裹列:
WHERE DATE(created_at) = '2024-01-15' → 無法用索引。改為範圍查詢(見上)。
- 存儲用 UTC (TIMESTAMP/DATETIME),顯示層按用戶時區轉換。
- 批量導入用 STR_TO_DATE 統一格式解析。
- 避免 SELECT NOW() 等非確定性函數 導致查詢緩存失效(5.7 已移除查詢緩存,8.0 無影響)。
- 大表時間範圍分區:`PARTITION BY RANGE (TO_DAYS(created_at))`。
總結
MySQL 日期時間函數體系完整覆蓋 **提取、計算、格式化、解析、轉換、當前時間** 六大類。核心原則:**業務邏輯用高級函數(DATE_ADD、DATE_FORMAT),索引查詢用範圍條件(BETWEEN/>= <),存儲統一 UTC,顯示按時區轉換**。掌握 DATE_FORMAT 格式符、INTERVAL 單位組合、STR_TO_DATE 解析模式,可處理 99% 日期時間業務需求。