Kettle date parameter usage

Kettle date parameter usage

When you use the date type parameter to compare the data size, make sure that the passed date type parameter and the comparison type are consistent.

It is common to use date-type parameters in ETL. For example, the SQL statement is as follows:

 
  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 DAYAND 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 DAYAND 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 DAYAND 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_DTEFROM FACT_matchedVideo aJOIN DIM_trackingWebsite b ON a.trackingWebsite_id = b.trackingWebsite_idWHERE a.first_send_notice_date >= date_format(‘FIRSTDAYOFLASTMONTH′,‘ANDa.firstsendnoticedate{FIRSTDAYOFCURMONTH}’, ‘%Y-%m-%d’)AND a.count_send_notice> 0AND a.hide_flag = 2AND b.website_type in(‘ugc’,’hybrid’)GROUP BY 1, 2
 

One of the where filter conditions is:

A. first_send_notice_date> = date_format ('$ {FIRSTDAYOFLASTMONTH}', '% Y-% m-% D ')

When the parameter is passed over, the date format of kettle is:

'Yyyy-MM-dd HH: mm: ss' format: '2017/01 00:00:00'

The date format in the database is 'yyyy-MM-dd HH: mm: ss'

If the two are compared, the following error occurs:

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 |+———————————————–+