Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

NiFi: Converting a datetime field (CSV to DB)

avatar
Explorer

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.

1 ACCEPTED SOLUTION

avatar
Explorer

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

View solution in original post

11 REPLIES 11

avatar
Super Guru

This is the proper method... Good job!

avatar
Explorer

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

avatar
Super Guru

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.

avatar
Explorer

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

avatar
Master Collaborator

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

avatar
Contributor

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. InputJson.png

avatar
Explorer

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

avatar
Contributor

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

avatar
Contributor

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