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]

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

[#3]

"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")}