NiFi processor: Convert string(datetime format) to long(unix_timestamp) in CSV field

[#1]

Hello,

Is there a processor help with converting a string(Datetime format: 2017-01-18 13:28:17) to long(133434444545) in a csv field?

Thanks.

[#2 · Accepted solution]

Hi @Alvin Jin

I haven't seen a special processor to do this yet, but, I've seen a few examples where this can be done using the NiFi Expression Language. These two links should get you going.

https://community.hortonworks.com/questions/55610/converting-datetime-to-unixepoch-time-milliseconds...

https://community.hortonworks.com/questions/48905/date-problems-with-convertjsontosql-or-putsql-in-n...

[#3]

@Sonu Sahi

Thank you for your response.

${now():toNumber():format('yyyy-MM-dd')}

It seems above expression works for attributes.

How to use it to convert a column in csv file?

Thanks.

[#4]

Hi @Alvin Jin

You could use a combination of ExtractText and ReplaceText with some RegEx, though it might be a bit tedious. There is a good example here with screens that you can use as a guide:

https://community.hortonworks.com/articles/66861/nifi-etl-removing-columns-filtering-rows-changing.h...

[#5]

As of NiFi 1.3.0, you can use UpdateRecord for this. If your incoming field name is "createdOn", you can add a user-defined property named "/createdOn" whose value is the following:

${field.value:toDate('yyyy-mm-dd HH:mm:ss.SSS'):toNumber()}

Note that you may need to change the type of createdOn from String (in the Reader's schema) to Long (in the Writer's schema).

[#6]

How would I use this with a literal letter in my timestamp format? My timestamp format looks like this:

yyyy-mm-dd'T'HH:mm:ss.SSS