Skip to main content
SandyClouds
SandyClouds
Gold ⭐️
Mar 28, 2023 6:12 PM UTC
Question

value cannot be converted to a timestamp

I have a mysql destination table with column type datetime.

my nifi flow is like convertJsontoSQL -> putsql (into destination table) The action is delete for putsql.

after conversion of jsontosql i am getting a value '2023-03-28 15:54:28' which is a valid timestamp.

but the flow is failing with below error:

 

duetoThevalue of the sqlargs. 14.value is '2023-03-28 15:54:28',

which cannot be converted to a timestamp; routing to failure: java.sql.SQLDataException:

The value of the sql.args. 14.value is '2023-03-28 15:54:28',

which cannot be converted to a timestamp

- Caused by: java. text. ParseException: Unparseable date: "2023-03-28 15:54:28"

 

I can able to use the same "2023-03-28 15:54:28" for manually inserting into a sample datetime column.

and can able to delete a record with where clause as this value.

I could not understand why Nifi is still not able to convert it to timestamp.

 

Any suggestions are appreciated.

3 replies

joseomjr
joseomjr
Gold ⭐️⭐️
Dec 8, 2023 9:00 PM UTC

Is this "convertJsontoSQL" something custom you've built?

Translate
jonay__reyes
jonay__reyes
Silver ⭐️⭐️⭐️
Jan 25, 2024 2:54 PM UTC

@joseomjr, I'm pretty sure it's a component that has been around from some time: https://nifi.apache.org/docs/nifi-docs/components/org.apache.nifi/nifi-standard-nar/1.6.0/org.apache.nifi.processors.standard.ConvertJSONToSQL/index.html

 

Translate
SAMSAL
SAMSAL
Platinum ⭐️⭐️⭐️
Jan 26, 2024 10:09 AM UTC

Hi @SandyClouds ,

I ran into this issue before and after some research I found that when you do the ConvertJsonToSQL nifi assigns timestamp data type (value = 93 in the sql.args.[n].type attribute ). When the PutSQL runs the generated sql statement it will parse the value according to the assigned type and format it accordingly. However for timestamp it expects it to be in the format of  "yyyy-MM-dd HH:mm:ss.SSS" so if you are missing the milliseconds in the original datetime value it will fail with the specified error message. To resolve the issue make sure to assign 000 milliseconds to your datetime value before running the PUTSQL processor. You can do that in the source Json itself before the conversion to SQL or after conversion to SQL using UpdateAttribute, by using the later option you have to know which sql.args.[n].value will have the datetime and do expression language to reformat.

If that helps please accept solution.

Thanks

 

 

Translate
    Terms & ConditionsAccessibility statement