Apache NiFi Execute SQL processor bug

[#1]

Hello! 

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!

[#2]

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.

[#3]

I've found a solution, but I think it's not a better way to deal with this.

I've added an option near date type column in sql query (for pgsql) like:

select deal_date at time zone 'UTC-6' as deal_date 

from schema_name.table_name

Why UTC-6 - for equality with 00h00m00s of my country (I've found this by empiric way) then I put this timestamp data to pgtable in column with date data type and that's was fine. 

If you have the best way to deal with this issue just let me know

[#4]

A little bit later we've found that we can cast the date type data just to timestamp without any manipulation with timezones and it will ride into a date type column with correct data. Example:

select deal_date::timestamp as deal_date 

from schema_name.table_name

But if think that in this case you can change column's data type in the table as well and it would be fine - you wrong -> In this case your date data become incorrect again )

[#5]

And also we've found that this problem is an official bug of ExecuteSQL in Apache NiFi 1.7.1  version -> it fixed in 1.10 and later versions.

[#6]

@acoast83 Even with 1.16 iam facing this issue. Any global workaround?.

[#7]

@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.