日期時間數據類型速查

類型	存儲	格式	範圍	適用場景
DATETIME	8 bytes	YYYY-MM-DD HH:MM:SS	1000-01-01 ~ 9999-12-31	通用日期時間，無時區轉換
TIMESTAMP	4 bytes	YYYY-MM-DD HH:MM:SS	1970-01-01 ~ 2038	自動時區轉換、版本控制、自動更新
DATE	3 bytes	YYYY-MM-DD	1000-01-01 ~ 9999-12-31	純日期（生日、入職）
YEAR	1 byte	YYYY	1901 ~ 2155	年份存儲

獲取當前時間族群

SELECT NOW();              -- 2024-01-15 14:30:45 (日期+時間)
SELECT CURDATE();          -- 2024-01-15 (僅日期)
SELECT CURTIME();          -- 14:30:45 (僅時間)
SELECT UNIX_TIMESTAMP();   -- 1705309845 (Unix 時間戳，秒)
SELECT FROM_UNIXTIME(1705309845); -- 2024-01-15 14:30:45 (時間戳轉日期時間)

日期時間分解提取函數

函數	返回值	示例
YEAR(date)	年份 (4位)	YEAR('2024-01-15') → 2024
MONTH(date)	月份 (1-12)	MONTH('2024-01-15') → 1
MONTHNAME(date)	月份英文名	MONTHNAME('2024-01-15') → January
DAY(date) / DAYOFMONTH()	月內天數 (1-31)	DAY('2024-01-15') → 15
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 (週一開始)
YEARWEEK(date[,mode])	年週組合	YEARWEEK('2024-01-15') → 202403
QUARTER(date)	季度 (1-4)	QUARTER('2024-01-15') → 1
HOUR(time)	小時 (0-23)	HOUR('14:30:45') → 14
MINUTE(time)	分鐘 (0-59)	MINUTE('14:30:45') → 30
SECOND(time)	秒 (0-59)	SECOND('14:30:45') → 45

日期時間運算：加減運算

-- DATE_ADD / DATE_SUB (標準)
SELECT DATE_ADD('2024-01-15', INTERVAL 6 MONTH);        -- 2024-07-15
SELECT DATE_SUB('2024-01-15', INTERVAL 6 HOUR);         -- 2024-01-14 20:00:00

-- 複合間隔
SELECT DATE_ADD('2024-01-15', INTERVAL '12 03:45' DAY_MINUTE); -- 2024-01-27 03:45:00
SELECT DATE_SUB('2024-01-15', INTERVAL '1-4' YEAR_MONTH);      -- 2022-09-15

-- 別名函數
SELECT ADDDATE('2024-01-15', INTERVAL 1 MONTH);  -- 同 DATE_ADD
SELECT SUBDATE('2024-01-15', INTERVAL 1 MONTH);  -- 同 DATE_SUB

間隔單位完整列表

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 SEC_TO_TIME(80);        -- 00:01:20
SELECT SEC_TO_TIME(3720);      -- 01:02:00
SELECT TIME_TO_SEC('24:01:10'); -- 86470 (超過 24 小時也可計算)

實戰常用查詢模式

最近 N 天數據

SELECT * FROM orders 
WHERE TO_DAYS(NOW()) - TO_DAYS(create_time) <= 30;

按月/周/日分組統計

SELECT DATE_FORMAT(create_time, '%Y-%m') AS month, COUNT(*) 
FROM orders GROUP BY month ORDER BY month DESC;

SELECT YEARWEEK(create_time) AS yw, COUNT(*) 
FROM orders GROUP BY yw;

周期計算

SELECT PERIOD_ADD(202401, 3);    -- 202404 (增加 3 個月)
SELECT PERIOD_DIFF(202404, 202312); -- 4 (相差 4 個月)

避坑指南

1. 字符串日期必加引號：YEAR('2024-01-15') 正確，YEAR(2024-01-15) 被解析為數學運算返回 NULL。
2. TIMESTAMP 2038 年問題：長期項目優先用 DATETIME 或 BIGINT 存儲毫秒時間戳。
3. 時區一致性：應用層、連接層、服務端統一時區（SET time_zone = '+08:00'），避免 TIMESTAMP 自動轉換導致跨時區 Bug。
4. 索引失效：WHERE DATE(create_time) = '2024-01-15' 導致索引失效，改用範圍查詢：WHERE create_time >= '2024-01-15' AND create_time < '2024-01-16'。

總結

MySQL 日期時間函數極其豐富，掌握類型選擇、當前時間獲取、分解提取、加減運算、秒數轉換、分組統計核心體系，配合範圍查詢保護索引、時區統一、2038 年規劃，即可輕松應對 99% 的時間業務場景。