MySQL 提供超過 40 個日期時間函數，本文按 **元件擷取**、**運算**、**格式化**、**轉換**、**當前時間**、**Unix 時間戳**、**週/季/年** 七大類彙整，附標準語法、範例與回傳值，適合開發查閱與面試複習。
一、元件擷取函數
| 函數 | 說明 | 範例回傳 |
|---|
YEAR(dt) | 年份 (1000-9999) | 2024 |
QUARTER(dt) | 季度 (1-4) | 1 |
MONTH(dt) | 月份 (1-12) | 3 |
DAY(dt) / DAYOFMONTH(dt) | 月內天數 (1-31) | 15 |
DAYOFYEAR(dt) | 年內天數 (1-366) | 75 |
DAYOFWEEK(dt) | 星期索引 (1=週日, 7=週六) ODBC 標準 | 6 |
WEEKDAY(dt) | 星期索引 (0=週一, 6=週日) | 4 |
DAYNAME(dt) | 星期英文名 | Friday |
MONTHNAME(dt) | 月份英文名 | March |
HOUR(dt) | 小時 (0-23) | 14 |
MINUTE(dt) | 分鐘 (0-59) | 30 |
SECOND(dt) | 秒數 (0-59) | 5 |
MICROSECOND(dt) | 微秒 (0-999999) | 123456 |
二、日期運算：DATE_ADD / DATE_SUB / INTERVAL
DATE_ADD(dt, INTERVAL expr unit)
DATE_SUB(dt, INTERVAL expr unit)
-- 同義詞：ADDDATE(dt, INTERVAL...), SUBDATE(dt, INTERVAL...)
-- 常用 unit 對照
SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR
MINUTE_SECOND -- 'MM:SS'
HOUR_MINUTE -- 'HH:MM'
DAY_HOUR -- 'DD HH'
YEAR_MONTH -- 'YY-MM'
HOUR_SECOND -- 'HH:MM:SS'
DAY_MINUTE -- 'DD HH:MM'
DAY_SECOND -- 'DD HH:MM:SS'
-- 範例
DATE_ADD('2024-03-15 14:30:00', INTERVAL 1 DAY) -- '2024-03-16 14:30:00'
DATE_ADD('2024-03-15', INTERVAL '1 12:30' DAY_HOUR) -- '2024-03-17 03:00:00'
DATE_SUB('2024-03-15', INTERVAL 3 MONTH) -- '2023-12-15'
SELECT '2024-03-15' + INTERVAL 1 DAY; -- 簡寫運算子 (同 DATE_ADD)
三、格式化：DATE_FORMAT / TIME_FORMAT
DATE_FORMAT(dt, format) -- 完整格式化
TIME_FORMAT(time, format) -- 僅時間部分 (%H %i %s %r %T %p 等)
-- 核心格式符 (完整見官方文件)
%Y 4位年 %y 2位年 %m 月(01-12) %c 月(1-12) %d 日(01-31) %e 日(1-31)
%H 24時(00-23) %k 24時(0-23) %h/%I 12時(01-12) %l 12時(1-12)
%i 分(00-59) %s/%S 秒(00-59) %f 微秒(000000-999999)
%r 12時時間(hh:mm:ss AM/PM) %T 24時時間(hh:mm:ss)
%p AM/PM %W 週名 %a 縮寫週名 %M 月名 %b 縮寫月名
%j 年內天數(001-366) %U 週數(週日首) %u 週數(週一首) %% 字面%
-- 範例
DATE_FORMAT('2024-03-15 14:30:05', '%Y-%m-%d %H:%i:%s') -- 2024-03-15 14:30:05
DATE_FORMAT(dt, '%Y%m%d') -- 20240315 (分區鍵/檔名常用)
四、轉換與解析
| 函數 | 用途 | 範例 |
|---|
STR_TO_DATE(str, fmt) | 字串依格式解析為 DATETIME | STR_TO_DATE('15/03/2024', '%d/%m/%Y') → '2024-03-15' |
DATE(dt) | 擷取日期部分 | DATE('2024-03-15 14:30:00') → '2024-03-15' |
TIME(dt) | 擷取時間部分 | TIME('2024-03-15 14:30:00') → '14:30:00' |
EXTRACT(unit FROM dt) | 標準 SQL 擷取單元 | EXTRACT(YEAR_MONTH FROM dt) → 202403 |
MAKEDATE(year, dayofyear) | 年+年內天數→日期 | MAKEDATE(2024, 75) → '2024-03-15' |
MAKETIME(hour, min, sec) | 時分秒→TIME | MAKETIME(14,30,5) → '14:30:05' |
PERIOD_ADD(ym, n) | 年月(YYMM/YYYYMM)加 n 月 | PERIOD_ADD(202403, 2) → 202405 |
PERIOD_DIFF(ym1, ym2) | 兩年月差距月數 | PERIOD_DIFF(202405, 202403) → 2 |
五、當前時間函數
| 函數 | 回傳型別 | 特性 |
|---|
NOW() | DATETIME | 語句執行開始時刻固定 |
SYSDATE() | DATETIME | 函數執行即時刻 (複制/觸發器中不同) |
CURRENT_TIMESTAMP() | DATETIME | 同 NOW() |
CURDATE() | DATE | 當前日期 |
CURTIME() | TIME | 當前時間 |
UTC_DATE() | DATE | UTC 日期 |
UTC_TIME() | TIME | UTC 時間 |
UTC_TIMESTAMP() | DATETIME | UTC 日期時間 |
六、Unix 時間戳轉換
UNIX_TIMESTAMP() -- 當前秒數
UNIX_TIMESTAMP(dt) -- 指定 datetime 轉秒數
FROM_UNIXTIME(ts) -- 秒數轉 DATETIME (session 時區)
FROM_UNIXTIME(ts, fmt) -- 格式化輸出
-- 注意：TIMESTAMP 欄位儲存 UTC，讀取自動轉 session 時區
七、秒數與 TIME 互轉
TIME_TO_SEC('14:30:05') -- 52205
SEC_TO_TIME(52205) -- '14:30:05'
八、週/季/年 進階
WEEK(dt[, mode]) -- mode: 0=週日首, 1=週一首, 3=週一首(ISO), 等
YEARWEEK(dt[, mode]) -- 年週合併值 (如 202411)
LAST_DAY(dt) -- 月底日期
QUARTER(dt) -- 季度 (1-4)
九、實戰提醒
- WHERE 範圍查詢：用
dt >= '2024-03-01' AND dt < '2024-04-01' 替代 MONTH(dt)=3 利用索引。
- 時區一致性：伺服器
default-time-zone=+00:00，應用層按用戶時區顯示。
- 8.0 新函數：
JSON_VALUE 擷取 JSON 日期、CAST(... AS DATETIME) 行為更嚴格。
