核心型別與精度
| 型別 | 儲存大小 | 精度 | 時區感知 | 適用場景 |
|---|
DATE | 7 Bytes | 秒 | 無 | 傳統應用、無時區需求 |
TIMESTAMP | 7-11 Bytes | 奈秒 (9 位小數) | 無 | 高精度、無時區需求 |
TIMESTAMP WITH TIME ZONE | 13 Bytes | 奈秒 | **有** (儲存位移) | 跨時區應用、審計追蹤 |
TIMESTAMP WITH LOCAL TIME ZONE | 11 Bytes | 奈秒 | **有** (正規化至 DB 時區) | 全球化應用、自動轉換 |
格式模型速查 (TO_CHAR / TO_DATE / TO_TIMESTAMP)
| 元素 | 含義 | 範例輸出 |
|---|
YYYY | 4 位年份 | 2024 |
YY | 2 位年份 | 24 |
MM | 2 位月份 | 03 |
MON | 縮寫月名 | MAR |
MONTH | 全寫月名 | MARCH |
DD | 月內第幾天 | 15 |
DY | 縮寫星期 | FRI |
DAY | 全寫星期 | FRIDAY |
HH24 | 24 小時制 | 14 |
HH12 | 12 小時制 | 02 |
MI | 分鐘 | 30 |
SS | 秒 | 59 |
FFn | 小數秒 (n=1..9) | .123456789 |
TZH:TZM | 時區位移 | +08:00 |
常用轉換範例
-- 字串 → DATE
TO_DATE('2024-03-15 14:30:59', 'YYYY-MM-DD HH24:MI:SS')
-- 字串 → TIMESTAMP WITH TIME ZONE
TO_TIMESTAMP_TZ('2024-03-15 14:30:59 +08:00', 'YYYY-MM-DD HH24:MI:SS TZH:TZM')
-- DATE → 字串 (指定語言)
TO_CHAR(SYSDATE, 'FMMonth DD, YYYY', 'NLS_DATE_LANGUAGE=AMERICAN') -- March 15, 2024
-- 數字 Julian Day → 字串
TO_CHAR(TO_DATE(2459999, 'J'), 'Jsp') -- Two Million Four Hundred Fifty-Nine Thousand Nine Hundred Ninety-Nine
日期運算實戰
| 需求 | 語法 | 說明 |
|---|
| 兩日期相差天數 | TRUNC(dt1) - TRUNC(dt2) | 整數天，含小數則為天+分數 |
| 加減天數 | SYSDATE + 7 / SYSDATE - 30 | DATE 型別支援數學運算 |
| 加減月份 | ADD_MONTHS(SYSDATE, 3) | 自動處理月底邊界 (3/31 + 1M = 4/30) |
| 下個星期幾 | NEXT_DAY(SYSDATE, 'FRIDAY') | 語言敏感，建議用數字 1-7 |
| 月最後一天 | LAST_DAY(SYSDATE) | |
| 截取到分/時/日 | TRUNC(SYSDATE, 'MI') / 'HH' / 'DD' | 分組統計必備 |
| 間隔運算 (高精度) | SYSTIMESTAMP - ts_col → INTERVAL DAY TO SECOND | TIMESTAMP 專用 |
NLS 參數與 Session 級控制
-- 查看當前 Session 日期格式
SELECT * FROM nls_session_parameters WHERE parameter LIKE 'NLS_DATE%';
-- 修改當前 Session (最高優先級)
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF3';
ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF3 TZH:TZM';
ALTER SESSION SET NLS_DATE_LANGUAGE = 'AMERICAN'; -- 星期/月份語言
-- 永久修改 (需重啟 Session)
-- 1. sqlnet.ora (Client) : NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS'
-- 2. Registry (Windows) : HKEY_LOCAL_MACHINE\SOFTWARE\Oracle\HOME0\NLS_DATE_FORMAT
-- 3. $ORACLE_HOME/sqlplus/admin/glogin.sql : ALTER SESSION SET ...
**優先級**：Session ALTER SESSION > sqlnet.ora/Registry > Database/Instance 參數 > 安裝預設。**生產代碼請勿依賴 Session 格式，務必顯式 TO_CHAR/TO_DATE 指定格式模型。**
常見陷阱與避坑
BETWEEN '2024-01-01' AND '2024-01-31' **漏掉 1/31 當天非午夜資料** → 改用 dt >= DATE '2024-01-01' AND dt < DATE '2024-02-01'。TO_DATE('2024-02-30', ...) 不報錯但轉成 3/1 → 開啟 ERROR_ON_OVERLAP_TIME (12c+) 或應用層驗證。- 跨時區比較：
TIMESTAMP WITH TIME ZONE 自動正規化，**直接比較即可**，勿手動換算。
EXTRACT(HOUR FROM ts) 回傳 **UTC 小時** 若為 TIMESTAMP WITH TIME ZONE → 用 CAST(ts AS TIMESTAMP WITH LOCAL TIME ZONE) 取本地小時。
結語
Oracle 日期時間處理核心：**選對型別 (TSTZ 首選)、顯式格式模型、Session 格式不可信、邊界條件用半開區間**。封裝通用工具包 (PKG_DT) 提供 TO_DATE_SAFE, DATE_RANGE_START/END, TZ_CONVERT 等函數，統一全專案日期邏輯，杜絕格式錯誤與時區 Bug。
