How to convert String Date "Wed Oct 13 15:58:58 IST 2021" to DATETIME datatype like "2021-10-13 15:58:58" in JsonPathReader to save in MySql DB

[#1]

I am getting String Date "Wed Oct 13 15:58:58 IST 2021" in changeCaptureMySql processor flowfile content out claim which is getting read from mysql-bin.log file as source DB data. Destination DB column has datatype DATETIME. I am using the JsonPathReader and AvroSchemaRegistry to read the each column data to save in db but it is giving data incorrect date-time value error. 

Q1. Is there any away I can convert String date time value to DATETIME to save it without error?

Q2. How to define the DATETIME data type in AvroSchemaRegistry ?. 

I tried below data type in AvroSchemaRegistry but it won't work.

Please find the attached screenshot for reference.

{ "name": "TIMESTAMP" , "type": { "type":"long", "logicalType":"timestamp-millis"} }, { "name": "DATE" , "type": { "type":"int", "logicalType":"date"} }

 

[#2]

Hi,

I ran into this issue before when dealing with ISO timeformat and it would not allow me to write to the DB. In this case you have to use an updateAttribute to convert this format to the format that DB can accept (yyyy-MM-dd HH:mm:ss), this worked for me:

${literal('Wed Oct 13 15:58:58 IST 2021'):replaceAll('(IST)\s',''):toDate('EEE MMM dd HH:mm:ss yyyy'):format('yyyy-MM-dd HH:mm:ss')}

Hope it would help.

[#3]

I tried above conversion but I am getting below error.

AvroSchema: { "name": "enrolled_at", "type": { "type":"int", "logicalType":"date"} },

ErrorLog:

org.apache.nifi.serialization.MalformedRecordException: Successfully parsed a JSON object from input but failed to convert into a Record object with the given schema
at org.apache.nifi.json.AbstractJsonRowRecordReader.nextRecord(AbstractJsonRowRecordReader.java:162)
at org.apache.nifi.serialization.RecordReader.nextRecord(RecordReader.java:50)
at jdk.internal.reflect.GeneratedMethodAccessor653.invoke(Unknown Source)

Caused by: com.jayway.jsonpath.InvalidPathException: Function with name: {literal does not exist. 

[#4]

Hi,

Can you post the Expression Language you used? I'm seeing in the error message that its complaining about the "literal" function doesnt exist. Did you use it with the variable name that contains the original date value? If so then remove the literal function and just keep the variable since literal works with constants. So the expression should be something like this:

${date_variable:replaceAll('(IST)\s',''):toDate('EEE MMM dd HH:mm:ss yyyy'):format('yyyy-MM-dd HH:mm:ss')}

[#5]

I tried again below expressions:

1. $.{enrolled_at:replaceAll('(IST)\s',''):toDate('EEE MMM dd HH:mm:ss yyyy'):format('yyyy-MM-dd HH:mm:ss')} // got error {enrolled_at:replaceAll does not exist.

2. ${enrolled_at:replaceAll('(IST)\s',''):toDate('EEE MMM dd HH:mm:ss yyyy'):format('yyyy-MM-dd HH:mm:ss')} // expression validation failling

[#6 · Accepted solution]

You cant use Expression Language when setting values in the JsonPathReader processor. You need to get the value first as is using the json path  and then use an updateAttribute processor on it using the expression above. In updateAttribute you can use the same variable name or different one.