date_part is calculating results incorrectly for intervals

[Omega359]

Describe the bug

Splitting out from #14738 (comment):

SELECT date_part('seconds', interval '1 hour');
-- returns 3600, but the result should be 0
SELECT date_part('seconds', interval '1 hour 5 second');
--- returns 3605, should return 5
SELECT date_part('seconds', interval '3605 seconds');
--- returns 3605, should return 5

It seems that for intervals at least the date part is calculating based on the overall interval, not the specifically requested part. Postgresql has this note about date_part and interval:

When processing an interval value, the extract function produces field values that match the interpretation used by the interval output function. This can produce surprising results if one starts with a non-normalized interval representation, for example:

SELECT INTERVAL '80 minutes';
Result: 01:20:00
SELECT EXTRACT(MINUTES FROM INTERVAL '80 minutes');
Result: 20

https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT

To Reproduce

No response

Expected behavior

I believe DataFusion should mirror PG in this behaviour.

Additional context

No response

[Omega359]

It seems that duckdb is also following the interval rules like pg for date_part - https://duckdb.org/docs/sql/data_types/interval.html

D SELECT datepart('seconds', interval '3605 seconds');
┌───────────────────────────────────────────────────────┐
│ datepart('seconds', CAST('3605 seconds' AS INTERVAL)) │
│                         int64                         │
├───────────────────────────────────────────────────────┤
│                           5                           │
└───────────────────────────────────────────────────────┘

[delamarch3]

I took a look into this and opened an issue in arrow: apache/arrow-rs#7182

[Omega359]

This issue has been resolved with arrow 55.0.0 via apache/arrow-rs#7189