一、核心時間類型
| 類型 | 精度 | 時區感知 | 儲存大小 | 適用場景 |
|---|
DATE | 秒 | 無 | 7 位元組 | 傳統業務日期時間 |
TIMESTAMP | 奈秒 (9 位小數) | 無 | 7-11 位元組 | 高精度時間戳 |
TIMESTAMP WITH TIME ZONE | 奈秒 | 是 (存儲時區位移) | 13 位元組 | 跨時區應用 |
TIMESTAMP WITH LOCAL TIME ZONE | 奈秒 | 是 (正規化至資料庫時區) | 7-11 位元組 | 自動時區轉換 |
INTERVAL YEAR TO MONTH | 年月 | — | 5 位元組 | 年月間隔 |
INTERVAL DAY TO SECOND | 奈秒 | — | 11 位元組 | 精確時間間隔 |
二、日期時間輸入輸出
1. TO_DATE / TO_TIMESTAMP
TO_DATE('2024-12-31 23:59:59', 'YYYY-MM-DD HH24:MI:SS')
TO_TIMESTAMP('2024-12-31 23:59:59.123456', 'YYYY-MM-DD HH24:MI:SS.FF6')
TO_TIMESTAMP_TZ('2024-12-31 23:59:59 +08:00', 'YYYY-MM-DD HH24:MI:SS TZH:TZM')
2. TO_CHAR 格式化輸出
TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS')
TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS.FF3 TZR')
3. 常用格式元素
YYYY 年、MM 月、DD 日、HH24 24制時、MI 分、SS 秒、FFn 奈秒 (n=1..9)。TZH:TZM 時區時:分、TZR 時區名稱。
三、核心日期時間函數
1. 當前時間
SYSDATE -- DATE, 資料庫伺服器時間
SYSTIMESTAMP -- TIMESTAMP WITH TIME ZONE
CURRENT_DATE -- DATE, Session 時區時間
CURRENT_TIMESTAMP -- TIMESTAMP WITH TIME ZONE, Session 時區
LOCALTIMESTAMP -- TIMESTAMP, Session 時區
2. 運算與間隔
-- 日期 ± 數字 = 日期 (單位：天)
SYSDATE + 1 -- 明天同一時間
SYSDATE - 1/24 -- 1 小時前
-- 日期 ± INTERVAL
SYSDATE + INTERVAL '1' DAY
SYSTIMESTAMP + INTERVAL '30' MINUTE
-- 日期 - 日期 = 數字 (天數差)
SYSDATE - HIRE_DATE
-- MONTHS_BETWEEN (月份差)
MONTHS_BETWEEN(SYSDATE, HIRE_DATE)
-- ADD_MONTHS (月份加減，自動處理月末)
ADD_MONTHS(SYSDATE, 3)
-- NEXT_DAY / LAST_DAY
NEXT_DAY(SYSDATE, 'FRIDAY')
LAST_DAY(SYSDATE)
3. 截取與取整
TRUNC(SYSDATE) -- 當天 00:00:00
TRUNC(SYSDATE, 'MM') -- 本月 1 號 00:00:00
TRUNC(SYSDATE, 'YYYY') -- 今年 1/1 00:00:00
ROUND(SYSDATE, 'HH') -- 四捨五入至小時
4. 時區轉換
FROM_TZ(TIMESTAMP '2024-01-01 12:00:00', '+08:00') -- 加時區
CAST(FROM_TZ(...) AS TIMESTAMP WITH LOCAL TIME ZONE) -- 轉換至資料庫時區
NEW_TIME(DATE, 'GMT', 'EST') -- 舊版跨時區 (已棄用)
5. 解析元件
EXTRACT(YEAR FROM SYSDATE)
EXTRACT(MONTH FROM SYSDATE)
EXTRACT(DAY FROM SYSDATE)
EXTRACT(HOUR FROM SYSTIMESTAMP)
四、NLS 參數影響
NLS_DATE_FORMAT：DATE 隱式轉字串格式。NLS_TIMESTAMP_FORMAT / NLS_TIMESTAMP_TZ_FORMAT。NLS_TERRITORY：決定週起始日、月名稱語言。- 會話級修改：
ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD';
五、最佳實踐
- 新系統優先使用
TIMESTAMP WITH LOCAL TIME ZONE 統一存儲，應用層僅處理呈現時區。
- 避免隱式轉換：永遠顯式使用
TO_DATE/TO_TIMESTAMP 配合格式模型。
- 日期範圍查詢使用 半開區間：
WHERE dt >= TRUNC(SYSDATE) AND dt < TRUNC(SYSDATE+1)，利用索引且不受時分秒影響。
- 分區表按時間分區時，分區鍵使用
DATE 或 TIMESTAMP，配合 INTERVAL 分區自動擴展。
- 批次處理時間運算盡量在 SQL 層完成，減少 PL/SQL 與 SQL 引擎切換。
