I've ran into a problem with use Execute SQL NiFi (version 1.7.1) processor .
The problem is in how this processor extracts a date data ----- from a database table. When we use a setting Use Avro Logical Type with true mode then it takes a data from db table with underlying format (and for future needs a mode = true would be a better solution for me), BUT with this mode we have a problem with date data type - it means that it takes this date and do minus one day from this day. For better understanding I'll show you an example with a screenshots.
1. Select from db table = true result
2. Execute SQL proc with Avro Logical Type = true -> gives underlying data format from db table - it's ok for our needs
3. Same sql query like in db client
4. But WRONG Date (minus 1 day) in outgoing flow file
Then if we change Avro Logical Mode to = false we'll see that date would be right BUT data type would be a STRING -> but this way isn't right and convenient for me.
5.Execute SQL proc with Avro Logical Type = false -> converts data from db to STRING data type - it's not ok for our needs
6. But gives for us the right data like from db client (the sql query didn't change)
But when I did cast the date type data to timestamp type my suggestion about timezone was totally confirmed!
7. SQL query with cast to timestamp
8. Time zone -3 hours (our countries true timezone on the Apache NiFi server)
Here's a question - How can I solve this problem without making some crutches ) Thanks!
I think that this is a bug of 1.7.1 version of Execute Sql proc because I've tried to emulate this situation on 1.11.1 NiFi version and have no any problem with date. But I still have no idea how to solve this issue in 1.7.1 version by normal way.
@Althotta, as this is an older post, you would have a better chance of receiving a resolution by starting a new thread. This will also be an opportunity to provide details specific to your environment that could aid others in assisting you with a more accurate answer to your question. You can link this thread as a reference in your new post.
Vidya Sargur, Community Manager
Was your question answered? Make sure to mark the answer as the accepted solution. If you find a reply useful, say thanks by clicking on the thumbs up button. Learn more about the Cloudera Community: