單一 SQL 計算兩日期間的時、分、秒差距（Oracle 語法）

需求

在 Oracle 資料庫中，直接用 SQL 取得兩個日期時間之差距，並拆解為「時、分、秒」三個欄位，不使用 PL/SQL，提高可移植性。

核心 SQL

SELECT 
    TO_CHAR(&&start_dt, 'YYYYMMDD HH24:MI:SS') AS start_date,
    TO_CHAR(&&end_dt,   'YYYYMMDD HH24:MI:SS') AS end_date,
    TRUNC((&&start_dt - &&end_dt) * 24) AS hours,
    TRUNC(MOD((&&start_dt - &&end_dt) * 24, 1) * 60) AS minutes,
    TRUNC(MOD(MOD((&&start_dt - &&end_dt) * 24, 1) * 60, 1) * 60) AS seconds
FROM dual;

參數說明

• &&start_dt、&&end_dt：SQL*Plus 替換變數，執行時輸入日期值（如 SYSDATE、SYSDATE - 0.12）。
• Oracle 日期相減單位為「天」，乘以 24 得小時，再取餘數轉換。

執行範例

-- 輸入：
-- start_dt = SYSDATE           (2007-03-15 17:52:16)
-- end_dt   = SYSDATE - 0.12    (2007-03-15 14:59:28)

-- 結果：
START_DATE           END_DATE             HOURS  MINUTES  SECONDS
-------------------- -------------------- ------ -------- -------
20070315 17:52:16    20070315 14:59:28       2       52      48

進階：組合成易讀字串

SELECT 
    TO_CHAR(&&start_dt, 'YYYY-MM-DD HH24:MI:SS') AS start_dt,
    TO_CHAR(&&end_dt,   'YYYY-MM-DD HH24:MI:SS') AS end_dt,
    TRUNC((&&start_dt - &&end_dt) * 24) || '時 ' ||
    TRUNC(MOD((&&start_dt - &&end_dt) * 24, 1) * 60) || '分 ' ||
    TRUNC(MOD(MOD((&&start_dt - &&end_dt) * 24, 1) * 60, 1) * 60) || '秒' AS diff
FROM dual;

SQL Server 對應寫法

DECLARE @start DATETIME = GETDATE(), @end DATETIME = DATEADD(HOUR, -3, GETDATE());
SELECT 
    DATEDIFF(SECOND, @end, @start) / 3600 AS hours,
    (DATEDIFF(SECOND, @end, @start) % 3600) / 60 AS minutes,
    DATEDIFF(SECOND, @end, @start) % 60 AS seconds;

原則

「能用 SQL 解決的就不用 PL/SQL，這樣可移植性很高。」