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

Oracle DATE 與 TIMESTAMP 差異深度解析：精度、運算、轉換與選擇指南

編輯：Oracle 教學

核心差異速查

特性	DATE	TIMESTAMP
精度	秒	毫秒/微秒（0-9 位，預設 6）
儲存大小	7 bytes	7-11 bytes（視精度）
相減結果	天數（含小數）	INTERVAL DAY TO SECOND 型別
時區感知	無	TIMESTAMP WITH TIME ZONE 可攜帶
TRUNC 支援	是	否（需 CAST 為 DATE）

DATE 型別運算範例

-- 兩日期相減得天數（含小數）
SELECT TO_DATE('2012-07-28 03:12:00','YYYY-MM-DD HH24:MI:SS') - SYSDATE FROM dual;
-- 結果：92.2472685185185 天

-- 需自行換算時分秒
SELECT 
    TRUNC(diff) AS days,
    TRUNC((diff - TRUNC(diff)) * 24) AS hours,
    TRUNC(((diff - TRUNC(diff)) * 24 - hours) * 60) AS minutes,
    ROUND((((diff - TRUNC(diff)) * 24 - hours) * 60 - minutes) * 60) AS seconds
FROM (
    SELECT TO_DATE('2012-07-28 03:12:00','YYYY-MM-DD HH24:MI:SS') - SYSDATE AS diff FROM dual
);

TIMESTAMP 型別運算範例

SELECT TO_TIMESTAMP('2012-07-28 03:12:00','YYYY-MM-DD HH24:MI:SS') - SYSTIMESTAMP FROM dual;
-- 結果：+000000092 05:51:24.032000000
-- 直接可讀：92 天 5 時 51 分 24.032 秒

-- 擷取元件
SELECT EXTRACT(DAY FROM diff) AS days,
       EXTRACT(HOUR FROM diff) AS hours,
       EXTRACT(MINUTE FROM diff) AS minutes,
       EXTRACT(SECOND FROM diff) AS seconds
FROM (
    SELECT TO_TIMESTAMP('2012-07-28 03:12:00','YYYY-MM-DD HH24:MI:SS') - SYSTIMESTAMP AS diff FROM dual
);

系統時間函數

SELECT SYSDATE FROM dual;        -- DATE
SELECT SYSTIMESTAMP FROM dual;   -- TIMESTAMP WITH TIME ZONE

-- 測試用固定時間（僅影響 SYSDATE）
ALTER SYSTEM SET FIXED_DATE = '2024-01-01-10:00:00';

互相轉換

-- TIMESTAMP → DATE（截斷微秒）
SELECT CAST(SYSTIMESTAMP AS DATE) FROM dual;
-- 或
SELECT TO_DATE(TO_CHAR(SYSTIMESTAMP,'YYYY-MM-DD HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS') FROM dual;

-- DATE → TIMESTAMP（微秒補 0）
SELECT CAST(SYSDATE AS TIMESTAMP) FROM dual;
-- 或
SELECT TO_TIMESTAMP(TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS') FROM dual;