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

2
votes

From This Answer

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.

  1. In Database date is storing in mysql datetime format 2017-09-11 14:26:13.
  2. NiFi flow file from Capture Change Mysql is showing like Mon Sep 11 19:56:13 IST 2017.
  3. 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.

1
apache-nifi
there is no day of month or year in your date: Mon Oct 09 23:55:23. - daggett
my table datetime column insert date like 2017-09-11 14:26:13 - rakesh
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
After converting json to sql flow file attributes sql.args.27.value is Mon Oct 09 23:38:55 - rakesh
what would be your suggestion..? - rakesh

1 Answers

4
votes

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