萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 日期時間函數大全:從 DAYOFWEEK 到 DATE_ADD 實戰範例

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

二、日期格式化與運算

DATE_FORMAT(date, format)

依指定格式輸出字串,常用格式代碼:

  • %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';

DATE_ADD / DATE_SUB / ADDDATE / SUBDATE

日期加減運算,支援多種 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+ 支援 + - 運算子)

EXTRACT(unit FROM date)

從日期擷取指定單位:

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):天數轉回 DATE
SELECT TO_DAYS('2023-01-01'); -- 738523
SELECT FROM_DAYS(738523); -- '2023-01-01'

四、期間運算(PERIOD_ADD / PERIOD_DIFF)

處理 YYMM 或 YYYYMM 格式的期間值:

SELECT PERIOD_ADD(202301, 2); -- 202303
SELECT PERIOD_DIFF(202302, 202203); -- 11

實務小撇步

  • 儲存生日用 DATE,需含時間用 DATETIME 或 TIMESTAMP
  • 查詢時盡量用「常數邊界 + 索引欄位」而非函數包裹欄位
  • 跨時區系統建議統一儲存 UTC,應用層轉換顯示

善用這些函數,能讓日期相關的商業邏輯在 SQL 層就完成,減少應用程式的處理負擔。

copyright © 萬盛學電腦網 all rights reserved