MySQL 日期時間類型與函數大全：從類型選擇到時區轉換的完整參考手冊

MySQL 日期時間類型與函數大全：從類型選擇到時區轉換的完整參考手冊

日期時間處理是業務開發高頻痛點。本文整合類型定義、獲取、提取、計算、格式化、解析、轉換、時區、時間戳全維度函數，附類型選擇決策表與最佳實踐，作為桌面級查閱手冊。

一、核心類型對比與選擇

關鍵差異：TIMESTAMP 存儲 UTC，讀取時按會話時區轉換；DATETIME 存儲字面值，不做時區轉換。5.6.4+ 支持微秒精度 (DATETIME(6))。

二、當前時間獲取函數族

三、組件提取函數 (推薦 EXTRACT() 統一入口)

YEAR/MONTH/DAY/HOUR/MINUTE/SECOND/MICROSECOND(date)
QUARTER/WEEK/DAYOFWEEK/WEEKDAY/DAYOFYEAR/DAYOFMONTH(date)
EXTRACT(unit FROM date) -- unit: YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND, MICROSECOND, 
                        -- YEAR_MONTH, DAY_HOUR, DAY_MINUTE, DAY_SECOND, DAY_MICROSECOND,
                        -- HOUR_MINUTE, HOUR_SECOND, HOUR_MICROSECOND, MINUTE_SECOND, MINUTE_MICROSECOND, SECOND_MICROSECOND

四、日期時間加減運算

DATE_ADD(date, INTERVAL expr unit)  -- 同義詞 ADDDATE
DATE_SUB(date, INTERVAL expr unit)  -- 同義詞 SUBDATE
-- unit: SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR
-- 組合: MINUTE_SECOND, HOUR_MINUTE, DAY_HOUR, YEAR_MONTH, HOUR_SECOND, DAY_MINUTE, DAY_SECOND
-- expr 允許負數實現減法
SELECT DATE_ADD('2024-01-01', INTERVAL -1 DAY); -- 2023-12-31

五、差值計算

DATEDIFF(date1, date2)          -- 天數差 (date1 - date2)
TIMEDIFF(time1, time2)          -- 時間差 (類型必須一致)
TIMESTAMPDIFF(unit, dt1, dt2)   -- 指定單位差值 (功能最強)
-- unit: SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR

六、格式化與解析

DATE_FORMAT(date, format)   -- 日期→字符串
TIME_FORMAT(time, format)   -- 時間→字符串
STR_TO_DATE(str, format)    -- 字符串→日期
-- 常用 format 符號:
%Y 4位年 %y 2位年 %m 月(01-12) %c 月(1-12) %b/%M 月名縮寫/全名
%d 日(01-31) %e 日(1-31) %D 英文後綴
%H 24時(00-23) %h/%I 12時(01-12) %k 24時(0-23) %l 12時(1-12)
%i 分 %S/%s 秒 %f 微秒
%r 12小時制 %T 24小時制 %p AM/PM
%W/%a 週名全寫/縮寫 %w 週幾(0=週日) %j 年內天數 %U/%u 周數

七、時間戳與天數轉換

UNIX_TIMESTAMP([date])          -- 日期→Unix 秒數 (1970 起)
FROM_UNIXTIME(unix_ts, [format]) -- 秒數→日期
TO_DAYS(date)                   -- 日期→紀元天數 (0000-00-00 起)
FROM_DAYS(days)                 -- 天數→日期
TIME_TO_SEC(time)               -- 時間→總秒數
SEC_TO_TIME(seconds)            -- 秒數→時間

八、周期計算 (較少用)

PERIOD_ADD(P, N)    -- YYYYMM + N 月
PERIOD_DIFF(P1, P2) -- 月數差

九、構造日期時間

MAKEDATE(year, dayofyear)   -- 2001, 32 → '2001-02-01'
MAKETIME(hour, minute, second) -- 12,15,30 → '12:15:30'

十、時區轉換

CONVERT_TZ(dt, from_tz, to_tz)  -- 需載入時區表 mysql_tzinfo_to_sql
SELECT CONVERT_TZ('2024-01-01 12:00:00', '+08:00', '+00:00'); -- UTC
-- 等價簡易法:
SELECT DATE_ADD('2024-01-01 12:00:00', INTERVAL -8 HOUR);

十一、時間戳擴展函數

TIMESTAMP(date)            -- DATE→DATETIME (補 00:00:00)
TIMESTAMP(dt, time)        -- DATETIME + TIME
TIMESTAMPADD(unit, n, dt)  -- 同 DATE_ADD 但單位更細
TIMESTAMPDIFF(unit, dt1, dt2) -- 同差值計算

十二、獲取地區化格式

GET_FORMAT({DATE|TIME|DATETIME}, {'EUR'|'USA'|'JIS'|'ISO'|'INTERNAL'})
-- 返回對應 format 字符串，可直接用於 DATE_FORMAT

十三、實戰最佳實踐

1. 存儲 UTC：應用層統一轉 UTC 存 TIMESTAMP 或 DATETIME，展示層按用戶時區轉換。
2. 篩選當天數據：

   
     WHERE created_at >= CURDATE() 
       AND created_at < DATE_ADD(CURDATE(), INTERVAL 1 DAY)
     -- 避免 DATE(created_at) = CURDATE() 導致索引失效
     

3. 分頁游標：WHERE created_at < 'last_seen_ts' ORDER BY created_at DESC LIMIT 20，避免 OFFSET 深分頁。
4. 避免隱式轉換：WHERE date_col = '2024-01-01' 而非 WHERE date_col = 20240101。
5. 定期清理過期分區：按月/日分區 + DROP PARTITION 秒級歸檔。

掌握類型語義邊界、函數組合邏輯、索引友好寫法，是處理好 MySQL 日期時間業務的關鍵。