Kettle 日期參數使用陷阱：格式不一致導致比較錯誤的根因與修正

Kettle 日期參數使用陷阱：格式不一致導致比較錯誤

在 Kettle (Pentaho Data Integration) ETL 作業中，日期參數傳遞至 SQL 查詢作為過濾條件時，若參數格式與數據庫列格式不一致，會導致邏輯錯誤（如字符串比較而非日期比較）。本文剖析典型錯誤案例、根因及標準化修正方案。

1. 錯誤現場還原

SQL 過濾條件：

WHERE a.first_send_notice_date >= date_format('${FIRSTDAYOFLASTMONTH}', '%Y-%m-%d')

Kettle 日期參數實際傳遞值：'2015/05/01 00:00:00'（含時間、斜杠分隔）
數據庫列 first_send_notice_date 類型：DATETIME，存儲格式 '2015-05-01 00:00:00'（連字符分隔）。

2. 災難性後果：字符串比較

mysql> select '2015/05/01 00:00:00' > '2015-05-02 00:00:00';
+------------------------------------------------------+
| '2015/05/01 00:00:00' > '2015-05-02 00:00:00'     |
+------------------------------------------------------+
| 1                                                    |
+------------------------------------------------------+

字符串按 ASCII 碼逐字符比較，'/' (47) 大於 '-' (45)，導致 2015/05/01 被判定大於 2015-05-02，邏輯完全錯亂。

3. 根因分析

• Kettle 日期參數默認輸出格式：yyyy/MM/dd HH:mm:ss。
• MySQL date_format() 期望輸入格式與目標格式一致，否則隱式轉換為字符串比較。
• 參數未顯式格式化即直接拼接入 SQL，繞過了類型系統保護。

4. 標準化修正方案

方案一：Kettle 轉換步驟中格式化參數（推薦）

# 在「設定變量」或「修改 JavaScript 值」步驟
var formatted = dateFormat(new Date(${FIRSTDAYOFLASTMONTH}), "yyyy-MM-dd");
# 將 formatted 存入新變量供 SQL 使用

方案二：SQL 中強制轉換參數類型

WHERE a.first_send_notice_date >= STR_TO_DATE('${FIRSTDAYOFLASTMONTH}', '%Y/%m/%d %H:%i:%s')
-- 或
WHERE a.first_send_notice_date >= CAST('${FIRSTDAYOFLASTMONTH}' AS DATETIME)

方案三：統一使用 ISO 8601 格式

• Kettle 參數設置為 2015-05-01 00:00:00（連字符）。
• SQL 直接比較：WHERE col >= '${PARAM}'，MySQL 可隱式識別標準格式。

5. 最佳實踐檢查清單

1. ✅ 所有日期參數在進入 SQL 前，統一格式化為 yyyy-MM-dd HH:mm:ss。
2. ✅ 使用 STR_TO_DATE 或 CAST 顯式指定參數類型，避免隱式轉換。
3. ✅ 數據庫列類型優先用 DATE/DATETIME/TIMESTAMP，拒絕字符串存日期。
4. ✅ 單元測試驗證邊界值（月初、月末、閏年、時區）。