Oracle TIMESTAMP vs DATE 精度、運算、顯示全方位對比

DATE 類型

• 精度：秒
• 存儲：7 字節（世紀、年、月、日、時、分、秒）
• 相減結果：天數（含小數），需自行 ×86400 換算秒/分/時。

TIMESTAMP 類型

• 精度：毫微秒（最多 9 位小數秒）
• 存儲：7~11 字節（視小數秒位數）
• 相減結果：INTERVAL DAY TO SECOND 類型，直接可提取天/時/分/秒/毫秒，無需手工換算。

轉換與顯示

-- DATE → TIMESTAMP
SELECT CAST(date_col AS TIMESTAMP) FROM t;

-- 顯示 DATE
SELECT TO_CHAR(date_col, 'MM/DD/YYYY HH24:MI:SS') FROM t;

-- 顯示 TIMESTAMP（含毫秒）
SELECT TO_CHAR(ts_col, 'MM/DD/YYYY HH24:MI:SS:FF3') FROM t;  -- FF3 顯示 3 位毫秒

時間差計算對比

-- DATE 相減：手工拆解
SELECT
  TRUNC(86400*(d2-d1)) - 60*TRUNC(86400*(d2-d1)/60)          AS seconds,
  TRUNC(86400*(d2-d1)/60) - 60*TRUNC(86400*(d2-d1)/3600)      AS minutes,
  TRUNC(86400*(d2-d1)/3600) - 24*TRUNC(86400*(d2-d1)/86400)   AS hours,
  TRUNC(86400*(d2-d1)/86400)                                    AS days
FROM date_table;

-- TIMESTAMP 相減：直接 SUBSTR 提取 INTERVAL 字符串
SELECT
  SUBSTR(t2-t1, INSTR(t2-t1,' ')+7, 2) AS seconds,
  SUBSTR(t2-t1, INSTR(t2-t1,' ')+4, 2) AS minutes,
  SUBSTR(t2-t1, INSTR(t2-t1,' ')+1, 2) AS hours,
  TRUNC(TO_NUMBER(SUBSTR(t2-t1,1,INSTR(t2-t1,' ')))) AS days
FROM date_table;

系統時間函數

• SYSDATE → DATE
• SYSTIMESTAMP → TIMESTAMP WITH TIME ZONE
• ALTER SYSTEM SET FIXED_DATE='2003-01-01-10:00:00'; 僅影響 SYSDATE，不影響 SYSTIMESTAMP。

選型建議

遷移提示

• INSERT SELECT 可直接將 DATE 列寫入 TIMESTAMP 列（自動補 0）。
• TRUNC 不支持 TIMESTAMP，需用 CAST(ts AS DATE) 或 TRUNC(CAST(ts AS DATE))。