MySQL 日期時間函數完整分類參考：從基礎提取到複雜運算
日期時間處理是業務開發高頻領域。本文將 MySQL 內建日期時間函數按功能分類（提取、格式化、運算、轉換、系統時間），提供語法、參數、返回值、實戰範例與版本差異，構建開發隨查隨用的速查手冊。
一、日期時間組件提取函數
| 函數 | 功能 | 返回值範圍/示例 |
|---|
| YEAR(date) | 年份 | 1000-9999, 如 2024 |
| QUARTER(date) | 季度 | 1-4 |
| MONTH(date) | 月份 | 1-12 |
| WEEK(date[,mode]) | 年間第幾周 | 0-53, mode 控制周起始日 |
| WEEKDAY(date) | 周幾 (0=週一) | 0-6 |
| DAYOFWEEK(date) | 周幾 (1=週日) | 1-7 (ODBC 標準) |
| DAYOFMONTH(date) | 月內第幾天 | 1-31 |
| DAYOFYEAR(date) | 年內第幾天 | 1-366 |
| DAYNAME(date) | 星期英文名 | 'Monday'... |
| MONTHNAME(date) | 月份英文名 | 'January'... |
| HOUR(time) | 小時 | 0-23 |
| MINUTE(time) | 分鐘 | 0-59 |
| SECOND(time) | 秒 | 0-59 |
| MICROSECOND(datetime) | 微秒 (5.6+) | 0-999999 |
二、日期時間格式化輸出
DATE_FORMAT(date, format) / TIME_FORMAT(time, format)
| 格式符 | 含義 | 示例 |
|---|
| %Y | 4位年份 | 2024 |
| %y | 2位年份 | 24 |
| %m | 月份(01-12) | 01 |
| %c | 月份(1-12) | 1 |
| %d | 日期(01-31) | 05 |
| %e | 日期(1-31) | 5 |
| %H | 小時(00-23) | 14 |
| %h/%I | 小時(01-12) | 02 |
| %i | 分鐘(00-59) | 30 |
| %s/%S | 秒(00-59) | 45 |
| %T | 時間 24制 | 14:30:45 |
| %r | 時間 12制 | 02:30:45 PM |
| %W | 完整星期名 | Monday |
| %a | 縮寫星期名 | Mon |
| %M | 完整月份名 | January |
| %b | 縮寫月份名 | Jan |
| %j | 年內天數(001-366) | 035 |
| %p | AM/PM | PM |
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s'); -- 2024-01-15 14:30:45
SELECT DATE_FORMAT('2024-01-15', '%W, %M %D, %Y'); -- Monday, January 15th, 2024
三、日期時間運算：加減間隔
DATE_ADD(date, INTERVAL expr unit) / DATE_SUB(date, INTERVAL expr unit)
| 單位 | 關鍵字 | expr 格式 | 示例 |
|---|
| 秒 | SECOND | SECONDS | INTERVAL 30 SECOND |
| 分 | MINUTE | MINUTES | INTERVAL 45 MINUTE |
| 時 | HOUR | HOURS | INTERVAL 2 HOUR |
| 天 | DAY | DAYS | INTERVAL 7 DAY |
| 月 | MONTH | MONTHS | INTERVAL 3 MONTH |
| 年 | YEAR | YEARS | INTERVAL 1 YEAR |
| 分:秒 | MINUTE_SECOND | "MIN:SEC" | INTERVAL '30:15' MINUTE_SECOND |
| 時:分 | HOUR_MINUTE | "HOUR:MIN" | INTERVAL '2:30' HOUR_MINUTE |
| 天 時 | DAY_HOUR | "DAY HOUR" | INTERVAL '3 5' DAY_HOUR |
| 年-月 | YEAR_MONTH | "YEAR-MONTH" | INTERVAL '1-6' YEAR_MONTH |
| 時:分:秒 | HOUR_SECOND | "H:M:S" | INTERVAL '1:30:45' HOUR_SECOND |
| 天 時:分 | DAY_MINUTE | "DAY H:M" | INTERVAL '2 12:30' DAY_MINUTE |
| 天 時:分:秒 | DAY_SECOND | "DAY H:M:S" | INTERVAL '1 10:20:30' DAY_SECOND |
SELECT DATE_ADD('2024-01-15', INTERVAL 1 MONTH); -- 2024-02-15
SELECT DATE_SUB(NOW(), INTERVAL '1 12:30' DAY_HOUR); -- 1.5天前
SELECT '2024-01-15' + INTERVAL 1 DAY; -- 運算符簡寫 (5.6+)
SELECT DATE_ADD('2024-01-31', INTERVAL 1 MONTH); -- 2024-02-29 (閏年自動調整)
四、時間戳與 Unix 時間轉換
| 函數 | 功能 | 示例 |
|---|
| UNIX_TIMESTAMP([date]) | 轉 Unix 時間戳 (秒) | 1705312245 |
| FROM_UNIXTIME(unix_ts[,format]) | 時間戳轉日期時間 | '2024-01-15 14:30:45' |
| UTC_TIMESTAMP() | 當前 UTC 時間 | '2024-01-15 06:30:45' |
| CONVERT_TZ(dt, from_tz, to_tz) | 時區轉換 | CONVERT_TZ(NOW(), '+00:00', '+08:00') |
五、系統時間獲取
| 函數 | 返回類型 | 示例 | 差異 |
|---|
| NOW() | DATETIME | '2024-01-15 14:30:45' | 語句開始時刻固定 |
| SYSDATE() | DATETIME | '2024-01-15 14:30:46' | 實時執行時刻，受 sysdate_is_now 影響 |
| CURDATE() | DATE | '2024-01-15' | 僅日期 |
| CURTIME() | TIME | '14:30:45' | 僅時間 |
| CURRENT_TIMESTAMP() | DATETIME | 同 NOW() | 標準 SQL 別名 |
六、周期運算：PERIOD_ADD / PERIOD_DIFF
-- 單位：月，格式 YYYYMM 或 YYMM
SELECT PERIOD_ADD(202401, 3); -- 202404
SELECT PERIOD_DIFF(202406, 202401); -- 5 (相差 5 個月)
七、天數與日期互轉：TO_DAYS / FROM_DAYS
SELECT TO_DAYS('2024-01-15'); -- 738532 (從 0000-01-01 起算)
SELECT FROM_DAYS(738532); -- '2024-01-15'
-- 注意：不支援 1582 年前格里高利曆改革前日期
八、秒數與時間互轉：TIME_TO_SEC / SEC_TO_TIME
SELECT TIME_TO_SEC('14:30:45'); -- 52245
SELECT SEC_TO_TIME(52245); -- '14:30:45'
九、提取複合組件：EXTRACT(unit FROM date)
SELECT EXTRACT(YEAR_MONTH FROM '2024-01-15 14:30:45'); -- 202401
SELECT EXTRACT(DAY_HOUR FROM '2024-01-15 14:30:45'); -- 1514
SELECT EXTRACT(MINUTE_SECOND FROM '2024-01-15 14:30:45'); -- 3045
十、字串解析為日期：STR_TO_DATE(str, format)
SELECT STR_TO_DATE('15/01/2024 14:30', '%d/%m/%Y %H:%i'); -- '2024-01-15 14:30:00'
-- 反向操作：DATE_FORMAT
十一、實戰常用組合模式
