日期時間類型選擇指南
| 類型 |
存儲 |
範圍 |
精度 |
時區感知 |
推薦場景 |
| DATETIME |
8 bytes |
1000-01-01 ~ 9999-12-31 |
秒 (8.0+ 支持微秒) |
❌ 無 |
業務時間、日志時間、歷史數據 |
| TIMESTAMP |
4 bytes |
1970-01-01 ~ 2038-01-19 |
秒 (8.0+ 支持微秒) |
✅ 自動轉換 |
創建/更新時間戳、跨時區應用 |
| DATE |
3 bytes |
1000-01-01 ~ 9999-12-31 |
天 |
❌ 無 |
生日、生效日期、統計維度 |
| TIME |
3 bytes |
-838:59:59 ~ 838:59:59 |
秒 (8.0+ 微秒) |
❌ 無 |
每日定時任務、營業時間 |
| YEAR |
1 byte |
1901 ~ 2155 |
年 |
❌ 無 |
年份維度表、歸檔分區 |
💡 選型建議
- 默認用 DATETIME(3) (毫秒精度),範圍大、無時區陷阱、8.0+ 存儲優化
- 自動維護的
created_at/updated_at 用 TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
- 跨時區業務 (國際化 App) 必須用 TIMESTAMP 或應用層統一 UTC 存 DATETIME
獲取當前時間函數
-- 會話級固定值 (推薦,可重複讀)
SELECT NOW(); -- 2024-01-15 14:30:45
SELECT CURRENT_TIMESTAMP(); -- 同 NOW()
-- 實時動態值 (慎用,同一語句多次調用結果不同)
SELECT SYSDATE(); -- 2024-01-15 14:30:45 → sleep(3) → 2024-01-15 14:30:48
-- 日期/時間分離
SELECT CURDATE(); -- 2024-01-15
SELECT CURTIME(); -- 14:30:45
-- UTC 時間 (跨時區基準)
SELECT UTC_TIMESTAMP(); -- 2024-01-15 06:30:45
SELECT UTC_DATE(); -- 2024-01-15
SELECT UTC_TIME(); -- 06:30:45
日期時間提取函數
SET @dt = '2024-01-15 14:30:45.123456';
-- 單字段提取
SELECT YEAR(@dt); -- 2024
SELECT QUARTER(@dt); -- 1
SELECT MONTH(@dt); -- 1
SELECT WEEK(@dt); -- 3 (默認模式 0,周日為週首)
SELECT DAY(@dt); -- 15
SELECT HOUR(@dt); -- 14
SELECT MINUTE(@dt); -- 30
SELECT SECOND(@dt); -- 45
SELECT MICROSECOND(@dt); -- 123456
-- 組合提取 (EXTRACT 通用語法)
SELECT EXTRACT(YEAR_MONTH FROM @dt); -- 202401
SELECT EXTRACT(DAY_HOUR FROM @dt); -- 1514
SELECT EXTRACT(HOUR_MINUTE FROM @dt); -- 1430
SELECT EXTRACT(SECOND_MICROSECOND FROM @dt); -- 45123456
-- 週/天名稱
SELECT DAYNAME(@dt); -- Monday
SELECT MONTHNAME(@dt); -- January
SELECT DAYOFWEEK(@dt); -- 2 (1=周日)
SELECT WEEKDAY(@dt); -- 0 (0=周一)
SELECT DAYOFYEAR(@dt); -- 15
日期時間運算:加減間隔
SET @dt = NOW();
-- DATE_ADD / DATE_SUB (推薦統一語法)
SELECT DATE_ADD(@dt, INTERVAL 1 DAY); -- 明天同一時間
SELECT DATE_ADD(@dt, INTERVAL 2 HOUR); -- 2 小時後
SELECT DATE_ADD(@dt, INTERVAL 30 MINUTE); -- 30 分鐘後
SELECT DATE_ADD(@dt, INTERVAL 1 MONTH); -- 下月同日 (自動處理月末)
SELECT DATE_ADD(@dt, INTERVAL -3 DAY); -- 3 天前 (負間隔)
-- 複合間隔
SELECT DATE_ADD(@dt, INTERVAL '1 12:30:00' DAY_SECOND); -- 1天12時30分後
SELECT DATE_ADD(@dt, INTERVAL '2-6' YEAR_MONTH); -- 2年6月後
-- 專用函數 (語義更清晰)
SELECT ADDDATE(@dt, 10); -- +10 天
SELECT SUBDATE(@dt, 10); -- -10 天
SELECT ADDTIME(@dt, '2:30:00'); -- +2.5 小時
SELECT SUBTIME(@dt, '1:15:00'); -- -1.25 小時
日期時間差值計算
-- 日期差 (天數)
SELECT DATEDIFF('2024-01-15', '2024-01-01'); -- 14
SELECT DATEDIFF('2024-01-01', '2024-01-15'); -- -14
-- 時間差 (TIME 類型,可負)
SELECT TIMEDIFF('14:30:00', '12:00:00'); -- 02:30:00
SELECT TIMEDIFF('12:00:00', '14:30:00'); -- -02:30:00
-- 精確到秒/微秒的差值 (TIMESTAMP 專用)
SELECT TIMESTAMPDIFF(SECOND, '2024-01-01 00:00:00', '2024-01-02 00:00:01'); -- 86401
SELECT TIMESTAMPDIFF(MINUTE, '2024-01-01 00:00:00', '2024-01-02 00:00:01'); -- 1440
SELECT TIMESTAMPDIFF(HOUR, '2024-01-01 00:00:00', '2024-01-02 00:00:01'); -- 24
SELECT TIMESTAMPDIFF(DAY, '2024-01-01 00:00:00', '2024-01-02 00:00:01'); -- 1
-- 週期運算 (YYYYMM 格式)
SELECT PERIOD_ADD(202401, 3); -- 202404
SELECT PERIOD_DIFF(202404, 202401); -- 3
格式化與解析
-- 日期/時間 → 字符串
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s'); -- 2024-01-15 14:30:45
SELECT DATE_FORMAT(NOW(), '%Y%m%d%H%i%s'); -- 20240115143045 (文件名安全)
SELECT DATE_FORMAT(NOW(), '%W, %M %d, %Y'); -- Monday, January 15, 2024
SELECT TIME_FORMAT(NOW(), '%H:%i:%s.%f'); -- 14:30:45.123456
-- 字符串 → 日期/時間 (格式化字符必須匹配)
SELECT STR_TO_DATE('15/01/2024', '%d/%m/%Y'); -- 2024-01-15
SELECT STR_TO_DATE('20240115143045', '%Y%m%d%H%i%s'); -- 2024-01-15 14:30:45
SELECT STR_TO_DATE('01-15-2024 14:30', '%m-%d-%Y %H:%i'); -- 2024-01-15 14:30:00
-- 常用格式符速查
-- %Y: 4位年 %y: 2位年 %m: 2位月 %c: 月(1-12)
-- %d: 2位日 %e: 日(1-31) %H: 24制時 %h: 12制時
-- %i: 分 %s: 秒 %f: 微秒 %p: AM/PM %w: 星期(0-6)
-- %j: 年內天數(001-366) %U: 週數(00-53,周日首) %u: 週數(00-53,周一首)
時區處理實戰
-- 會話級時區設置
SET time_zone = '+08:00'; -- 北京時間
SET time_zone = 'Asia/Shanghai'; -- 具名時區 (需載入時區表)
SET time_zone = 'UTC'; -- 統一用 UTC 存儲
-- 時區轉換
SELECT CONVERT_TZ('2024-01-15 14:30:00', '+08:00', '+00:00'); -- 2024-01-15 06:30:00
SELECT CONVERT_TZ(NOW(), @@session.time_zone, 'UTC'); -- 當前會話轉 UTC
-- 載入系統時區表 (僅需一次)
mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -uroot -p mysql
實戰場景模板
場景 1:按自然日統計 (跨時區安全)
-- 錯誤:WHERE DATE(create_time) = CURDATE() -- 依賴會話時區
-- 正確:統一用 UTC 邊界
SELECT COUNT(*) FROM orders
WHERE create_time >= DATE_SUB(CONVERT_TZ(CURDATE(), '+00:00', @@session.time_zone), INTERVAL 1 DAY)
AND create_time < CONVERT_TZ(CURDATE(), '+00:00', @@session.time_zone);
-- 最佳:應用層傳入 UTC 邊界
-- WHERE create_time >= '2024-01-14 16:00:00' AND create_time < '2024-01-15 16:00:00'
場景 2:獲取本月第一天/最後一天
SELECT DATE_FORMAT(NOW(), '%Y-%m-01'); -- 本月第一天 00:00:00
SELECT LAST_DAY(NOW()); -- 本月最後一天 00:00:00
SELECT DATE_ADD(LAST_DAY(NOW()), INTERVAL 1 DAY); -- 下月第一天
場景 3:計算精確年齡
SELECT FLOOR(DATEDIFF(CURDATE(), birth_date) / 365.25) AS age FROM users;
-- 或更精確:
SELECT TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) AS age FROM users;
場景 4:查找最近 N 個工作日
SELECT DATE_SUB(CURDATE(), INTERVAL seq DAY) AS workday
FROM (
SELECT 0 AS seq UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
) d
WHERE WEEKDAY(DATE_SUB(CURDATE(), INTERVAL seq DAY)) < 5 -- 0-4 為周一到周五
ORDER BY workday DESC LIMIT 5;
性能優化原則
- 避免函數包裹列:
WHERE DATE(create_time) = '2024-01-15' → 不走索引
改為:WHERE create_time >= '2024-01-15' AND create_time < '2024-01-16'
- 用整數時間戳存儲:
BIGINT utc_ms 存儲毫秒時間戳,範圍查詢極快,應用層格式化
- 分區表按時間分區:
PARTITION BY RANGE (TO_DAYS(create_time)) 實現分區裁剪
- 預計算常用維度:在寫入時冗存
year, month, day, hour, week_of_year 列,建立普通索引