Describe the bug
With a month stride, date_bin bins through chrono's DateTime<Utc>, whose range (about ±262,000 years) is much narrower than Timestamp(Second), Timestamp(Millisecond) or Timestamp(Microsecond). Values outside it return NULL, although their bin can be represented in the output type.
This is the same kind of limit as the i64 nanosecond overflow fixed by #25815: a limit of the intermediate representation rather than of the output type, only much narrower. Fixed-duration strides are not affected.
To Reproduce
On #25815 (10000000000000 seconds is about the year 318,857):
-- Month stride: NULL
select arrow_cast(date_bin(interval '1 month', arrow_cast(10000000000000, 'Timestamp(Second)')), 'Int64');
-- NULL
-- Day stride on the same value works
select arrow_cast(date_bin(interval '1 day', arrow_cast(10000000000000, 'Timestamp(Second)')), 'Int64');
-- 9999999936000
(The Int64 cast is only needed because values outside chrono's range cannot be displayed as dates.)
Expected behavior
The month-stride query returns the start of the month containing the value, like the day-stride query does.
Additional context
The month arithmetic in bin_months / date_bin_months_interval_wide (datafusion/functions/src/datetime/date_bin.rs) could use i64 day counts instead of chrono, for example with the civil_from_days / days_from_civil helpers in date_trunc.rs. Then every remaining NULL would be a bin that is outside the output type.
Follow-up from #25815.
Describe the bug
With a month stride,
date_binbins through chrono'sDateTime<Utc>, whose range (about ±262,000 years) is much narrower thanTimestamp(Second),Timestamp(Millisecond)orTimestamp(Microsecond). Values outside it returnNULL, although their bin can be represented in the output type.This is the same kind of limit as the i64 nanosecond overflow fixed by #25815: a limit of the intermediate representation rather than of the output type, only much narrower. Fixed-duration strides are not affected.
To Reproduce
On #25815 (
10000000000000seconds is about the year 318,857):(The
Int64cast is only needed because values outside chrono's range cannot be displayed as dates.)Expected behavior
The month-stride query returns the start of the month containing the value, like the day-stride query does.
Additional context
The month arithmetic in
bin_months/date_bin_months_interval_wide(datafusion/functions/src/datetime/date_bin.rs) could use i64 day counts instead of chrono, for example with thecivil_from_days/days_from_civilhelpers indate_trunc.rs. Then every remainingNULLwould be a bin that is outside the output type.Follow-up from #25815.