萬盛學電腦網

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

MySQL 日期時間函數大全:提取、計算、格式化、轉換、業務場景實戰

一、日期時間組件提取函數

函數返回值示例結果
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)

常用格式符

符號含義示例
%Y4位年份2024
%y2位年份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
%r12小時制時間02:30:00 PM
%T24小時制時間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)DATETIME2024-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'); -- 需載入時區表

六、性能與最佳實踐

  1. WHERE 條件避免函數包裹列:WHERE DATE(created_at) = '2024-01-15' → 無法用索引。改為範圍查詢(見上)。
  2. 存儲用 UTC (TIMESTAMP/DATETIME),顯示層按用戶時區轉換。
  3. 批量導入用 STR_TO_DATE 統一格式解析。
  4. 避免 SELECT NOW() 等非確定性函數 導致查詢緩存失效(5.7 已移除查詢緩存,8.0 無影響)。
  5. 大表時間範圍分區:`PARTITION BY RANGE (TO_DAYS(created_at))`。

總結

MySQL 日期時間函數體系完整覆蓋 **提取、計算、格式化、解析、轉換、當前時間** 六大類。核心原則:**業務邏輯用高級函數(DATE_ADD、DATE_FORMAT),索引查詢用範圍條件(BETWEEN/>= <),存儲統一 UTC,顯示按時區轉換**。掌握 DATE_FORMAT 格式符、INTERVAL 單位組合、STR_TO_DATE 解析模式,可處理 99% 日期時間業務需求。

copyright © 萬盛學電腦網 all rights reserved