date_trunc always returns Timestamp(Nanosecond, None) which might truncate ranges #6653

Is your feature request related to a problem or challenge?

Prior to #6632 and #6638 the date_trunc function was inconsistent between reported types and actual types for arrays/scalars

Now it is consistent in the sense that

date_trunc(Timestamp(Second, ..)) --> Timestamp(Nanosecond, None)
date_trunc(Timestamp(Millisecond, ..)) --> Timestamp(Nanosecond, None)
date_trunc(Timestamp(Microsecond ..)) --> Timestamp(Nanosecond, None)
date_trunc(Timestamp(Nanosecond ..)) --> Timestamp(Nanosecond, None)

However, by using Timestamp(Nanosecond, None) everywhere will truncate the range over which date_trunc will work -- specifically if there is some value that can be represented using Timestamp(Second) that doesn't fit into a Timestamp(Nanosecond) such as a value before 1677-09-21 00:12:43.145224193 or after 2262-04-11 23:47:16.854775807 date_trunc will error.

Describe the solution you'd like

We could potentially change the function to keep the same TimeUnit of its input:

Namely

date_trunc(Timestamp(Second, ..)) --> Timestamp(Second, None)
date_trunc(Timestamp(Millisecond, ..)) --> Timestamp(Millisecond, None)
date_trunc(Timestamp(Microsecond ..)) --> Timestamp(Microsecond, None)
date_trunc(Timestamp(Nanosecond ..)) --> Timestamp(Nanosecond, None)

Describe alternatives you've considered

We could also just leave the current behavior

Additional context

See comments #6632 (comment)