在使用日期類型參數進行比較大小的時候,務必保證傳遞的日期類型參數和比較的類型保持一致。
在ETL過程中使用日期類型的參數很是常見,例如SQL如下:
SELECT b.display_name,
a.company_id,
date_format(max(first_send_notice_date), ‘%Y-%m’) accMonth,
count(*) totalReport,
sum(CASE WHEN a.takeoff_time > ‘1980’ THEN 1 ELSE 0 END) removed ,
sum(CASE WHEN a.takeoff_time > ‘1980’
AND a.takeoff_time < a.first_send_notice_date + interval 1 DAY THEN 1 ELSE 0 END) 1DAY ,
sum(CASE WHEN a.takeoff_time >=a.first_send_notice_date + interval 1 DAY
AND a.takeoff_time < a.first_send_notice_date + interval 3 DAY THEN 1 ELSE 0 END) 2_3day ,
sum(CASE WHEN a.takeoff_time >=a.first_send_notice_date + interval 3 DAY
AND a.takeoff_time < a.first_send_notice_date + interval 5 DAY THEN 1 ELSE 0 END) 4_5day ,
sum(CASE WHEN a.takeoff_time >=a.first_send_notice_date + interval 5 DAY
AND a.takeoff_time < a.first_send_notice_date + interval 7 DAY THEN 1 ELSE 0 END) 6_7day,
sum(CASE WHEN a.takeoff_time >=a.first_send_notice_date + interval 7 DAY THEN 1 ELSE 0 END) 7DAY,
current_timestamp ETL_DTE
FROM FACT_matchedVideo a
JOIN DIM_trackingWebsite b ON a.trackingWebsite_id = b.trackingWebsite_id
WHERE a.first_send_notice_date >= date_format(‘FIRSTDAYOFLASTMONTH′,‘ANDa.firstsendnoticedate{FIRSTDAYOFCURMONTH}’, ‘%Y-%m-%d’)
AND a.count_send_notice> 0
AND a.hide_flag = 2
AND b.website_type in(‘ugc’,’hybrid’)
GROUP BY 1, 2
其中where過濾條件之一為:
a.first_send_notice_date >= date_format(‘${FIRSTDAYOFLASTMONTH}’, ‘%Y-%m-%d’)
在參數傳遞過來的時候,kettle的日期參數傳遞過來的時候,日期格式為:
‘yyyy-MM-dd HH:mm:ss’ 形式為 ‘2015/05/01 00:00:00’
而數據庫裡面的日期格式為’yyyy-MM-dd HH:mm:ss’
那麼兩者比較的話就會出錯,如下面的錯誤:
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 | +———————————————–+