PutSQL time format issue

Hi @ariel12 ,

This is happening because the PutSQL expects the timestamp (if no format is provided) to be in the format of "yyyy-MM-dd HH:mm:ss.SSS" . That is the format passed to the Java timestamp constructor by default when trying to parse the date string. Since the date in your input json is missing the milliseconds ".SSS" and no format is provided the process will fail with the "Unparseable date" error.

To fix this,  you can use different processors as follows:

1- UpdateAttribute

You need to find what sql.args.N.value attribute generated by the ConvertJsonToSQL processor that corresponds to the value of the alertDate, then you can update this value using to UpdateAttribute to the proper expected format by PUTSql. For example, if you find that the alertDate value is stored in sql.args.1.value then , then you can update it using Expression Language to the following:

${sql.args.1.value:toDate("yyyy-MM-dd HH:mm:ss"):format("yyyy-MM-dd HH:mm:ss.SSS")}

And the UpdateAttribute will look like:

Alternatively:  Instead of updating the value, you can update the type for the sql.args.N.type to set it to be as varchar ( value of 12) instead of timestamp (value of 93), this will fix the problem too. Also you can assign format using sql.args.N.format -not provided by default - where you set the format value to "yyyy-MM-dd HH:mm:ss" , the PutSQL will respect the provided format and use it.

2- UpdateRecord:

If you dont like purging default data , or figuring out which sql args the timestamp is getting stored into specially when you have large data set and many timestamp fields where it becomes challenging to do or figure out index based on changing order, Instead you want to do it on the source data before it gets converted to SQL, then you can use UpdateRecord where you have to provide Json reader\writer and add dynamic property that sets the path to the target field and set it accordingly. In your case the UpdateRecod will look like this:

The Value field has the following:

${field.value:toDate("yyyy-MM-dd HH:mm:ss"):format("yyyy-MM-dd HH:mm:ss.SSS")}

Note: the field.value in the expression is provided by this processor to reference the original value of the given path. please refer to UpdateRecod documentation under Additional Details.

3- Jolt Transformation:

Since you asked about using Jolt, you can use it as well to set the data in the source. Since Jolt doesnt have datetime functions you just use string concat to add default value for the .SSS like .000. here is the spec :

[
  {
    "operation": "modify-overwrite-beta",
    "spec": {
      "alertDate": "=concat(@(1,alertDate),'.000')"
    }
  }
]

If that helps please accept solution.

Thanks

@SAMSAL very much , thank you!