Date - Date returns a type inconsistent with other databases

[Omega359]

Describe the bug

As uncovered in #19460, SELECT '2001-09-28'::date - '2001-08-12'::date returns a Duration type whereas other systems return an int:

https://www.postgresql.org/docs/current/functions-datetime.html
https://duckdb.org/docs/stable/sql/data_types/interval#arithmetic-with-timestamps-dates-and-intervals
https://dev.mysql.com/doc/refman/8.4/en/expressions.html#temporal-intervals

Datafusion should adhere to common conventions for operation types unless there is a really good reason to deviate.

IOW, this should work:

query T
SELECT arrow_typeof('2001-09-28'::date - '2001-08-12'::date)
----
Int64

To Reproduce

See sql above

Expected behavior

date - date should return an int of the days difference between the two dates, not a duration.

Additional context

No response

[kumarUjjawal]

I am taking a look at this.

[kumarUjjawal]

Arrow-rs #4383 - The original feature request designed Date - Date to return a Duration type
DataFusion #6578 - Implemented date-date coercion using Arrow's arithmetic kernel, inheriting the Duration return type

[Omega359]

It may be fine for arrow but Datafusion should attempt to be as standardized as possible.

FYI @alamb since you requested apache/arrow-rs#4383 a couple of years back

[alamb]

Perhaps DataFusion can add a coercion back to int ?

[kumarUjjawal]

Perhaps DataFusion can add a coercion back to int ?

That should be the ideal approach here.