Non-constant `DATE_TRUNC` expression regression for values before epoch

[sadboy]

Describe the bug

On DF 50, DATE_TRUNC function sometimes returns off-by-1 result.

To Reproduce

DataFusion CLI v50.3.0
> select d as datetime,
DATE_TRUNC('hour', d) as hour_from_column,
DATE_TRUNC('hour', (timestamp '1900-06-15 07:09:00')) as hour_from_const
from (values (timestamp '1900-06-15 07:09:00')) as T(d);
+---------------------+---------------------+---------------------+
| datetime            | hour_from_column    | hour_from_const     |
+---------------------+---------------------+---------------------+
| 1900-06-15T07:09:00 | 1900-06-15T08:00:00 | 1900-06-15T07:00:00 |
+---------------------+---------------------+---------------------+
1 row(s) fetched. 
Elapsed 0.006 seconds.

Expected behavior

hour_from_column should have the same value as hour_from_const

Additional context

No response

[sadboy]

Also exists on main as of commit e9431fc

[sadboy]

Caused by #16859 -- removing

datafusion/datafusion/functions/src/datetime/date_trunc.rs

Lines 192 to 216 in 7909909

	if matches!(
	granularity.as_str(),
	// For modern timezones, it's correct to truncate "minute" in this way.
	// Both datafusion and arrow are ignoring historical timezone's non-minute granularity
	// bias (e.g., Asia/Kathmandu before 1919 is UTC+05:41:16).
	"second" | "minute" | "millisecond" | "microsecond"
	) ||
	// In UTC, "hour" and "day" have uniform durations and can be truncated with simple arithmetic
	(parsed_tz.is_none() && matches!(granularity.as_str(), "hour" | "day"))
	{
	let result = general_date_trunc_array_fine_granularity(
	T::UNIT,
	array,
	granularity.as_str(),
	)?;
	return Ok(ColumnarValue::Array(result));
	}
	let array: PrimitiveArray<T> = array
	.try_unary(|x| {
	general_date_trunc(T::UNIT, x, parsed_tz, granularity.as_str())
	})?
	.with_timezone_opt(tz_opt.clone());
	Ok(ColumnarValue::Array(Arc::new(array)))
	}

appears to fix the problem

cc @waynexia @findepi

[comphead]

Thanks @sadboy appreciate if you also add a regression test

[alamb]

FYI @mhilton has 🦸 us and has a proposed fix:

• fix: correct date_trunc for times before the epoch #18356