在 DB2 UDB 中轉換 UTC 時間戳至本地時間 (Java UDF 實作)

背景與挑戰

• 資料倉庫整合多地區資料，儲存統一 UTC 時間戳 (如 2004-04-04-04.00.00.000000)。
• 報表分析需轉換為各地區本地時間 (含夏令時間 DST)。
• 挑戰：地區名稱別名多 (如 Eastern Daylight/EST/America/New_York)，DST 規則複雜且隨年份變更。

解決方案架構

1. Java UDF：利用 JDK 內建 java.util.TimeZone、Calendar 處理所有时区與 DST 規則。
2. 別名映射表：Hashtable 將各種輸入地區名映射至 JDK 標準 ID (如 EST、America/New_York)。
3. SQL 包裝函數：CREATE FUNCTION F_CONVERT_TIMEZONE(UTC_TS VARCHAR(26), TZ_NAME VARCHAR(50)) RETURNS VARCHAR(26) LANGUAGE JAVA PARAMETER STYLE JAVA EXTERNAL NAME 'MyClass.J_CONVERT_TIMEZONE'。

Java 核心實作

public class TimeZoneConverter {
  private static Hashtable tzMap = new Hashtable();
  static {
    tzMap.put("Eastern Daylight", "EST");
    tzMap.put("Eastern Standard Time", "EST");
    tzMap.put("America/New_York", "EST");
    // ... 250+ 地區映射
  }

  public static String J_CONVERT_TIMEZONE(String utcTs, String tzName) throws Exception {
    // 1. 解析別名
    String jdkTz = (String) tzMap.get(tzName);
    if (jdkTz == null) jdkTz = "GMT";  // 預設 UTC

    // 2. 解析 UTC 時間戳 (格式: yyyy-MM-dd-HH.mm.ss.ffffff)
    String ts = utcTs.replace('.', '-');
    StringTokenizer st = new StringTokenizer(ts, "-");
    int y = Integer.parseInt(st.nextToken());
    int m = Integer.parseInt(st.nextToken()) - 1; // Calendar 月份 0-based
    int d = Integer.parseInt(st.nextToken());
    int h = Integer.parseInt(st.nextToken());
    int min = Integer.parseInt(st.nextToken());
    int s = Integer.parseInt(st.nextToken());
    String micros = st.nextToken(); // 微秒直接透傳

    // 3. 建立 UTC Calendar
    Calendar cal = Calendar.getInstance(TimeZone.getTimeZone("GMT"));
    cal.clear();
    cal.set(y, m, d, h, min, s);

    // 4. 格式化輸出至目標時區
    SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd-HH.mm.ss");
    fmt.setTimeZone(TimeZone.getTimeZone(jdkTz));
    String result = fmt.format(cal.getTime());
    return result + "." + micros; // 附加微秒
  }
}

SQL 部署步驟

1. 編譯 Java：javac TimeZoneConverter.java
2. 建立 JAR：jar cf tzconv.jar TimeZoneConverter.class
3. 複製 JAR 至 DB2 函數目錄：$HOME/sqllib/function/jar/
4. 註冊 JAR：CALL SQLJ.INSTALL_JAR('file:///path/tzconv.jar', 'TZCONV')
5. 建立函數：

   CREATE FUNCTION F_CONVERT_TIMEZONE(UTC_TS VARCHAR(26), TZ_NAME VARCHAR(50))
   RETURNS VARCHAR(26)
   LANGUAGE JAVA
   PARAMETER STYLE JAVA
   EXTERNAL NAME 'TZCONV:TimeZoneConverter.J_CONVERT_TIMEZONE'
   FENCED THREADSAFE;

使用範例

SELECT F_CONVERT_TIMEZONE(TRANSACTION_TS, 'America/Nassau')
FROM TRANSACTION_TABLE;

優勢總結

• 集中管理 250+ 地區別名映射，新增資料源只需更新映射表。
• 自動處理歷年 DST 規則變更 (JDK 定期更新)。
• SQL 呼叫簡單，應用程式無需修改。
• 效能可接受 (單次轉換 ~毫秒)，ETL 批次處理足夠。

注意事項

• JDK 版本需支援目標時區 (JDK 1.1.8 僅支援短名稱 EST；1.4+ 支援長名稱 America/New_York)。
• 微秒精度 Java Calendar 不支援，範例直接透傳字串。
• UDF 定義為 FENCED THREADSAFE，隔離執行不影響引擎。