數據清洗筆記：時間戳字串轉 DATE 的關鍵解析步驟

問題背景

源端「時間意義」欄位存儲 Timestamp 格式字串 (如 2024-06-15 14:30:45.123456)，欄位類型為 VARCHAR2。目標端要求 DATE 類型 (精度至秒)。需提取有效部分並轉換。

Timestamp 字串結構分析

YYYY-MM-DD HH24:MI:SS.FF6
||||||| ||||||| |||||||
||||||| ||||||| └── 微秒 (0-6 位)
||||||| ||||||└──── 秒
||||||| |||||└───── 分
||||||| ||||└────── 時
||||||| |||└─────── 天
||||||| ||└──────── 月
||||||| |└───────── 年
||||||| └────────── 分隔符 (空格/T)
||||||└──────────── 分隔符 (-/.)

轉換策略：截取秒級 + TO_DATE

-- 方法 1: SUBSTR 固定長度截取 (格式固定時最快)
SELECT TO_DATE(SUBSTR(ts_str, 1, 19), 'YYYY-MM-DD HH24:MI:SS') FROM dual;

-- 方法 2: REPLACE 標準化分隔符 + TO_TIMESTAMP 再 CAST (格式多變)
SELECT CAST(TO_TIMESTAMP(REPLACE(ts_str, 'T', ' '), 'YYYY-MM-DD HH24:MI:SS.FF') AS DATE) FROM dual;

-- 方法 3: 正則表達式提取前 19 字符 (11g+)
SELECT TO_DATE(REGEXP_SUBSTR(ts_str, '^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}'), 'YYYY-MM-DD HH24:MI:SS') FROM dual;

處理時區資訊 (TZH:TZM)

-- 帶時區: '2024-06-15 14:30:45.123 +08:00'
SELECT CAST(
  TO_TIMESTAMP_TZ(ts_str, 'YYYY-MM-DD HH24:MI:SS.FF TZH:TZM') 
  AT TIME ZONE 'UTC'  -- 或 SESSIONTIMEZONE
AS DATE) FROM dual;

批次清洗範例

UPDATE staging_table
SET target_date = 
  CASE
    WHEN ts_col LIKE '%:%:%:%' THEN  -- 含微秒
      TO_DATE(SUBSTR(ts_col, 1, 19), 'YYYY-MM-DD HH24:MI:SS')
    WHEN ts_col LIKE '%+%' OR ts_col LIKE '%-%' THEN  -- 含時區
      CAST(TO_TIMESTAMP_TZ(ts_col, 'YYYY-MM-DD HH24:MI:SS.FF TZH:TZM') AT LOCAL AS DATE)
    ELSE
      TO_DATE(ts_col, 'YYYY-MM-DD HH24:MI:SS')
  END
WHERE ts_col IS NOT NULL;

避坑指南

1. NLS 環境：TO_DATE 依賴 NLS_DATE_FORMAT，**永遠顯式指定格式模板**，勿依賴隱式轉換。
2. 精度截斷：DATE 無微秒，直接截斷不四捨五入；若業務需四捨五入，需自行邏輯判斷 FF3 >= 500 進位秒。
3. NULL/空串：TO_DATE('') 報錯，需 NULLIF(TRIM(ts_col), '') 先轉 NULL。
4. 效能：大表更新建議 CTAS 新表 + DROP/RENAME，或分批 WHERE ROWID BETWEEN ... 避免長事務。

Timestamp 字串轉 DATE，核心在於精度截斷邊界確認與格式模板的精確匹配，配合 TO_TIMESTAMP/TO_TIMESTAMP_TZ 處理豐富變體。