Date + interval returns a type inconsistent with other databases

### Describe the bug

As uncovered in #19460, `SELECT '2001-09-28'::date + interval '1 hour'` returns a Date32 or Date64 type depending on the lhs type whereas other systems return a timestamp:
- 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:

```sql
query T
SELECT arrow_typeof('2001-09-28'::date + interval '1 hour')
----
Timestamp(ns)
```

### To Reproduce

See above sql.

### Expected behavior

date + type should return Timestamp, not date.

### Additional context

_No response_

Start by running the reproduced SQL query through DataFusion and inspect how date-plus-interval expressions resolve their result type. Add a regression test showing that `arrow_typeof('2001-09-28'::date + interval '1 hour')` returns `Timestamp(ns)`, then run the relevant SQL expression tests.

Written by the indexing model from the issue text.