Search code examples
javaapache-nifi

ERROR: column "DOJ" is of type timestamp without time zone but expression is of type character varying

I'm trying to extract data from oracle db table and insert into postgres db table using nifi

I'm using ExecuteSQLRecord with JsonRecordSetWriter and I'm giving this json data to PutDataBaseRecord with record reader as JsonTreeReader.

My data in queue is like :-

[ {
  "EMP_ID" : "1",
  "DOJ" : "631170000000"
}, {
  "EMP_ID" : "2",
  "DOJ" : "631170000000"
}]

As per usage guide

Any numeric value can be coerced into a Date, Time, or Timestamp type, by assuming that the Long value is the number of milliseconds since epoch (Midnight GMT, January 1, 1970). A String value can be coerced into a Date, Time, or Timestamp type, if its format matches the configured "Date Format," "Time Format," or "Timestamp Format."

But in my case, date is extracting into milliseconds as a String.

Is I'm missing any configuration with respect to JsonRecordSetWriter and JsonTreeReader regarding date/timestamp field.

I have seen options in JsonRecordSetWriter to provide date, time or timestamp format. In my oracle database date field has data like :- 2001-12-01 00:00:00 but how can I provide this value in date attribute of JsonRecordSetWriter, as it is expecting date format same as Java Date format.

How do I overcome my issue, How do I extract data in timezone format from oracle date field or milliseconds but as numeric...

Solution

  • Try to set Use Avro Logical Types to true for ExecuteSQLRecord, in this case, date shouldn't be string according to usage documentation

    enter image description here