Convert dates in Apache NiFi

Question

This question is based on my old question: link.

I used solution with custom CSVReader and CSVRecordSetWriter services. A few days ago I noticed that field with date can contains invalid date format, for example instead of date 1/29/2020 3:00:00 AM there can be a value 0. So, UpdateRecord processor can't parse this csv:

AdvertiserID,CampaignStartDate,CampaignEndDate,CampaignName
10730729,1/29/2020 3:00:00 AM,2/20/2020 3:00:00 AM,Nestle
40376079,0,4/1/2020 3:00:00 AM,Heinz
...

I decided to use UpdateRecord without custom CSV services and update date format like this:

/CampaignStartDate ${field.value:equals('0'):ifElse('null', ${field.value:toDate("MM/dd/yyyy hh:mm:ss a"):format("yyyy-MM-dd"))}

So, I want to put if-else logic into UpdateRecord processor. But I met two problems:

1. It writes null as string, but it should be null as data type.
2. It writes ${field.value:toDate("MM/dd/yyyy hh:mm:ss a"):format("yyyy-MM-dd")) as field value

Result is:

AdvertiserID,CampaignStartDate,CampaignEndDate,CampaignName
10730729,${field.value:toDate("MM/dd/yyyy hh:mm:ss a"):format("yyyy-MM-dd")),2/20/2020 3:00:00 AM,Nestle
40376079,null,4/1/2020 3:00:00 AM,Heinz
...

How can I implement if/else logic in Apache NiFi for this case?

Update

I solved problem #2:

${field.value:equals('0')
 :ifElse(
         'null',
         ${field.value:replace(${field.value},${field.value:toDate('M/dd/yyyy hh:mm:ss a'):format('yyyy-MM-dd')})}
)}

But problem #1 is still here. Because I can't save null (it writes as String) to database column with date type. It returns:

ERROR: invalid input syntax for type date: "null" Where: COPY null_place_report_8246a29a_f620_4333_97f5_e47, row 62, column campaign_start_date: "null"

Update #2

Fixed problem #1. Changed expression to:

${field.value:equals('0')
 :ifElse(
         '',
         ${field.value:replace(${field.value},${field.value:toDate('M/dd/yyyy hh:mm:ss a'):format('yyyy-MM-dd')})}
)}

And Reader defines it as null by itself.

Anyway, other solutions?

Answer 1

${field.value:equals('0')
 :ifElse(
         '',
         ${field.value:replace(
            ${field.value},${field.value:toDate('M/dd/yyyy hh:mm:ss a'):format('yyyy-MM-dd')}
          )}
)}