Support Questions
Find answers, ask questions, and share your expertise

NiFi: Converting a datetime field (CSV to DB)

New Contributor

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

New Contributor

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

This is the proper method... Good job!

New Contributor

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

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.

New Contributor

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

Expert Contributor

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

Explorer

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

New Contributor

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

Explorer

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

Explorer

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

Explorer

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

Explorer

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