萬盛學電腦網

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

MySQL 日期時間函數大全:類型選擇、運算、格式化與時區實戰

日期時間類型選擇指南

類型 存儲 範圍 精度 時區感知 推薦場景
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;

性能優化原則

  1. 避免函數包裹列:WHERE DATE(create_time) = '2024-01-15' → 不走索引
    改為:WHERE create_time >= '2024-01-15' AND create_time < '2024-01-16'
  2. 用整數時間戳存儲:BIGINT utc_ms 存儲毫秒時間戳,範圍查詢極快,應用層格式化
  3. 分區表按時間分區:PARTITION BY RANGE (TO_DAYS(create_time)) 實現分區裁剪
  4. 預計算常用維度:在寫入時冗存 year, month, day, hour, week_of_year 列,建立普通索引
copyright © 萬盛學電腦網 all rights reserved