您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

SQL 日期時間差值計算全攻略：DATEDIFF 函數與多維度差值實作

編輯：MySQL 綜合教學

前言

計算兩個日期時間之間的差值是資料分析與業務邏輯中的高頻需求。本文系統整理 DATEDIFF 函數用法，涵盖天、月、年、時、分、秒等多維度差值計算，並提供 Oracle 與 SQL Server 的跨平台寫法對照，以及複雜「天+時間」混合差值的實戰解法。

核心函數：DATEDIFF

語法：DATEDIFF(interval, start_date, end_date)

回傳 start_date 到 end_date 的間隔數（整數）。
interval 支援 day、month、year、hour、minute、second 等。
結果可為負數（若起始日期大於結束日期）。

多維度差值速查範例

-- 相差天數
SELECT DATEDIFF(day, '2008-08-25', '2008-09-01');  -- 7

-- 相差秒數
SELECT DATEDIFF(second, '2009-08-25 12:15:12', '2009-09-01 07:18:20');

-- 相差分鐘數
SELECT DATEDIFF(minute, '2009-08-25 12:15:12', '2009-09-01 07:18:20');

-- 相差小時數
SELECT DATEDIFF(hour, '2009-08-25 12:15:12', '2009-09-01 07:18:20');

Oracle 平台對應寫法

Oracle 無 DATEDIFF，需使用日期相減或 MONTHS_BETWEEN：

-- 相差天數
SELECT TRUNC(SYSDATE) - TO_DATE('2009-01-04', 'YYYY-MM-DD') FROM dual;

-- 相差月數
SELECT MONTHS_BETWEEN(TRUNC(SYSDATE, 'MM'), TO_DATE('2009-01', 'YYYY-MM')) FROM dual;

-- 相差年數
SELECT TRUNC(MONTHS_BETWEEN(TRUNC(SYSDATE, 'DD'), TO_DATE('2009-01-01', 'YYYY-MM-DD')) / 12) FROM dual;

進階實戰：將秒數拆解為「天時分秒」

DECLARE @dt1 DATETIME = '2008-08-04 09:36:41',
        @dt2 DATETIME = '2008-08-02 09:33:39';
DECLARE @sec INT, @day INT, @hour INT, @min INT;

SET @sec = DATEDIFF(second, @dt2, @dt1);
SET @day = @sec / (24*60*60);
SET @sec = @sec % (24*60*60);
SET @hour = @sec / 3600;
SET @sec = @sec % 3600;
SET @min = @sec / 60;
SET @sec = @sec % 60;

SELECT CAST(@day AS VARCHAR) + '天' + CAST(@hour AS VARCHAR) + '時' 
     + CAST(@min AS VARCHAR) + '分' + CAST(@sec AS VARCHAR) + '秒';

複雜場景：「開始天數+時間」到「結束天數+時間」的耗時計算

表結構：beginday INT, begintime VARCHAR, endday INT, endtime VARCHAR

需求：計算 (endday*24h + endtime) - (beginday*24h + begintime)

方案一：DATEDIFF + DATEADD 組合

DECLARE @t TABLE (beginday INT, begintime VARCHAR(20), endday INT, endtime VARCHAR(20));
INSERT @t SELECT 1,'16:00',2,'12:20' UNION ALL SELECT 1,'03:00',3,'19:10';

SELECT RTRIM(diff/60) + ':' + RTRIM(diff%60) AS 耗時
FROM (
    SELECT DATEDIFF(minute, 1, 
        DATEADD(day, endday-beginday, CAST(beginday AS DATETIME)) 
        - CAST(begintime AS DATETIME) + CAST(endtime AS DATETIME)
    ) AS diff FROM @t
) t;

方案二：簡化時數計算（適用於天數較大）

DECLARE @t TABLE (開始天數 VARCHAR(10), 開始時間 VARCHAR(10), 到達天數 VARCHAR(10), 到達時間 VARCHAR(10));
INSERT @t SELECT '1','16:00','2','12:20';

SELECT 到達天數*24 + DATEPART(hour, 到達時間) - 開始天數*24 - DATEPART(hour, 開始時間) AS 總時數
FROM @t;

效能與避坑指南

索引友善：WHERE DATEDIFF(day, col, GETDATE()) < 30 會掃描全表；改為 WHERE col > DATEADD(day, -30, GETDATE()) 可用索引。
型別一致：字串時間請先 CAST(... AS DATETIME)，避免格式錯誤。
跨資料庫移植：Oracle 用 MONTHS_BETWEEN、TRUNC；MySQL 用 TIMESTAMPDIFF、DATEDIFF（僅支援天）。