MySQL 中日期相減的完整指南

在 MySQL 中，日期相減有幾種不同的方法，具體取決于你想要得到的結果類型（天數差、時間差等）。

1. 使用 DATEDIFF() 函數（返回天數差）

SELECT DATEDIFF('2023-05-15', '2023-05-10'); -- 返回 5（天數差）

特點：

• 計算兩個日期之間的天數差
• 第一個參數是結束日期，第二個是開始日期
• 結果 = 結束日期 - 開始日期

2. 使用 TIMESTAMPDIFF() 函數（靈活的單位）

-- 計算相差的天數
SELECT TIMESTAMPDIFF(DAY, '2023-05-10', '2023-05-15'); -- 返回 5-- 計算相差的月數
SELECT TIMESTAMPDIFF(MONTH, '2023-01-15', '2023-05-20'); -- 返回 4-- 計算相差的年份
SELECT TIMESTAMPDIFF(YEAR, '2020-01-01', '2023-01-01'); -- 返回 3

支持的單位：

• MICROSECOND
• SECOND
• MINUTE
• HOUR
• DAY
• WEEK
• MONTH
• QUARTER
• YEAR

3. 使用減法運算符（返回時間間隔）

SELECT '2023-05-15 12:00:00' - '2023-05-10 10:30:00';
-- 返回 50230（格式為DDHHMM，不推薦）-- 更安全的做法是轉為日期時間對象再減
SELECT CAST('2023-05-15 12:00:00' AS DATETIME) - CAST('2023-05-10 10:30:00' AS DATETIME);

注意：直接減法在MySQL中行為不一致，不推薦使用

4. 計算時間差（TIMEDIFF）

SELECT TIMEDIFF('12:00:00', '10:30:00'); -- 返回 01:30:00
SELECT TIMEDIFF('2023-05-15 12:00:00', '2023-05-15 10:30:00'); -- 返回 01:30:00

特點：

• 專門計算時間部分差值
• 返回格式為 HH:MM:SS

5. 獲取日期部分并計算

-- 計算兩個日期之間的小時差
SELECT TIMESTAMPDIFF(HOUR, '2023-05-10 08:00', '2023-05-10 17:30'); -- 返回 9-- 計算精確到分鐘
SELECT TIMESTAMPDIFF(MINUTE, '2023-05-10 08:00', '2023-05-10 17:30'); -- 返回 570

實際應用示例

-- 計算訂單處理時長（天）
SELECT order_id, DATEDIFF(shipped_date, order_date) AS processing_days
FROM orders;-- 計算員工年齡（精確到年）
SELECT employee_name, TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) AS age
FROM employees;-- 計算服務時長（精確到小時）
SELECT service_id, TIMESTAMPDIFF(HOUR, start_time, end_time) AS duration_hours
FROM services;

注意事項

1. 日期格式必須正確（推薦使用’YYYY-MM-DD’格式）
2. 處理NULL值時結果會為NULL
3. 對于大日期范圍，TIMESTAMPDIFF比DATEDIFF更精確
4. 時區差異會影響包含時間的計算

性能建議

1. 對日期列建立索引可以提高計算效率
2. 在WHERE條件中使用日期函數會導致索引失效，如：

   -- 不好的寫法（索引失效）
   SELECT * FROM orders WHERE DATEDIFF(CURDATE(), order_date) > 30;-- 好的寫法（可以使用索引）
   SELECT * FROM orders WHERE order_date < DATE_SUB(CURDATE(), INTERVAL 30 DAY);
   

掌握這些日期計算方法，可以高效處理各種與時間相關的數據分析和查詢需求。