MySQL 提供豐富的日期時間函數,掌握它們能大幅簡化 SQL 撰寫。本文分類整理常用函數,並附上實際查詢範例,方便快速查閱。
| 函數 | 回傳值 | 範例 |
|---|---|---|
DAYOFWEEK(date) | 星期索引(1=週日 ... 7=週六) | SELECT DAYOFWEEK('2023-01-01'); -- 1 |
WEEKDAY(date) | 星期索引(0=週一 ... 6=週日) | SELECT WEEKDAY('2023-01-01'); -- 6 |
DAYOFMONTH(date) | 月份中的第幾天(1-31) | SELECT DAYOFMONTH('2023-01-15'); -- 15 |
DAYOFYEAR(date) | 年份中的第幾天(1-366) | SELECT DAYOFYEAR('2023-02-01'); -- 32 |
MONTH(date) | 月份(1-12) | SELECT MONTH('2023-05-20'); -- 5 |
DAYNAME(date) | 星期英文名稱 | SELECT DAYNAME('2023-01-01'); -- Sunday |
MONTHNAME(date) | 月份英文名稱 | SELECT MONTHNAME('2023-01-01'); -- January |
QUARTER(date) | 季度(1-4) | SELECT QUARTER('2023-04-01'); -- 2 |
WEEK(date[, mode]) | 年份中的週數,mode 決定週起始日 | SELECT WEEK('2023-01-01', 1); -- 52 |
YEAR(date) | 年份(1000-9999) | SELECT YEAR('2023-01-01'); -- 2023 |
HOUR(time) | 小時(0-23) | SELECT HOUR('14:30:00'); -- 14 |
MINUTE(time) | 分鐘(0-59) | SELECT MINUTE('14:30:00'); -- 30 |
SECOND(time) | 秒數(0-59) | SELECT SECOND('14:30:05'); -- 5 |
依指定格式輸出字串,常用格式代碼:
%Y 四位數年、%m 兩位數月、%d 兩位數日%H 24小時、%i 分鐘、%s 秒%W 完整星期名、%M 完整月份名-- 查詢 6 月 3 日到 7 月 8 日間過生日的會員(忽略年份)
SELECT * FROM `member`
WHERE DATE_FORMAT(`birthday`, '%m-%d') BETWEEN '06-03' AND '07-08';
日期加減運算,支援多種 INTERVAL 單位:
| 類型關鍵字 | 預期 expr 格式 |
|---|---|
| SECOND / MINUTE / HOUR / DAY / MONTH / YEAR | 數字 |
| MINUTE_SECOND | "MINUTES:SECONDS" |
| HOUR_MINUTE | "HOURS:MINUTES" |
| DAY_HOUR | "DAYS HOURS" |
| YEAR_MONTH | "YEARS-MONTHS" |
| HOUR_SECOND | "HOURS:MINUTES:SECONDS" |
| DAY_MINUTE | "DAYS HOURS:MINUTES" |
| DAY_SECOND | "DAYS HOURS:MINUTES:SECONDS" |
SELECT DATE_ADD('2023-12-31 23:59:59', INTERVAL 1 SECOND); -- 2024-01-01 00:00:00
SELECT DATE_SUB('2024-01-01', INTERVAL 1 MONTH); -- 2023-12-01
SELECT '2023-12-31' + INTERVAL 1 DAY; -- 2024-01-01 (MySQL 3.23+ 支援 + - 運算子)
從日期擷取指定單位:
SELECT EXTRACT(YEAR_MONTH FROM '2023-07-02 01:02:03'); -- 202307
SELECT EXTRACT(DAY_MINUTE FROM '2023-07-02 01:02:03'); -- 20102 (20日 01:02)
TO_DAYS(date):日期轉為從 Year 0 起算的天數(不適用 1582 年前)FROM_DAYS(N):天數轉回 DATESELECT TO_DAYS('2023-01-01'); -- 738523
SELECT FROM_DAYS(738523); -- '2023-01-01'
處理 YYMM 或 YYYYMM 格式的期間值:
SELECT PERIOD_ADD(202301, 2); -- 202303
SELECT PERIOD_DIFF(202302, 202203); -- 11
DATE,需含時間用 DATETIME 或 TIMESTAMP善用這些函數,能讓日期相關的商業邏輯在 SQL 層就完成,減少應用程式的處理負擔。