Trying To Solve remove the timestamp format conversion error by using "UpdateAttribute" processor i.e. ConvertJSONtoSQL-> UpdateAttribute--> PutSQL

Question

Same issue but no luck in my case i'm using ConvertJsonToSql---->(Relationship--SQL,Original)---->Update Attribute(Poperty from your answer)------>PutSql. java.sql.SQLDataException :The value of the sql.args.28.value is Mon Oct 09 23:55:23 which cannot be converted to a timestamp.

In Database date is storing in mysql datetime format 2017-09-11 14:26:13.
NiFi flow file from Capture Change Mysql is showing like Mon Sep 11 19:56:13 IST 2017.
JoltTransform Json also same and while converting form json to sql flow file attribute value of sql.args.27.type is 93 and sql.args.27.value is Mon Oct 09 23:38:55.

any suggestions are welcome and appreciated.

there is no day of month or year in your date: `Mon Oct 09 23:55:23`. — daggett, Oct 12 '17 at 10:47
my table datetime column insert date like 2017-09-11 14:26:13 — rakesh, Oct 12 '17 at 10:55
i'm reading mysql bin log and jolt transorm json resulting the attributes like [{ "DiscoveredTime" : "Mon Sep 11 19:56:13 IST 2017", "LatestDiscoveredTime" : "Mon Oct 09 23:38:55 IST 2017" }] — rakesh, Oct 12 '17 at 11:00
After converting json to sql flow file attributes sql.args.27.value is Mon Oct 09 23:38:55 — rakesh, Oct 12 '17 at 11:04
please add the details of your question into the question itself. — daggett, Oct 12 '17 at 12:30
where from you are getting the value like `Mon Sep 11 19:56:13 IST 2017`? can you change it to `2017-09-11 19:56:13` at source? — daggett, Oct 12 '17 at 13:17
In source DB column (datetime type) it is stored as 2017-09-11 19:56:13 but when we retirve the data using Capture Change Mysql, we are getting Sep 11 19:56:13 IST 2017. — rakesh, Oct 12 '17 at 13:51

score 4 · Accepted Answer · answered Oct 17 '17 at 07:21

4

I got answer for this In updateattribute processor property: sql.args.27.value and value :${sql.args.27.value:toDate("EEE MMM dd HH:mm:ss z yyyy"):toNumber():format("yyyy-MM-dd HH:mm:ss.SSS")}

answered Oct 17 '17 at 07:21

rakesh

85
12

Trying To Solve remove the timestamp format conversion error by using "UpdateAttribute" processor i.e. ConvertJSONtoSQL-> UpdateAttribute--> PutSQL

1 Answers1

Linked