question NiFi: Converting a datetime field (CSV to DB) in Support Questions

NiFi: Converting a datetime field (CSV to DB)

waltervos — Wed, 29 Aug 2018 21:56:10 GMT

Hi,

With NiFi, I'm picking up a CSV file whose records have a bit of a silly timestamp format: MM-dd-yyyy HH:mm:ss. I want to convert this into yyyy-MM-dd HH:mm:ss before attempting to insert this into a SQL server datetime field.

To do this, I'm trying the ConvertRecord processor, with a CSVReader and currently a JSONRecordSetWriter (convenient since it allows me easily read the resulting data. An example CSV is:

id;timestamp
1;11-12-2016 9:38:13
2;12-12-2016 9:38:14
3;12-21-2016 9:38:15

For the CSVReader, my schema text is:

{    
"type": "record",
    "name": "testdata",
    "fields": [
		{
            "name": "id",
            "type": "int"
        },
        {
            "name": "timestamp",
            "type": {"type":"string","logicalType":"timestamp"}
        }
    ]
}

I've set the property Timestamp format to MM-dd-yyyy HH:mm:ss.

I'm using the same schema text for the JSONRecordSetWriter and have the Timestamp format set to yyyy--MM-dd HH:mm:ss.

I'm expecting this to achieve the datetime conversion that I need to do, but in the resulting JSON my datetime field is exactly the same.

Attached you'll find and XML template that demos this.

Am I going about this the wrong way and completely misunderstanding what ConvertRecord does? Am I missing something?datetime-format-conversion.xml

Edit: I'm still interested to hear your take on this, but as it so often goes I solved the problem a little while after I posted this question. I'm using UpdateRecord and doing:

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

That works for me.

Re: NiFi: Converting a datetime field (CSV to DB)

stevenmatison — Thu, 30 Aug 2018 00:23:54 GMT

This is the proper method... Good job!

Re: NiFi: Converting a datetime field (CSV to DB)

waltervos — Thu, 30 Aug 2018 13:30:28 GMT

Thanks! For the next person trying to accomplish something like this, I've attached another xml template that demonstrates this functionality: updaterecord-datetime-conversion.xml

Re: NiFi: Converting a datetime field (CSV to DB)

stevenmatison — Thu, 30 Aug 2018 18:20:53 GMT

The next thing you can do is write your own answer, then accept it. I believe you will get some profile badges for doing that.

Re: NiFi: Converting a datetime field (CSV to DB)

waltervos — Fri, 31 Aug 2018 14:13:47 GMT

Per @Steven Matison's suggestion, I'm posting the solution to the problem I was facing as an answer:

I was using the wrong processor. It turns out the UpdateRecord processor is a good fit for this. I'm updating the field in question with the following expression language command:

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

Here's an XML template that demonstrates this functionality: updaterecord-datetime-conversion.xml

Re: NiFi: Converting a datetime field (CSV to DB)

justenji — Wed, 10 Jul 2019 20:34:10 GMT

@Walter Vos Thanks, saved me from loosing my mind!

Re: NiFi: Converting a datetime field (CSV to DB)

skiganesh — Thu, 04 Jun 2020 11:58:52 GMT

Hi ,

My input data is a JSON which has value like "Thu Jun 04 11:54:54 UTC 2020". How should I convert this in AvroSchema "YYYY-MM-DD hh:mm:ss".

Re: NiFi: Converting a datetime field (CSV to DB)

skiganesh — Wed, 17 Jun 2020 08:38:29 GMT

Hi @waltervos , I need to achieve this data conversion in updateAttribute processor. Below are my configuration in updateAttribure.

PropertyName: created_date

Value as : ${created_date.value:toDate("EEE MMM dd HH:mm:ss z yyyy"):toNumber():format("yyyy-MM-dd HH:mm:ss.SSS")}

After updateAttribute I have AttributesToJSON processor -> PutDatabaseRecord.

I am not getting the converted value after updateAttribute or AttributesToJSON. Please suggest how to get the updated value in PutDatabaseRecord. Input JSON attached.

Re: NiFi: Converting a datetime field (CSV to DB)

waltervos — Wed, 17 Jun 2020 08:45:42 GMT

Why do you have toNumber() in there, @skiganesh ?

Re: NiFi: Converting a datetime field (CSV to DB)

skiganesh — Wed, 17 Jun 2020 09:25:46 GMT

HI @waltervos , Yes, it worked without converting it to Number also, I was applying the solutions from another post.

Re: NiFi: Converting a datetime field (CSV to DB)

skiganesh — Wed, 17 Jun 2020 10:26:45 GMT

Hi @waltervos , I am not getting date converted in updateAttribute processor. Please suggest.

Re: NiFi: Converting a datetime field (CSV to DB)

skiganesh — Fri, 19 Jun 2020 18:26:26 GMT

"UpdateRecord" processor with below syntax resolved the issue.

${field.value:toDate("EEE MMM dd HH:mm:ss z yyyy"):format("yyyy-MM-dd HH:mm:ss")}