question Re: NiFi processor: Convert string(datetime format) to long(unix_timestamp) in CSV field in Archives of Support Questions (Read Only)

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

alvinuw — Tue, 27 Jun 2017 23:22:35 GMT

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.

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

ssahi — Tue, 27 Jun 2017 23:58:59 GMT

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.html

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

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

alvinuw — Wed, 28 Jun 2017 00:24:18 GMT

@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.

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

ssahi — Wed, 28 Jun 2017 02:21:38 GMT

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.html

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

mburgess — Thu, 29 Jun 2017 03:16:32 GMT

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).

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

maik_krisanov — Wed, 12 Sep 2018 17:56:54 GMT

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