NiFi: Converting a datetime field (CSV to DB)

[#1]

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.

[#2]

This is the proper method... Good job!

[#3]

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

[#4]

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.

[#5 · Accepted solution]

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

[#6]

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

[#7]

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. 

[#8]

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

[#9]

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

[#10]

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