MySQL 時間差計算函數 TIMESTAMPDIFF 完整用法指南

函數簽名

TIMESTAMPDIFF(unit, datetime_expr1, datetime_expr2)
-- 返回 datetime_expr2 - datetime_expr1 的整數差值，單位由 unit 指定

支援單位

核心範例

-- 基本用法
SELECT TIMESTAMPDIFF(DAY, '2024-01-01', '2024-01-10');        -- 9
SELECT TIMESTAMPDIFF(HOUR, '2024-01-01 10:00', '2024-01-01 14:30'); -- 4
SELECT TIMESTAMPDIFF(MONTH, '2023-01-15', '2024-03-20');      -- 14
SELECT TIMESTAMPDIFF(YEAR, '2000-02-29', '2024-02-28');       -- 23 (非閏年)

-- 表達式參數
SELECT TIMESTAMPDIFF(SECOND, created_at, NOW()) AS seconds_alive
FROM users WHERE id = 1;

-- 反向順序返回負數
SELECT TIMESTAMPDIFF(DAY, '2024-01-10', '2024-01-01');        -- -9

與 DATEDIFF / TIMEDIFF 比較

實戰場景

1. 用戶存活天數 / 留存分析

SELECT
    user_id,
    TIMESTAMPDIFF(DAY, register_date, last_login_date) AS retention_days
FROM user_stats
WHERE last_login_date IS NOT NULL;

2. 訂單處理時效（分鐘級 SLA）

SELECT
    order_id,
    TIMESTAMPDIFF(MINUTE, created_at, paid_at) AS pay_latency_min,
    CASE
        WHEN TIMESTAMPDIFF(MINUTE, created_at, paid_at) <= 30 THEN 'OnTime'
        ELSE 'Delayed'
    END AS sla_status
FROM orders
WHERE paid_at IS NOT NULL;

3. 年齡計算（精確到年）

SELECT
    name,
    TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) AS age
FROM patients;

4. 分組統計：平均處理時間

SELECT
    department,
    AVG(TIMESTAMPDIFF(SECOND, start_time, end_time)) AS avg_seconds
FROM tasks
WHERE status = 'completed'
GROUP BY department;

注意事項與避坑

1. 參數順序：expr2 - expr1，反了號相反。
2. 截斷非進位：TIMESTAMPDIFF(MONTH, '2024-01-31', '2024-02-01') = 0（未滿一個月曆月）。
3. NULL 傳播：任一參數為 NULL 結果為 NULL，需 COALESCE 處理。
4. 索引優化：WHERE TIMESTAMPDIFF(DAY, created_at, NOW()) < 30 無法用索引。改寫：WHERE created_at > DATE_SUB(NOW(), INTERVAL 30 DAY)。
5. 時區一致性：確保兩參數同一時區（或均為 UTC），跨時區先 CONVERT_TZ。

MySQL 8.0 新增：TIMESTAMPDIFF 支援微秒/納秒

-- MySQL 8.0.28+ 新增單位
SELECT TIMESTAMPDIFF(MICROSECOND, '2024-01-01 00:00:00.000001', '2024-01-01 00:00:00.000002'); -- 1
SELECT TIMESTAMPDIFF(NANOSECOND, ...);  -- 同理