DB2日期時間函數完全手冊：從基礎抽取到複雜運算與效能優化

前言：為何精通日期時間函數至關重要？

日期時間處理是商業應用最高頻需求：帳齡分析、SLA 計算、分區裁剪、資料歸檔、跨時區報表。DB2 提供豐富的標準 SQL 函數與擴展語法，**善用內建函數可避免應用層迴圈、減少網路傳輸、啟用分區裁剪**，是 SQL 效能優化關鍵。

一、核心屬性抽取函數（標準 SQL）

二、格式化與解析函數（雙向轉換）

常用格式元素： `YYYY` 四位年、`MM` 兩位月、`DD` 兩位日、`HH24` 24小時、`MI` 分鐘、`SS` 秒、`MS` 毫秒、`US` 微秒、`Q` 季、`WW` 週、`D` 週幾(1-7, 1=Sun)、`DY` 週幾縮寫。

三、日期時間運算（英文自然語言語法）

DB2 支援直觀的 **持續時間運算**：

-- 日期運算
CURRENT DATE + 1 YEAR
CURRENT DATE + 3 YEARS + 2 MONTHS + 15 DAYS
CURRENT DATE - 30 DAYS

-- 時間運算
CURRENT TIME + 5 HOURS - 30 MINUTES + 45 SECONDS

-- 時間戳運算
CURRENT TIMESTAMP + 2 DAYS - 3 HOURS

-- 複合：下月同一天
CURRENT DATE + 1 MONTH
-- 複合：本月最後一天
(LAST_DAY(CURRENT DATE))  -- 需 DB2 11.1+ 或自寫 UDF

規則： 年/月運算會處理月末邊界（1/31 + 1 MONTH = 2/28 或 2/29），日/時/分/秒為精確加減。

四、進階分析函數

五、時區處理與 GMT 轉換

DB2 透過特殊暫存器 `CURRENT TIMEZONE`（`DECIMAL(6,0)` 格式 `HHMMSS`，東正西負）支援時區感知：

-- 當地時間 → UTC/GMT
CURRENT TIMESTAMP - CURRENT TIMEZONE
CURRENT TIME - CURRENT TIMEZONE

-- UTC → 當地時間
CURRENT TIMESTAMP + CURRENT TIMEZONE

-- 具體時間戳轉換（假設資料庫在 UTC+8）
SELECT TS_UTC, TS_UTC + CURRENT TIMEZONE AS TS_LOCAL FROM T_LOG;

最佳實務： 資料庫層**統一儲存 UTC**（`TIMESTAMP WITH TIME ZONE` 在 DB2 11.5+ 支援），應用層/報表層按使用者時區轉換顯示。

六、時間差計算：精確 vs 近似

1. 近似計算：TIMESTAMPDIFF（快但不精確）

-- 語法：TIMESTAMPDIFF(間隔類型, 字串化的時間戳差)
-- 間隔類型：1=微秒分數 2=秒 4=分 8=時 16=天 32=週 64=月 128=季 256=年
TIMESTAMPDIFF(16, CHAR(TIMESTAMP('2024-11-30') - TIMESTAMP('2024-11-08')))  -- 回傳 22 天

缺陷： 假設每月 30 天、每年 360 天，**不考慮閏年、實際月長**，跨月/跨年誤差大。

2. 精確計算：DAYS + MIDNIGHT_SECONDS（標準做法）

-- 精確秒差
(DAYS(TS1) - DAYS(TS2)) * 86400 + (MIDNIGHT_SECONDS(TS1) - MIDNIGHT_SECONDS(TS2))

-- 封裝為 UDF（建議部署於 SYSTOOLS 或應用 Schema）
CREATE FUNCTION SECONDS_DIFF(TS1 TIMESTAMP, TS2 TIMESTAMP)
RETURNS BIGINT
LANGUAGE SQL
DETERMINISTIC NO EXTERNAL ACTION
RETURN (DAYS(TS1) - DAYS(TS2)) * 86400 + (MIDNIGHT_SECONDS(TS1) - MIDNIGHT_SECONDS(TS2));

3. 閏年感知：年度天數函數

CREATE FUNCTION DAYS_IN_YEAR(YR INT)
RETURNS INT
LANGUAGE SQL
DETERMINISTIC NO EXTERNAL ACTION
RETURN CASE
  WHEN MOD(YR, 400) = 0 THEN 366
  WHEN MOD(YR, 100) = 0 THEN 365
  WHEN MOD(YR, 4) = 0 THEN 366
  ELSE 365
END;

七、效能優化關鍵：啟用分區裁剪與索引範圍掃描

反模式（導致全表掃描）：

-- 函數套用在欄位上，無法使用索引
WHERE YEAR(ORDER_DATE) = 2024
WHERE DATE(ORDER_TS) = CURRENT DATE
WHERE ORDER_DATE + 30 DAYS > CURRENT DATE

正模式（SARGable，可索引範圍掃描、分區裁剪）：

-- 使用常數範圍
WHERE ORDER_DATE >= '2024-01-01' AND ORDER_DATE < '2025-01-01'
WHERE ORDER_TS >= TIMESTAMP('2024-01-15 00:00:00') AND ORDER_TS < TIMESTAMP('2024-01-16 00:00:00')
WHERE ORDER_DATE > CURRENT DATE - 30 DAYS

原則： **欄位保持「裸露」，運算移至常數側**。優化器才能利用索引、範圍分區、MDC 區塊索引。

八、實用範本庫

-- 1. 本月第一天 00:00:00
TIMESTAMP(DATE(CURRENT TIMESTAMP - (DAY(CURRENT TIMESTAMP)-1) DAYS), '00:00:00')

-- 2. 本月最後一天 23:59:59.999999
TIMESTAMP(LAST_DAY(CURRENT DATE), '23:59:59.999999')

-- 3. 上月同期範圍
WHERE ORDER_DATE BETWEEN 
  DATE(CURRENT DATE - 1 MONTH - (DAY(CURRENT DATE)-1) DAYS) 
  AND LAST_DAY(CURRENT DATE - 1 MONTH)

-- 4. 將字串 '20240115' 安全轉 DATE（含錯誤處理）
CASE 
  WHEN TRANSLATE('20240115', '', '0123456789') = '' AND LENGTH('20240115') = 8
  THEN DATE(SUBSTR('20240115',1,4)||'-'||SUBSTR('20240115',5,2)||'-'||SUBSTR('20240115',7,2))
  ELSE NULL
END

-- 5. 移除 TIMESTAMP 微秒部分
CURRENT TIMESTAMP - MICROSECOND(CURRENT TIMESTAMP) MICROSECONDS

總結

DB2 日期時間函數族群完整支援 ANSI SQL:2011 標準並提供強大擴展。核心掌握三點：

1. 抽取/格式化/解析 靈活雙向轉換
2. 自然語言運算 讓 SQL 可讀、可維護
3. SARGable 寫法 確保索引與分區裁剪生效

將常用邏輯封裝為 UDF（`SECONDS_DIFF`、`DAYS_IN_YEAR`、`FIRST_DAY_OF_MONTH`），建立團隊函數庫，標準化開發、消除重複造輪子、避免時區 Bug。