question Re: 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 in Support Questions

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

AbhishekSingh — Fri, 08 Jul 2022 15:23:21 GMT

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

Re: 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

SAMSAL — Fri, 08 Jul 2022 19:51:32 GMT

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.

Re: 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

AbhishekSingh — Mon, 11 Jul 2022 17:18:17 GMT

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.

Re: 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

SAMSAL — Mon, 11 Jul 2022 17:32:39 GMT

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

Re: 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

AbhishekSingh — Mon, 11 Jul 2022 18:06:05 GMT

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

Re: 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

SAMSAL — Mon, 11 Jul 2022 18:21:38 GMT

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.