You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@datafusion.apache.org by gi...@apache.org on 2026/10/18 11:22:25 UTC
(datafusion) 01/01: Support Preimage for `date_trunc` (#22114)
This is an automated email from the ASF dual-hosted git repository.
github-merge-queue[bot] pushed a commit to branch gh-readonly-queue/main/pr-22114-61c34724fcadf50f0e2eb36e3b03f9c0b675d78c
in repository https://gitbox.apache.org/repos/asf/datafusion.git
commit 0ee39784e8702c0583a73f1a88fcae8356897f56
Author: Kosta Tarasov <33...@users.noreply.github.com>
AuthorDate: Sun Oct 18 11:22:01 2026 +0000
Support Preimage for `date_trunc` (#22114)
## Which issue does this PR close?
<!--
We generally require a GitHub issue to be filed for all bug fixes and
enhancements and this helps us generate change logs for our releases.
You can link an issue to this PR using the GitHub syntax. For example
`Closes #123` indicates that this PR will close issue #123.
-->
- Closes #18319.
## Rationale for this change
Enable pruning for predicates with `date_trunc` on the column side
<!--
Why are you proposing this change? If this is already explained clearly
in the issue then this section is not needed.
Explaining clearly why changes are proposed helps reviewers understand
your changes and offer better suggestions for fixes.
-->
## What changes are included in this PR?
- Added `preimage` implementation for `date_trunc`
- Moved `date_to_scalar` to a common crate
-
<!--
There is no need to duplicate the description in the issue here but it
is sometimes worth providing a summary of the individual changes in this
PR.
-->
## Are these changes tested?
Yes, unit tests
<!--
We typically require tests for all PRs in order to:
1. Prevent the code from being accidentally broken by subsequent changes
2. Serve as another way to document the expected behavior of the code
If tests are not included in your PR, please explain why (for example,
are they covered by existing tests)?
-->
## Are there any user-facing changes?
No
<!--
If there are user-facing changes then we may require documentation to be
updated before approving the PR.
-->
<!--
If there are any breaking changes to public APIs, please add the `api
change` label.
-->
---------
Co-authored-by: Andrew Lamb <an...@nerdnetworks.org>
---
datafusion/common/src/scalar/mod.rs | 236 ++++++++++-
datafusion/functions/src/datetime/date_part.rs | 58 +--
datafusion/functions/src/datetime/date_trunc.rs | 440 +++++++++++++++++++-
.../test_files/datetime/timestamps.slt | 450 +++++++++++++++++++++
4 files changed, 1122 insertions(+), 62 deletions(-)
diff --git a/datafusion/common/src/scalar/mod.rs b/datafusion/common/src/scalar/mod.rs
index 9884e5b0b5..ccac815405 100644
--- a/datafusion/common/src/scalar/mod.rs
+++ b/datafusion/common/src/scalar/mod.rs
@@ -59,6 +59,7 @@ use crate::scalar::consts::{
};
use crate::utils::SingleRowListArrayBuilder;
use crate::{_internal_datafusion_err, arrow_datafusion_err};
+use arrow::array::timezone::Tz;
use arrow::array::{
Array, ArrayData, ArrayDataBuilder, ArrayRef, ArrowNativeTypeOp, ArrowPrimitiveType,
AsArray, BinaryArray, BinaryViewArray, BinaryViewBuilder, BooleanArray, Date32Array,
@@ -83,10 +84,10 @@ use arrow::compute::kernels::numeric::{
};
use arrow::datatypes::{
ArrowDictionaryKeyType, ArrowNativeType, ArrowTimestampType, DataType, Date32Type,
- Decimal32Type, Decimal64Type, Decimal128Type, Decimal256Type, DecimalType, Field,
- FieldRef, Float32Type, Int8Type, Int16Type, Int32Type, Int64Type, IntervalDayTime,
- IntervalDayTimeType, IntervalMonthDayNano, IntervalMonthDayNanoType, IntervalUnit,
- IntervalYearMonthType, MAX_DECIMAL32_FOR_EACH_PRECISION,
+ Date64Type, Decimal32Type, Decimal64Type, Decimal128Type, Decimal256Type,
+ DecimalType, Field, FieldRef, Float32Type, Int8Type, Int16Type, Int32Type, Int64Type,
+ IntervalDayTime, IntervalDayTimeType, IntervalMonthDayNano, IntervalMonthDayNanoType,
+ IntervalUnit, IntervalYearMonthType, MAX_DECIMAL32_FOR_EACH_PRECISION,
MAX_DECIMAL64_FOR_EACH_PRECISION, MAX_DECIMAL128_FOR_EACH_PRECISION,
MAX_DECIMAL256_FOR_EACH_PRECISION, MIN_DECIMAL32_FOR_EACH_PRECISION,
MIN_DECIMAL64_FOR_EACH_PRECISION, MIN_DECIMAL128_FOR_EACH_PRECISION,
@@ -97,7 +98,7 @@ use arrow::datatypes::{
};
use arrow::util::display::{ArrayFormatter, FormatOptions, array_value_to_string};
use cache::{get_or_create_cached_key_array, get_or_create_cached_null_array};
-use chrono::{Duration, NaiveDate};
+use chrono::{Duration, LocalResult, NaiveDate, NaiveDateTime, Offset, TimeZone};
use half::f16;
use num_traits::ToPrimitive;
pub use struct_builder::ScalarStructBuilder;
@@ -109,6 +110,52 @@ const NANOS_PER_DAY: i64 = MICROS_PER_DAY * 1_000;
const MICROS_PER_MILLISECOND: i64 = 1_000;
const NANOS_PER_MILLISECOND: i64 = 1_000_000;
+/// Returns the first instant of `date` in `tz` (UTC if `tz` is `None`) as a
+/// UTC [`NaiveDateTime`].
+///
+/// This is local midnight unless the timezone skips midnight that day (for
+/// example a daylight saving transition from 00:00 to 01:00), in which case
+/// it is the first instant after the gap. If midnight occurs twice (a
+/// transition back from 01:00 to 00:00) the earlier instant is returned.
+///
+/// This follows the convention for local times that fall in a gap or are
+/// ambiguous described in [RFC 5545 §3.3.5].
+///
+/// [RFC 5545 §3.3.5]: https://datatracker.ietf.org/doc/html/rfc5545#section-3.3.5
+fn first_instant_of_date(date: NaiveDate, tz: Option<&Tz>) -> Option<NaiveDateTime> {
+ let midnight = date.and_hms_opt(0, 0, 0)?;
+ let Some(tz) = tz else {
+ return Some(midnight);
+ };
+ match tz.from_local_datetime(&midnight) {
+ LocalResult::Single(dt) => Some(dt.naive_utc()),
+ LocalResult::Ambiguous(earliest, _) => Some(earliest.naive_utc()),
+ LocalResult::None => {
+ // Midnight falls in a gap, so the first instant of the day is the
+ // instant of the transition itself. To find it we need the UTC
+ // offset that was in effect just before the transition to follow
+ // the RFC convention.
+ //
+ // `midnight` is a local time, but here it is deliberately treated
+ // as if it were UTC: we only need *some* UTC instant that is before
+ // the transition and after any earlier transition, and the exact
+ // value does not matter.
+ //
+ // Read as UTC, the transition instant is within |offset| < 1 day of
+ // `midnight`, so `midnight` minus one day is guaranteed to fall
+ // before it. This assumes that transitions are separated by more
+ // than two days, so subtracting a day does not cross an earlier
+ // one.
+ let probe = midnight.checked_sub_signed(Duration::days(1))?;
+ let offset_before = tz.offset_from_utc_datetime(&probe).fix();
+ let transition = midnight.checked_sub_offset(offset_before)?;
+ // Sanity check: the transition must land on or after local midnight
+ (tz.from_utc_datetime(&transition).naive_local() >= midnight)
+ .then_some(transition)
+ }
+ }
+}
+
/// Returns the multiplier that converts the input date representation into the
/// desired timestamp unit, if the conversion requires a multiplication that can
/// overflow an `i64`.
@@ -1446,6 +1493,143 @@ impl ScalarValue {
}
}
+ /// Returns a [`ScalarValue`] of type `target_type` representing `date`.
+ ///
+ /// For `Timestamp` types the result is the first instant of `date` in the
+ /// type's timezone (or UTC when the type has no timezone). This is normally
+ /// midnight, but when the timezone skips midnight (for example a daylight
+ /// saving transition at 00:00) it is the first instant that exists on that
+ /// day, such as 01:00. When midnight occurs twice (a transition back to
+ /// 00:00) the earlier instant is returned.
+ ///
+ /// Returns `None` if `target_type` is not a date or timestamp type, or if
+ /// the result is outside the range of the timestamp unit.
+ ///
+ /// # Example
+ /// ```
+ /// # use std::sync::Arc;
+ /// # use arrow::datatypes::{DataType, TimeUnit};
+ /// # use chrono::NaiveDate;
+ /// # use datafusion_common::ScalarValue;
+ /// let date = NaiveDate::from_ymd_opt(2024, 5, 1).unwrap();
+ ///
+ /// // Dates
+ /// assert_eq!(
+ /// ScalarValue::try_from_date(date, &DataType::Date32),
+ /// Some(ScalarValue::Date32(Some(19844)))
+ /// );
+ ///
+ /// // Timestamps without a timezone are midnight UTC
+ /// assert_eq!(
+ /// ScalarValue::try_from_date(date, &DataType::Timestamp(TimeUnit::Second, None)),
+ /// Some(ScalarValue::TimestampSecond(Some(1714521600), None))
+ /// );
+ ///
+ /// // Timestamps with a timezone are midnight in that timezone
+ /// let tz: Arc<str> = Arc::from("+02:00");
+ /// assert_eq!(
+ /// ScalarValue::try_from_date(
+ /// date,
+ /// &DataType::Timestamp(TimeUnit::Second, Some(Arc::clone(&tz)))
+ /// ),
+ /// Some(ScalarValue::TimestampSecond(Some(1714514400), Some(tz)))
+ /// );
+ ///
+ /// // Other types are not supported
+ /// assert_eq!(ScalarValue::try_from_date(date, &DataType::Int32), None);
+ /// ```
+ pub fn try_from_date(date: NaiveDate, target_type: &DataType) -> Option<Self> {
+ Some(match target_type {
+ DataType::Date32 => {
+ ScalarValue::Date32(Some(Date32Type::from_naive_date(date)))
+ }
+ DataType::Date64 => {
+ ScalarValue::Date64(Some(Date64Type::from_naive_date(date)))
+ }
+ DataType::Timestamp(unit, tz_opt) => {
+ let tz: Option<Tz> = tz_opt.clone().and_then(|s| s.parse().ok());
+ let utc = first_instant_of_date(date, tz.as_ref())?;
+
+ // The date can only be out of range for `TimeUnit::Nanosecond`
+ // as the other units cover the entire range of `NaiveDate`.
+ match unit {
+ TimeUnit::Second => {
+ let value = TimestampSecondType::from_naive_datetime(utc, None)?;
+ ScalarValue::TimestampSecond(Some(value), tz_opt.clone())
+ }
+ TimeUnit::Millisecond => {
+ let value =
+ TimestampMillisecondType::from_naive_datetime(utc, None)?;
+ ScalarValue::TimestampMillisecond(Some(value), tz_opt.clone())
+ }
+ TimeUnit::Microsecond => {
+ let value =
+ TimestampMicrosecondType::from_naive_datetime(utc, None)?;
+ ScalarValue::TimestampMicrosecond(Some(value), tz_opt.clone())
+ }
+ TimeUnit::Nanosecond => {
+ let value =
+ TimestampNanosecondType::from_naive_datetime(utc, None)?;
+ ScalarValue::TimestampNanosecond(Some(value), tz_opt.clone())
+ }
+ }
+ }
+ _ => return None,
+ })
+ }
+
+ /// Returns the internal Arrow representation of a timestamp.
+ ///
+ /// Returns `None` if this value is not a timestamp or is `NULL`.
+ ///
+ /// Returns a tuple of (`i64` value, [`TimeUnit`], and timezone) for a
+ /// non-null timestamp. The `i64` is always the offset from the UTC epoch in
+ /// `unit`s, regardless of the timezone, as defined by the
+ /// [Arrow specification].
+ ///
+ /// [Arrow specification]: https://github.com/apache/arrow/blob/main/format/Schema.fbs
+ ///
+ /// # Example
+ /// ```
+ /// # use std::sync::Arc;
+ /// # use arrow::datatypes::TimeUnit;
+ /// # use datafusion_common::ScalarValue;
+ /// let utc: Arc<str> = Arc::from("UTC");
+ /// let ts = ScalarValue::TimestampSecond(Some(1714521600), Some(Arc::clone(&utc)));
+ /// assert_eq!(
+ /// ts.try_as_timestamp(),
+ /// Some((1714521600, TimeUnit::Second, Some(&utc)))
+ /// );
+ ///
+ /// // Timestamps without a timezone
+ /// let ts = ScalarValue::TimestampMillisecond(Some(1714521600000), None);
+ /// assert_eq!(
+ /// ts.try_as_timestamp(),
+ /// Some((1714521600000, TimeUnit::Millisecond, None))
+ /// );
+ ///
+ /// // NULL timestamps and other types return None
+ /// assert_eq!(ScalarValue::TimestampSecond(None, None).try_as_timestamp(), None);
+ /// assert_eq!(ScalarValue::Int64(Some(1)).try_as_timestamp(), None);
+ /// ```
+ pub fn try_as_timestamp(&self) -> Option<(i64, TimeUnit, Option<&Arc<str>>)> {
+ match self {
+ ScalarValue::TimestampSecond(Some(v), tz) => {
+ Some((*v, TimeUnit::Second, tz.as_ref()))
+ }
+ ScalarValue::TimestampMillisecond(Some(v), tz) => {
+ Some((*v, TimeUnit::Millisecond, tz.as_ref()))
+ }
+ ScalarValue::TimestampMicrosecond(Some(v), tz) => {
+ Some((*v, TimeUnit::Microsecond, tz.as_ref()))
+ }
+ ScalarValue::TimestampNanosecond(Some(v), tz) => {
+ Some((*v, TimeUnit::Nanosecond, tz.as_ref()))
+ }
+ _ => None,
+ }
+ }
+
/// Returns a [`ScalarValue`] representing PI
pub fn new_pi(datatype: &DataType) -> Result<ScalarValue> {
match datatype {
@@ -6052,6 +6236,48 @@ mod tests {
use insta::assert_snapshot;
use rand::Rng;
+ #[test]
+ fn try_from_date_timestamp_dst_at_midnight() {
+ // America/Havana moves its clocks at midnight: in 2024 it sprang
+ // forward 00:00 -> 01:00 on March 10 and fell back 01:00 -> 00:00 on
+ // November 3.
+ let tz: Arc<str> = Arc::from("America/Havana");
+ let ty = DataType::Timestamp(TimeUnit::Second, Some(Arc::clone(&tz)));
+ let ts = |s: &str| {
+ let dt = chrono::DateTime::parse_from_rfc3339(s).unwrap();
+ ScalarValue::TimestampSecond(Some(dt.timestamp()), Some(Arc::clone(&tz)))
+ };
+
+ // Ordinary day: midnight local (-05:00)
+ let date = NaiveDate::from_ymd_opt(2024, 3, 9).unwrap();
+ assert_eq!(
+ ScalarValue::try_from_date(date, &ty),
+ Some(ts("2024-03-09T00:00:00-05:00"))
+ );
+
+ // Midnight is skipped: the first instant of the day is 01:00 (-04:00),
+ // which is the same instant as the (nonexistent) 00:00 -05:00
+ let date = NaiveDate::from_ymd_opt(2024, 3, 10).unwrap();
+ assert_eq!(
+ ScalarValue::try_from_date(date, &ty),
+ Some(ts("2024-03-10T01:00:00-04:00"))
+ );
+
+ // Midnight occurs twice: the earlier instant (-04:00) is returned
+ let date = NaiveDate::from_ymd_opt(2024, 11, 3).unwrap();
+ assert_eq!(
+ ScalarValue::try_from_date(date, &ty),
+ Some(ts("2024-11-03T00:00:00-04:00"))
+ );
+
+ // The day after is back to -05:00
+ let date = NaiveDate::from_ymd_opt(2024, 11, 4).unwrap();
+ assert_eq!(
+ ScalarValue::try_from_date(date, &ty),
+ Some(ts("2024-11-04T00:00:00-05:00"))
+ );
+ }
+
#[test]
fn test_scalar_value_from_for_map() {
let string_builder = StringBuilder::new();
diff --git a/datafusion/functions/src/datetime/date_part.rs b/datafusion/functions/src/datetime/date_part.rs
index 20f62196eb..5b8d32062b 100644
--- a/datafusion/functions/src/datetime/date_part.rs
+++ b/datafusion/functions/src/datetime/date_part.rs
@@ -28,9 +28,9 @@ use arrow::datatypes::DataType::{
};
use arrow::datatypes::TimeUnit::{Microsecond, Millisecond, Nanosecond, Second};
use arrow::datatypes::{
- ArrowTimestampType, DataType, Date32Type, Date64Type, Field, FieldRef,
- IntervalUnit as ArrowIntervalUnit, TimeUnit, TimestampMicrosecondType,
- TimestampMillisecondType, TimestampNanosecondType, TimestampSecondType,
+ ArrowTimestampType, DataType, Field, FieldRef, IntervalUnit as ArrowIntervalUnit,
+ TimeUnit, TimestampMicrosecondType, TimestampMillisecondType,
+ TimestampNanosecondType, TimestampSecondType,
};
use chrono::{Datelike, NaiveDate, Offset};
use datafusion_common::types::{NativeType, logical_date};
@@ -304,8 +304,8 @@ impl ScalarUDFImpl for DatePartFunc {
// Convert to ScalarValues
let (Some(lower), Some(upper)) = (
- date_to_scalar(start_time, target_type),
- date_to_scalar(end_time, target_type),
+ ScalarValue::try_from_date(start_time, target_type),
+ ScalarValue::try_from_date(end_time, target_type),
) else {
return Ok(PreimageResult::None);
};
@@ -431,54 +431,6 @@ fn timezone_part_typed<T: ArrowTimestampType>(
Ok(Arc::new(result))
}
-fn date_to_scalar(date: NaiveDate, target_type: &DataType) -> Option<ScalarValue> {
- Some(match target_type {
- Date32 => ScalarValue::Date32(Some(Date32Type::from_naive_date(date))),
- Date64 => ScalarValue::Date64(Some(Date64Type::from_naive_date(date))),
-
- Timestamp(unit, tz_opt) => {
- let naive_midnight = date.and_hms_opt(0, 0, 0)?;
- let tz: Option<Tz> = tz_opt.clone().and_then(|s| s.parse().ok());
-
- // Midnight has no representation when the date falls outside the
- // unit's range, or when the zone skips it. Reporting no bound keeps
- // the caller on the original predicate; a NULL bound here would make
- // the rewritten comparison NULL on every row.
- match unit {
- Second => ScalarValue::TimestampSecond(
- Some(TimestampSecondType::from_naive_datetime(
- naive_midnight,
- tz.as_ref(),
- )?),
- tz_opt.clone(),
- ),
- Millisecond => ScalarValue::TimestampMillisecond(
- Some(TimestampMillisecondType::from_naive_datetime(
- naive_midnight,
- tz.as_ref(),
- )?),
- tz_opt.clone(),
- ),
- Microsecond => ScalarValue::TimestampMicrosecond(
- Some(TimestampMicrosecondType::from_naive_datetime(
- naive_midnight,
- tz.as_ref(),
- )?),
- tz_opt.clone(),
- ),
- Nanosecond => ScalarValue::TimestampNanosecond(
- Some(TimestampNanosecondType::from_naive_datetime(
- naive_midnight,
- tz.as_ref(),
- )?),
- tz_opt.clone(),
- ),
- }
- }
- _ => return None,
- })
-}
-
// Try to remove quote if exist, if the quote is invalid, return original string and let the downstream function handle the error
fn part_normalization(part: &str) -> &str {
part.strip_prefix(|c| c == '\'' || c == '\"')
diff --git a/datafusion/functions/src/datetime/date_trunc.rs b/datafusion/functions/src/datetime/date_trunc.rs
index 082671cdc0..3d304989af 100644
--- a/datafusion/functions/src/datetime/date_trunc.rs
+++ b/datafusion/functions/src/datetime/date_trunc.rs
@@ -23,6 +23,7 @@ use std::sync::Arc;
use crate::datetime::common::parse_tz;
use arrow::array::temporal_conversions::{
MICROSECONDS, MILLISECONDS, NANOSECONDS, as_datetime_with_timezone,
+ timestamp_ns_to_datetime,
};
use arrow::array::timezone::Tz;
use arrow::array::types::{
@@ -32,24 +33,28 @@ use arrow::array::types::{
};
use arrow::array::{Array, ArrayRef, PrimitiveArray};
use arrow::compute::DatePart;
-use arrow::datatypes::DataType::{self, Time32, Time64, Timestamp};
+use arrow::datatypes::DataType::{self, Date32, Date64, Time32, Time64, Timestamp};
use arrow::datatypes::TimeUnit::{self, Microsecond, Millisecond, Nanosecond, Second};
use arrow::datatypes::{Field, FieldRef};
use datafusion_common::cast::as_primitive_array;
use datafusion_common::types::{NativeType, logical_date, logical_string};
+use datafusion_common::utils::take_function_args;
use datafusion_common::{
DataFusionError, Result, ScalarValue, exec_datafusion_err, exec_err, internal_err,
};
+use datafusion_expr::preimage::PreimageResult;
+use datafusion_expr::simplify::SimplifyContext;
use datafusion_expr::sort_properties::{ExprProperties, SortProperties};
use datafusion_expr::{
- ColumnarValue, Documentation, ReturnFieldArgs, ScalarFunctionArgs, ScalarUDFImpl,
- Signature, TypeSignature, Volatility,
+ Cast, ColumnarValue, Documentation, Expr, ReturnFieldArgs, ScalarFunctionArgs,
+ ScalarUDFImpl, Signature, TypeSignature, Volatility, interval_arithmetic::Interval,
};
use datafusion_expr_common::signature::{Coercion, TypeSignatureClass};
use datafusion_macros::user_doc;
use chrono::{
- DateTime, Datelike, Duration, LocalResult, NaiveDateTime, Offset, TimeDelta, Timelike,
+ DateTime, Datelike, Duration, LocalResult, Months, NaiveDate, NaiveDateTime, Offset,
+ TimeDelta, Timelike, Weekday,
};
fn parse_granularity(value: &str) -> Result<DatePart> {
@@ -85,6 +90,7 @@ trait DateTruncGranularityExt {
fn is_fine_granularity(&self) -> bool;
fn is_fine_granularity_utc(&self) -> bool;
fn valid_for_time(&self) -> bool;
+ fn has_variable_width_in_tz(&self) -> bool;
}
impl DateTruncGranularityExt for DatePart {
@@ -118,6 +124,21 @@ impl DateTruncGranularityExt for DatePart {
| DatePart::Microsecond
)
}
+
+ /// Returns true if `preimage` can not compute the bucket boundaries for
+ /// this granularity in a non-UTC timezone.
+ ///
+ /// * `Day` and `Week`: the bucket is normally a fixed number of hours, but
+ /// can be 23 or 25 hours long on a DST transition day.
+ /// * `Month`, `Quarter` and `Year`: the bucket boundary is local midnight on
+ /// for the first instance, but [`next_calendar_boundary_utc`] computes the
+ /// boundary in UTC, which varies across timezones.
+ fn has_variable_width_in_tz(&self) -> bool {
+ matches!(
+ self,
+ Self::Day | Self::Week | Self::Month | Self::Quarter | Self::Year
+ )
+ }
}
#[user_doc(
@@ -436,6 +457,105 @@ impl ScalarUDFImpl for DateTruncFunc {
&self.aliases
}
+ /// Compute the preimage for `date_trunc(granularity, col) = literal`.
+ ///
+ /// `date_trunc` floors a timestamp to the start of the given period, so
+ /// for any aligned literal `L` the preimage is the half-open interval
+ /// `[L, L + 1 granularity)`.
+ ///
+ /// The simplifier turns that interval into a range predicate on `col`,
+ /// for example:
+ ///
+ /// ```text
+ /// date_trunc('month', ts) = '2024-05-01'
+ /// --> ts >= '2024-05-01' AND ts < '2024-06-01'
+ ///
+ /// date_trunc('hour', ts) = '2024-05-01T10:00:00'
+ /// --> ts >= '2024-05-01T10:00:00' AND ts < '2024-05-01T11:00:00'
+ ///
+ /// date_trunc('day', ts) > '2024-05-01'
+ /// --> ts >= '2024-05-02'
+ ///
+ /// date_trunc('year', ts) != '2024-01-01'
+ /// --> ts < '2024-01-01' OR ts >= '2025-01-01'
+ /// ```
+ ///
+ /// Returns [`PreimageResult::None`], leaving the predicate unchanged, when
+ /// no exact interval exists. See `timestamp_preimage_bounds` for the
+ /// conditions.
+ fn preimage(
+ &self,
+ args: &[Expr],
+ lit_expr: &Expr,
+ info: &SimplifyContext,
+ ) -> Result<PreimageResult> {
+ let [precision, col_expr] = take_function_args(self.name(), args)?;
+
+ // Granularity literal (e.g. "month").
+ let Some(granularity) = precision
+ .as_literal()
+ .and_then(|sv| sv.try_as_str().flatten())
+ .and_then(|s| parse_granularity(s).ok())
+ else {
+ return Ok(PreimageResult::None);
+ };
+
+ // Try to extract bounds for date columns
+ if let Some(result) = date_column_preimage(col_expr, lit_expr, granularity, info)
+ {
+ return Ok(result);
+ }
+
+ // Try to extract bounds for time columns
+ if let Some(result) = time_column_preimage(col_expr, lit_expr, granularity, info)
+ {
+ return Ok(result);
+ }
+
+ // The bounds must match the column's type
+ let target_type = info.get_data_type(col_expr)?;
+ let (unit, tz_opt) = match &target_type {
+ Timestamp(unit, tz) => (*unit, tz.clone()),
+ _ => return Ok(PreimageResult::None),
+ };
+
+ // Some buckets in a timezone can not be computed with a fixed step
+ // (e.g. DST makes some days 23 or 25 hours long) or with UTC calendar
+ // arithmetic (local month boundaries are not UTC month boundaries).
+ //
+ // Stay conservative for those, unless the timezone is explicitly UTC,
+ // where the buckets are the same as without a timezone.
+ if tz_opt.as_ref().is_some_and(|tz| !is_utc(tz))
+ && granularity.has_variable_width_in_tz()
+ {
+ return Ok(PreimageResult::None);
+ }
+
+ // Extract the literal value and verify it matches the column's type.
+ let Some((value, lit_unit, lit_tz)) =
+ lit_expr.as_literal().and_then(|lit| lit.try_as_timestamp())
+ else {
+ return Ok(PreimageResult::None);
+ };
+ if lit_unit != unit || lit_tz != tz_opt.as_ref() {
+ return Ok(PreimageResult::None);
+ }
+
+ let Some((lower, upper)) =
+ timestamp_preimage_bounds(unit, value, tz_opt.as_ref(), granularity)
+ else {
+ return Ok(PreimageResult::None);
+ };
+
+ Ok(PreimageResult::Range {
+ expr: col_expr.clone(),
+ interval: Box::new(Interval::try_new(
+ timestamp_to_scalar(unit, lower, tz_opt.as_ref()),
+ timestamp_to_scalar(unit, upper, tz_opt.as_ref()),
+ )?),
+ })
+ }
+
fn output_ordering(&self, input: &[ExprProperties]) -> Result<SortProperties> {
let precision = &input[0];
let date_value = &input[1];
@@ -587,6 +707,318 @@ where
1 + 3 * ((date.month() - 1) / 3)
}
+/// Returns true if `tz` explicitly names UTC (`"UTC"` or `"+00:00"`)
+fn is_utc(tz: &str) -> bool {
+ tz.eq_ignore_ascii_case("utc") || tz == "+00:00"
+}
+
+/// Extract an `i64` timestamp value from a date literal wrapped in a CAST
+/// (e.g. `Cast(date_col, Timestamp(ns))`)
+///
+/// Needed because `unwrap_cast_in_comparison` doesn't currently support Date
+/// targets, see <https://github.com/apache/datafusion/issues/26289>. Without this
+/// case, the plan contains a cast which blocks pruning on Date columns.
+///
+/// Returns `None` if the column isn't a Date wrapped in the expected cast, or
+/// if the literal isn't a midnight-UTC value aligned to the granularity
+/// boundary.
+fn date_column_preimage(
+ col_expr: &Expr,
+ lit_expr: &Expr,
+ granularity: DatePart,
+ info: &SimplifyContext,
+) -> Option<PreimageResult> {
+ // The signature wraps Date columns as exactly Cast(_, Timestamp(Nanosecond, None)).
+ let Expr::Cast(Cast { expr: inner, field }) = col_expr else {
+ return None;
+ };
+ if !matches!(field.data_type(), Timestamp(Nanosecond, None)) {
+ return None;
+ }
+ let inner_type = info.get_data_type(inner).ok()?;
+ if !matches!(inner_type, Date32 | Date64) {
+ return None;
+ }
+
+ // The post-coercion literal is Timestamp(ns) with no tz. Anything not at
+ // midnight UTC can never equal date_trunc(g, date_col); bail so the
+ // unsimplified predicate runs and returns its (empty) result.
+ let (ts_ns, Nanosecond, None) = lit_expr.as_literal()?.try_as_timestamp()? else {
+ return None;
+ };
+ let dt = timestamp_ns_to_datetime(ts_ns)?;
+ if dt.time() != chrono::NaiveTime::from_hms_opt(0, 0, 0)? {
+ return None;
+ }
+ let lower = dt.date();
+
+ let upper = date_preimage_upper(lower, granularity)?;
+
+ Some(PreimageResult::Range {
+ expr: inner.as_ref().clone(),
+ interval: Box::new(
+ Interval::try_new(
+ ScalarValue::try_from_date(lower, &inner_type)?,
+ ScalarValue::try_from_date(upper, &inner_type)?,
+ )
+ .ok()?,
+ ),
+ })
+}
+
+/// Upper bound (exclusive) of the date bucket starting at `lower` for the
+/// given granularity. Returns `None` if `lower` isn't aligned to the bucket
+/// boundary (e.g. month-granularity with day != 1).
+///
+/// Date columns have day-level precision, so every value casts to midnight
+/// UTC. Since `date_trunc(any_g, midnight)` is still midnight, sub-day
+/// granularities collapse to a 1-day interval (same as `Day`).
+fn date_preimage_upper(lower: NaiveDate, granularity: DatePart) -> Option<NaiveDate> {
+ match granularity {
+ DatePart::Microsecond
+ | DatePart::Millisecond
+ | DatePart::Second
+ | DatePart::Minute
+ | DatePart::Hour
+ | DatePart::Day => lower.succ_opt(),
+ DatePart::Week => {
+ if lower.weekday() != Weekday::Mon {
+ return None;
+ }
+ lower.checked_add_signed(Duration::days(7))
+ }
+ DatePart::Month => {
+ if lower.day() != 1 {
+ return None;
+ }
+ lower.checked_add_months(Months::new(1))
+ }
+ DatePart::Quarter => {
+ if lower.day() != 1 || !matches!(lower.month(), 1 | 4 | 7 | 10) {
+ return None;
+ }
+ lower.checked_add_months(Months::new(3))
+ }
+ DatePart::Year => {
+ if lower.day() != 1 || lower.month() != 1 {
+ return None;
+ }
+ lower.checked_add_months(Months::new(12))
+ }
+ // `parse_granularity` rejects every other `DatePart`.
+ _ => None,
+ }
+}
+
+/// Emit constant-step preimage bounds for `date_trunc(g, time_col) = lit` on
+/// `Time32`/`Time64` columns. Time has no timezone and no calendar arithmetic,
+/// so every supported granularity (Hour and finer) is a fixed-width bucket.
+fn time_column_preimage(
+ col_expr: &Expr,
+ lit_expr: &Expr,
+ granularity: DatePart,
+ info: &SimplifyContext,
+) -> Option<PreimageResult> {
+ let target_type = info.get_data_type(col_expr).ok()?;
+ let unit = match &target_type {
+ Time32(unit) | Time64(unit) => *unit,
+ _ => return None,
+ };
+ if !granularity.valid_for_time() {
+ return None;
+ }
+
+ let value = lit_expr
+ .as_literal()
+ .and_then(|lit| time_value(lit, unit))?;
+ let step = constant_granularity_step(unit, granularity)?;
+ let step = if step == 0 { 1 } else { step };
+ // Alignment: literal must already be on a bucket boundary, else the
+ // original predicate is unsatisfiable.
+ if value % step != 0 {
+ return None;
+ }
+ let upper = value.checked_add(step)?;
+
+ Some(PreimageResult::Range {
+ expr: col_expr.clone(),
+ interval: Box::new(
+ Interval::try_new(time_to_scalar(unit, value), time_to_scalar(unit, upper))
+ .ok()?,
+ ),
+ })
+}
+
+/// Extract the underlying i64 representation from a Time scalar of the given
+/// unit. Time32 values are sign-extended; valid times fit comfortably.
+fn time_value(literal: &ScalarValue, unit: TimeUnit) -> Option<i64> {
+ match (unit, literal) {
+ (Second, ScalarValue::Time32Second(Some(v))) => Some(*v as i64),
+ (Millisecond, ScalarValue::Time32Millisecond(Some(v))) => Some(*v as i64),
+ (Microsecond, ScalarValue::Time64Microsecond(Some(v))) => Some(*v),
+ (Nanosecond, ScalarValue::Time64Nanosecond(Some(v))) => Some(*v),
+ _ => None,
+ }
+}
+
+/// Wrap an i64 value back into the matching Time scalar. The unit uniquely
+/// determines whether the result is `Time32` or `Time64`.
+fn time_to_scalar(unit: TimeUnit, value: i64) -> ScalarValue {
+ match unit {
+ Second => ScalarValue::Time32Second(Some(value as i32)),
+ Millisecond => ScalarValue::Time32Millisecond(Some(value as i32)),
+ Microsecond => ScalarValue::Time64Microsecond(Some(value)),
+ Nanosecond => ScalarValue::Time64Nanosecond(Some(value)),
+ }
+}
+
+/// Wrap a raw `i64` timestamp value back into the matching `ScalarValue` for
+/// the given `(unit, tz)`. Mirrors [`ScalarValue::try_from_date`].
+fn timestamp_to_scalar(
+ unit: TimeUnit,
+ value: i64,
+ tz_opt: Option<&Arc<str>>,
+) -> ScalarValue {
+ match unit {
+ Second => ScalarValue::TimestampSecond(Some(value), tz_opt.cloned()),
+ Millisecond => ScalarValue::TimestampMillisecond(Some(value), tz_opt.cloned()),
+ Microsecond => ScalarValue::TimestampMicrosecond(Some(value), tz_opt.cloned()),
+ Nanosecond => ScalarValue::TimestampNanosecond(Some(value), tz_opt.cloned()),
+ }
+}
+
+/// Returns timestamp preimage bounds `[lower, upper)` for
+/// `date_trunc(granularity, expr) = lower`.
+///
+/// See [`DateTruncFunc::preimage`] for examples of the rewrites these bounds
+/// enable.
+///
+/// Returns `None` when:
+///
+/// * the literal is not aligned to the granularity. For example
+/// `date_trunc('month', ts) = '2024-05-15'` can never be true, since
+/// `date_trunc('month', ..)` always returns the first of a month.
+/// * the upper bound would overflow the timestamp range.
+/// * the bucket starting at `lower` is not the expected width.
+///
+///
+/// As an example of the last condition, the `Australia/Lord_Howe` timezone,
+/// moves its clocks by 30 minutes for daylight saving, so `date_trunc('hour',
+/// ts)` has a 30 minute bucket starting at `2024-10-05T15:30:00Z`
+fn timestamp_preimage_bounds(
+ unit: TimeUnit,
+ lower: i64,
+ tz_opt: Option<&Arc<str>>,
+ granularity: DatePart,
+) -> Option<(i64, i64)> {
+ // Verify the literal is already truncated to this granularity. If it
+ // isn't, the original predicate is unsatisfiable; returning None defers to
+ // the unsimplified plan rather than producing a bogus interval.
+ let tz = parse_tz(tz_opt).ok().flatten();
+ if general_date_trunc(unit, lower, tz, granularity).ok()? != lower {
+ return None;
+ }
+
+ let upper = if let Some(step) = constant_granularity_step(unit, granularity) {
+ // step == 0 happens when the granularity is finer than the unit's
+ // resolution (e.g. truncating a TimestampSecond to microseconds is a
+ // no-op); the preimage is then the singleton `[lower, lower + 1 unit)`.
+ let step = if step == 0 { 1 } else { step };
+ lower.checked_add(step)?
+ } else {
+ next_calendar_boundary_utc(unit, lower, granularity)?
+ };
+
+ // Verify that `[lower, upper)` is the same bucket starting at `lower` to
+ // catch timezones whose buckets are not a fixed number of `unit`s wide,
+ // such as an hour bucket that straddles a 30 minute DST shift
+ // (Australia/Lord_Howe), or a minute bucket that straddles a historical
+ // sub-minute offset change.
+ if general_date_trunc(unit, upper.checked_sub(1)?, tz, granularity).ok()? != lower
+ || general_date_trunc(unit, upper, tz, granularity).ok()? != upper
+ {
+ return None;
+ }
+
+ Some((lower, upper))
+}
+
+/// Width of one bucket for `(unit, granularity)` measured in `unit`s, when
+/// that width is constant. Returns `None` for calendar-variable buckets
+/// (month/quarter/year), which are handled by `next_calendar_boundary_utc`.
+fn constant_granularity_step(unit: TimeUnit, granularity: DatePart) -> Option<i64> {
+ match (unit, granularity) {
+ (Second, DatePart::Microsecond) => Some(0),
+ (Second, DatePart::Millisecond) => Some(0),
+ (Second, DatePart::Second) => Some(1),
+ (Second, DatePart::Minute) => Some(60),
+ (Second, DatePart::Hour) => Some(3_600),
+ (Second, DatePart::Day) => Some(86_400),
+ (Second, DatePart::Week) => Some(604_800),
+
+ (Millisecond, DatePart::Microsecond) => Some(0),
+ (Millisecond, DatePart::Millisecond) => Some(1),
+ (Millisecond, DatePart::Second) => Some(1_000),
+ (Millisecond, DatePart::Minute) => Some(60_000),
+ (Millisecond, DatePart::Hour) => Some(3_600_000),
+ (Millisecond, DatePart::Day) => Some(86_400_000),
+ (Millisecond, DatePart::Week) => Some(604_800_000),
+
+ (Microsecond, DatePart::Microsecond) => Some(1),
+ (Microsecond, DatePart::Millisecond) => Some(1_000),
+ (Microsecond, DatePart::Second) => Some(1_000_000),
+ (Microsecond, DatePart::Minute) => Some(60_000_000),
+ (Microsecond, DatePart::Hour) => Some(3_600_000_000),
+ (Microsecond, DatePart::Day) => Some(86_400_000_000),
+ (Microsecond, DatePart::Week) => Some(604_800_000_000),
+
+ (Nanosecond, DatePart::Microsecond) => Some(1_000),
+ (Nanosecond, DatePart::Millisecond) => Some(1_000_000),
+ (Nanosecond, DatePart::Second) => Some(1_000_000_000),
+ (Nanosecond, DatePart::Minute) => Some(60_000_000_000),
+ (Nanosecond, DatePart::Hour) => Some(3_600_000_000_000),
+ (Nanosecond, DatePart::Day) => Some(86_400_000_000_000),
+ (Nanosecond, DatePart::Week) => Some(604_800_000_000_000),
+ _ => None,
+ }
+}
+
+/// Compute the next month/quarter/year boundary in UTC and convert it back
+/// to the timestamp's `unit`. Uses `chrono::Months` for calendar arithmetic,
+/// matching the chrono-first style used in `date_part::preimage`.
+fn next_calendar_boundary_utc(
+ unit: TimeUnit,
+ lower: i64,
+ granularity: DatePart,
+) -> Option<i64> {
+ let scale = timestamp_unit_scale(unit);
+ let lower_ns = lower.checked_mul(scale)?;
+ let lower_dt = timestamp_ns_to_datetime(lower_ns)?;
+
+ let months = match granularity {
+ DatePart::Month => 1,
+ DatePart::Quarter => 3,
+ DatePart::Year => 12,
+ _ => return None,
+ };
+ let next_dt = lower_dt.checked_add_months(Months::new(months))?;
+
+ let upper_ns = next_dt.and_utc().timestamp_nanos_opt()?;
+ if upper_ns % scale != 0 {
+ return None;
+ }
+ Some(upper_ns / scale)
+}
+
+fn timestamp_unit_scale(unit: TimeUnit) -> i64 {
+ match unit {
+ Second => 1_000_000_000,
+ Millisecond => 1_000_000,
+ Microsecond => 1_000,
+ Nanosecond => 1,
+ }
+}
+
fn _date_trunc_coarse_with_tz(
granularity: DatePart,
value: DateTime<Tz>,
diff --git a/datafusion/sqllogictest/test_files/datetime/timestamps.slt b/datafusion/sqllogictest/test_files/datetime/timestamps.slt
index a0f4af894a..f52b8fb2ef 100644
--- a/datafusion/sqllogictest/test_files/datetime/timestamps.slt
+++ b/datafusion/sqllogictest/test_files/datetime/timestamps.slt
@@ -3334,6 +3334,456 @@ SELECT to_timestamp(t.ts, make_array('%Y-%m-%d %H/%M/%S%#z', '%s', '%q', '%d-%m-
statement ok
drop table ts_utf8_data
+##########
+## date_trunc Preimage Tests
+##
+## date_trunc(granularity, col) = literal rewrites to
+## col >= literal AND col < literal + 1 granularity
+## when the literal is aligned to the bucket boundary.
+##########
+
+statement ok
+set datafusion.explain.logical_plan_only = true;
+
+# Day: equality rewrites to a half-open interval on the column
+query TT
+EXPLAIN SELECT * FROM ts_data_nanos
+WHERE date_trunc('day', ts) = arrow_cast('2024-05-01T00:00:00', 'Timestamp(Nanosecond, None)');
+----
+logical_plan
+01)Filter: ts_data_nanos.ts >= TimestampNanosecond(1714521600000000000, None) AND ts_data_nanos.ts < TimestampNanosecond(1714608000000000000, None)
+02)--TableScan: ts_data_nanos projection=[ts, value]
+
+# Month: variable-width upper bound computed via calendar arithmetic
+query TT
+EXPLAIN SELECT * FROM ts_data_nanos
+WHERE date_trunc('month', ts) = arrow_cast('2024-05-01T00:00:00', 'Timestamp(Nanosecond, None)');
+----
+logical_plan
+01)Filter: ts_data_nanos.ts >= TimestampNanosecond(1714521600000000000, None) AND ts_data_nanos.ts < TimestampNanosecond(1717200000000000000, None)
+02)--TableScan: ts_data_nanos projection=[ts, value]
+
+# Year: 2024 → [2024-01-01, 2025-01-01)
+query TT
+EXPLAIN SELECT * FROM ts_data_nanos
+WHERE date_trunc('year', ts) = arrow_cast('2024-01-01T00:00:00', 'Timestamp(Nanosecond, None)');
+----
+logical_plan
+01)Filter: ts_data_nanos.ts >= TimestampNanosecond(1704067200000000000, None) AND ts_data_nanos.ts < TimestampNanosecond(1735689600000000000, None)
+02)--TableScan: ts_data_nanos projection=[ts, value]
+
+# Quarter: 2024-Q2 → [2024-04-01, 2024-07-01)
+query TT
+EXPLAIN SELECT * FROM ts_data_nanos
+WHERE date_trunc('quarter', ts) = arrow_cast('2024-04-01T00:00:00', 'Timestamp(Nanosecond, None)');
+----
+logical_plan
+01)Filter: ts_data_nanos.ts >= TimestampNanosecond(1711929600000000000, None) AND ts_data_nanos.ts < TimestampNanosecond(1719792000000000000, None)
+02)--TableScan: ts_data_nanos projection=[ts, value]
+
+# Week: date_trunc floors to Monday; aligned Monday literal → [Mon, next Mon)
+query TT
+EXPLAIN SELECT * FROM ts_data_nanos
+WHERE date_trunc('week', ts) = arrow_cast('2024-04-29T00:00:00', 'Timestamp(Nanosecond, None)');
+----
+logical_plan
+01)Filter: ts_data_nanos.ts >= TimestampNanosecond(1714348800000000000, None) AND ts_data_nanos.ts < TimestampNanosecond(1714953600000000000, None)
+02)--TableScan: ts_data_nanos projection=[ts, value]
+
+# Greater-than: only the upper bound survives
+query TT
+EXPLAIN SELECT * FROM ts_data_nanos
+WHERE date_trunc('day', ts) > arrow_cast('2024-05-01T00:00:00', 'Timestamp(Nanosecond, None)');
+----
+logical_plan
+01)Filter: ts_data_nanos.ts >= TimestampNanosecond(1714608000000000000, None)
+02)--TableScan: ts_data_nanos projection=[ts, value]
+
+# Less-than-or-equal: only the upper bound survives
+query TT
+EXPLAIN SELECT * FROM ts_data_nanos
+WHERE date_trunc('day', ts) <= arrow_cast('2024-05-01T00:00:00', 'Timestamp(Nanosecond, None)');
+----
+logical_plan
+01)Filter: ts_data_nanos.ts < TimestampNanosecond(1714608000000000000, None)
+02)--TableScan: ts_data_nanos projection=[ts, value]
+
+# Misaligned literal: no rewrite — preimage returns None and the original predicate stays
+query TT
+EXPLAIN SELECT * FROM ts_data_nanos
+WHERE date_trunc('month', ts) = arrow_cast('2024-05-15T00:00:00', 'Timestamp(Nanosecond, None)');
+----
+logical_plan
+01)Filter: date_trunc(Utf8("month"), ts_data_nanos.ts) = TimestampNanosecond(1715731200000000000, None)
+02)--TableScan: ts_data_nanos projection=[ts, value]
+
+# Literal of a different type: type coercion rejects the comparison before
+# the preimage is consulted, so the preimage only ever sees timestamp literals.
+query error DataFusion error: type_coercion\ncaused by\nError during planning: Cannot infer common argument type for comparison operation Timestamp\(ns\) = Int32
+SELECT * FROM ts_data_nanos
+WHERE date_trunc('day', ts) = arrow_cast(1714521600, 'Int32');
+
+# Date column: preimage peels the signature-inserted Cast(date, Timestamp(ns))
+# and emits bounds in the Date type against the raw column, enabling pruning
+# on Date partition columns. Date values are always at midnight UTC after the
+# cast, so sub-day granularities (e.g. 'hour') collapse to a 1-day interval.
+statement ok
+create table d (d date) as values (date '2024-05-01');
+
+query TT
+EXPLAIN SELECT * FROM d
+WHERE date_trunc('day', d) = arrow_cast('2024-05-01T00:00:00', 'Timestamp(Nanosecond, None)');
+----
+logical_plan
+01)Filter: d.d >= Date32("2024-05-01") AND d.d < Date32("2024-05-02")
+02)--TableScan: d projection=[d]
+
+query TT
+EXPLAIN SELECT * FROM d
+WHERE date_trunc('month', d) = arrow_cast('2024-05-01T00:00:00', 'Timestamp(Nanosecond, None)');
+----
+logical_plan
+01)Filter: d.d >= Date32("2024-05-01") AND d.d < Date32("2024-06-01")
+02)--TableScan: d projection=[d]
+
+# Sub-day granularity with midnight literal: every Date value is at midnight
+# UTC, so date_trunc(any_g, midnight) = midnight; predicate collapses to a
+# single-date bucket.
+query TT
+EXPLAIN SELECT * FROM d
+WHERE date_trunc('hour', d) = arrow_cast('2024-05-01T00:00:00', 'Timestamp(Nanosecond, None)');
+----
+logical_plan
+01)Filter: d.d >= Date32("2024-05-01") AND d.d < Date32("2024-05-02")
+02)--TableScan: d projection=[d]
+
+# Non-midnight literal: a Date column can never produce a 10:00 bucket; both
+# arms bail and the predicate stays unsimplified.
+query TT
+EXPLAIN SELECT * FROM d
+WHERE date_trunc('day', d) = arrow_cast('2024-05-01T10:00:00', 'Timestamp(Nanosecond, None)');
+----
+logical_plan
+01)Filter: date_trunc(Utf8("day"), CAST(d.d AS Timestamp(ns))) = TimestampNanosecond(1714557600000000000, None)
+02)--TableScan: d projection=[d]
+
+# Misaligned date-natural literal (day=15 isn't a month boundary): bails.
+query TT
+EXPLAIN SELECT * FROM d
+WHERE date_trunc('month', d) = arrow_cast('2024-05-15T00:00:00', 'Timestamp(Nanosecond, None)');
+----
+logical_plan
+01)Filter: date_trunc(Utf8("month"), CAST(d.d AS Timestamp(ns))) = TimestampNanosecond(1715731200000000000, None)
+02)--TableScan: d projection=[d]
+
+statement ok
+drop table d;
+
+# Date64 column: same arm as Date32, emits Date64 bounds.
+statement ok
+create table d64 as select arrow_cast(date '2024-05-01', 'Date64') as d;
+
+query TT
+EXPLAIN SELECT * FROM d64
+WHERE date_trunc('month', d) = arrow_cast('2024-05-01T00:00:00', 'Timestamp(Nanosecond, None)');
+----
+logical_plan
+01)Filter: d64.d >= Date64("2024-05-01") AND d64.d < Date64("2024-06-01")
+02)--TableScan: d64 projection=[d]
+
+statement ok
+drop table d64;
+
+# Time column: signature accepts Time as exact match (no cast), so the column
+# arrives raw. All valid-for-Time granularities are fixed-width, so the
+# preimage is a half-open Time interval against the raw column.
+statement ok
+create table t (t time);
+
+query TT
+EXPLAIN SELECT * FROM t WHERE date_trunc('minute', t) = '13:42:00'::time;
+----
+logical_plan
+01)Filter: t.t >= Time64Nanosecond("49320000000000") AND t.t < Time64Nanosecond("49380000000000")
+02)--TableScan: t projection=[t]
+
+# Misaligned literal — bails.
+query TT
+EXPLAIN SELECT * FROM t WHERE date_trunc('minute', t) = '13:42:29'::time;
+----
+logical_plan
+01)Filter: date_trunc(Utf8("minute"), t.t) = Time64Nanosecond("49349000000000")
+02)--TableScan: t projection=[t]
+
+# Granularity not valid for Time (Day) — bails. At runtime invoke_with_args
+# would error; preimage leaves the predicate alone.
+query TT
+EXPLAIN SELECT * FROM t WHERE date_trunc('day', t) = '00:00:00'::time;
+----
+logical_plan
+01)Filter: date_trunc(Utf8("day"), t.t) = Time64Nanosecond("0")
+02)--TableScan: t projection=[t]
+
+statement ok
+drop table t;
+
+# Granularity finer than the unit's resolution: truncating a Timestamp(Second)
+# to microseconds is a no-op, so the preimage is a singleton bucket of one
+# underlying second tick.
+statement ok
+create table ts_sec as select arrow_cast(1700000000, 'Timestamp(Second, None)') as ts;
+
+query TT
+EXPLAIN SELECT * FROM ts_sec
+WHERE date_trunc('microsecond', ts) = arrow_cast(1700000000, 'Timestamp(Second, None)');
+----
+logical_plan
+01)Filter: ts_sec.ts >= TimestampSecond(1700000000, None) AND ts_sec.ts < TimestampSecond(1700000001, None)
+02)--TableScan: ts_sec projection=[ts]
+
+statement ok
+drop table ts_sec;
+
+# Fine granularity with a named-offset timezone: bucket width is constant
+# regardless of tz, so the rewrite fires with the column's tz preserved on
+# the bounds.
+statement ok
+create table ts_tz02 as select arrow_cast('2024-10-27T02:00:00+02:00', 'Timestamp(Nanosecond, Some("+02"))') as ts;
+
+query TT
+EXPLAIN SELECT * FROM ts_tz02
+WHERE date_trunc('minute', ts) = arrow_cast('2024-10-27T02:00:00+02:00', 'Timestamp(Nanosecond, Some("+02"))');
+----
+logical_plan
+01)Filter: ts_tz02.ts >= TimestampNanosecond(1729987200000000000, Some("+02")) AND ts_tz02.ts < TimestampNanosecond(1729987260000000000, Some("+02"))
+02)--TableScan: ts_tz02 projection=[ts]
+
+statement ok
+drop table ts_tz02;
+
+# Hour granularity with a named timezone whose DST shifts are whole hours:
+# every hour bucket is exactly one hour wide, so the rewrite fires (date_trunc is removed).
+statement ok
+create table ts_berlin as select arrow_cast('2024-10-27T00:30:00Z', 'Timestamp(Nanosecond, Some("Europe/Berlin"))') as ts;
+
+query TT
+EXPLAIN SELECT * FROM ts_berlin
+WHERE date_trunc('hour', ts) = arrow_cast('2024-10-27T00:00:00Z', 'Timestamp(Nanosecond, Some("Europe/Berlin"))');
+----
+logical_plan
+01)Filter: ts_berlin.ts >= TimestampNanosecond(1729987200000000000, Some("Europe/Berlin")) AND ts_berlin.ts < TimestampNanosecond(1729990800000000000, Some("Europe/Berlin"))
+02)--TableScan: ts_berlin projection=[ts]
+
+statement ok
+drop table ts_berlin;
+
+# Day granularity with a named timezone: days can be 23 or 25 hours long
+# around DST transitions, so the rewrite is skipped (date_trunc is still present).
+statement ok
+create table ts_berlin as select arrow_cast('2024-10-27T00:30:00Z', 'Timestamp(Nanosecond, Some("Europe/Berlin"))') as ts;
+
+query TT
+EXPLAIN SELECT * FROM ts_berlin
+WHERE date_trunc('day', ts) = arrow_cast('2024-10-26T22:00:00Z', 'Timestamp(Nanosecond, Some("Europe/Berlin"))');
+----
+logical_plan
+01)Filter: date_trunc(Utf8("day"), ts_berlin.ts) = TimestampNanosecond(1729980000000000000, Some("Europe/Berlin"))
+02)--TableScan: ts_berlin projection=[ts]
+
+statement ok
+drop table ts_berlin;
+
+# Day and month granularity with an explicit UTC timezone: UTC has no DST so
+# every bucket is a fixed width and the rewrite fires, preserving the timezone
+# on the bounds.
+statement ok
+create table ts_utc as select arrow_cast('2024-05-01T10:00:00Z', 'Timestamp(Nanosecond, Some("UTC"))') as ts;
+
+query TT
+EXPLAIN SELECT * FROM ts_utc
+WHERE date_trunc('day', ts) = arrow_cast('2024-05-01T00:00:00Z', 'Timestamp(Nanosecond, Some("UTC"))');
+----
+logical_plan
+01)Filter: ts_utc.ts >= TimestampNanosecond(1714521600000000000, Some("UTC")) AND ts_utc.ts < TimestampNanosecond(1714608000000000000, Some("UTC"))
+02)--TableScan: ts_utc projection=[ts]
+
+query TT
+EXPLAIN SELECT * FROM ts_utc
+WHERE date_trunc('month', ts) = arrow_cast('2024-05-01T00:00:00Z', 'Timestamp(Nanosecond, Some("UTC"))');
+----
+logical_plan
+01)Filter: ts_utc.ts >= TimestampNanosecond(1714521600000000000, Some("UTC")) AND ts_utc.ts < TimestampNanosecond(1717200000000000000, Some("UTC"))
+02)--TableScan: ts_utc projection=[ts]
+
+statement ok
+drop table ts_utc;
+
+# Same with "+00:00"
+statement ok
+create table ts_zero as select arrow_cast('2024-05-01T10:00:00Z', 'Timestamp(Nanosecond, Some("+00:00"))') as ts;
+
+query TT
+EXPLAIN SELECT * FROM ts_zero
+WHERE date_trunc('year', ts) = arrow_cast('2024-01-01T00:00:00Z', 'Timestamp(Nanosecond, Some("+00:00"))');
+----
+logical_plan
+01)Filter: ts_zero.ts >= TimestampNanosecond(1704067200000000000, Some("+00:00")) AND ts_zero.ts < TimestampNanosecond(1735689600000000000, Some("+00:00"))
+02)--TableScan: ts_zero projection=[ts]
+
+statement ok
+drop table ts_zero;
+
+# Hour granularity in a timezone with a 30 minute DST shift: the bucket
+# starting at 2024-10-05T15:30:00Z is only 30 minutes wide, so the fixed
+# one-hour step is wrong and the rewrite must be skipped (date_trunc is still present).
+statement ok
+create table ts_lord_howe as select arrow_cast('2024-10-05T15:45:00Z', 'Timestamp(Nanosecond, Some("Australia/Lord_Howe"))') as ts;
+
+query TT
+EXPLAIN SELECT * FROM ts_lord_howe
+WHERE date_trunc('hour', ts) = arrow_cast('2024-10-05T15:30:00Z', 'Timestamp(Nanosecond, Some("Australia/Lord_Howe"))');
+----
+logical_plan
+01)Filter: date_trunc(Utf8("hour"), ts_lord_howe.ts) = TimestampNanosecond(1728142200000000000, Some("Australia/Lord_Howe"))
+02)--TableScan: ts_lord_howe projection=[ts]
+
+statement ok
+drop table ts_lord_howe;
+
+statement ok
+RESET datafusion.explain.logical_plan_only;
+
+# Misaligned literal data check: verify the un-rewritten predicate still
+# produces correct (empty) results at runtime — date_trunc returns the bucket
+# start, so a non-boundary literal can never match.
+query I rowsort
+SELECT value FROM ts_data_nanos
+WHERE date_trunc('month', ts) = arrow_cast('2024-05-15T00:00:00', 'Timestamp(Nanosecond, None)');
+----
+
+# Boundary check: the rewritten half-open interval must include the
+# bucket start and the last instant of the bucket, and exclude the first
+# instant of the next bucket.
+statement ok
+create table ts_bounds as select arrow_cast(column1, 'Timestamp(Nanosecond, None)') as ts
+from (values
+ ('2024-04-30T23:59:59.999999999'),
+ ('2024-05-01T00:00:00'),
+ ('2024-05-31T23:59:59.999999999'),
+ ('2024-06-01T00:00:00')
+);
+
+query P rowsort
+SELECT ts FROM ts_bounds
+WHERE date_trunc('month', ts) = arrow_cast('2024-05-01T00:00:00', 'Timestamp(Nanosecond, None)');
+----
+2024-05-01T00:00:00
+2024-05-31T23:59:59.999999999
+
+query P rowsort
+SELECT ts FROM ts_bounds
+WHERE date_trunc('month', ts) != arrow_cast('2024-05-01T00:00:00', 'Timestamp(Nanosecond, None)');
+----
+2024-04-30T23:59:59.999999999
+2024-06-01T00:00:00
+
+query P rowsort
+SELECT ts FROM ts_bounds
+WHERE date_trunc('month', ts) > arrow_cast('2024-05-01T00:00:00', 'Timestamp(Nanosecond, None)');
+----
+2024-06-01T00:00:00
+
+query P rowsort
+SELECT ts FROM ts_bounds
+WHERE date_trunc('month', ts) <= arrow_cast('2024-05-01T00:00:00', 'Timestamp(Nanosecond, None)');
+----
+2024-04-30T23:59:59.999999999
+2024-05-01T00:00:00
+2024-05-31T23:59:59.999999999
+
+query P rowsort
+SELECT ts FROM ts_bounds
+WHERE date_trunc('month', ts) IN (
+ arrow_cast('2024-04-01T00:00:00', 'Timestamp(Nanosecond, None)'),
+ arrow_cast('2024-06-01T00:00:00', 'Timestamp(Nanosecond, None)')
+);
+----
+2024-04-30T23:59:59.999999999
+2024-06-01T00:00:00
+
+statement ok
+drop table ts_bounds;
+
+# Test timezone whose DST shift is not a whole hour.
+# Australia/Lord_Howe springs forward by 30 minutes at 2024-10-06 02:00
+# local (+10:30 -> +11:00)
+#
+# So for example date_trunc('2024-10-05T15:30:00Z') truncates in local time,
+# so the bucket that starts at 15:30Z (02:30+11:00,
+# right after the gap) is only 30 minutes wide: 16:00Z is local 03:00 and
+# belongs to the next bucket. A fixed one-hour step would be wrong here.
+statement ok
+create table ts_lord_howe as select arrow_cast(column1, 'Timestamp(Nanosecond, Some("Australia/Lord_Howe"))') as ts
+from (values
+ ('2024-10-05T15:00:00Z'),
+ ('2024-10-05T15:30:00Z'),
+ ('2024-10-05T15:45:00Z'),
+ ('2024-10-05T16:00:00Z'),
+ ('2024-10-05T16:15:00Z')
+);
+
+# Demonstrate what date_trunc produces
+query PP
+SELECT ts, date_trunc('hour', ts) FROM ts_lord_howe ORDER BY ts;
+----
+2024-10-06T01:30:00+10:30 2024-10-06T01:00:00+10:30
+2024-10-06T02:30:00+11:00 2024-10-06T02:30:00+11:00
+2024-10-06T02:45:00+11:00 2024-10-06T02:30:00+11:00
+2024-10-06T03:00:00+11:00 2024-10-06T03:00:00+11:00
+2024-10-06T03:15:00+11:00 2024-10-06T03:00:00+11:00
+
+# Equality on the 30 minute bucket: only the two rows inside it match.
+query P rowsort
+SELECT ts FROM ts_lord_howe
+WHERE date_trunc('hour', ts) = arrow_cast('2024-10-05T15:30:00Z', 'Timestamp(Nanosecond, Some("Australia/Lord_Howe"))');
+----
+2024-10-06T02:30:00+11:00
+2024-10-06T02:45:00+11:00
+
+# Greater-than: the next bucket starts at 16:00Z, not 16:30Z.
+query P rowsort
+SELECT ts FROM ts_lord_howe
+WHERE date_trunc('hour', ts) > arrow_cast('2024-10-05T15:30:00Z', 'Timestamp(Nanosecond, Some("Australia/Lord_Howe"))');
+----
+2024-10-06T03:00:00+11:00
+2024-10-06T03:15:00+11:00
+
+statement ok
+drop table ts_lord_howe;
+
+# Time column: the last bucket of the day. The exclusive upper bound is
+# 24:00:00, which is outside the range of valid Time values.
+statement ok
+create table t32 as select arrow_cast(column1, 'Time32(Second)') as t
+from (values ('22:59:59'), ('23:00:00'), ('23:59:59'));
+
+query D rowsort
+SELECT t FROM t32
+WHERE date_trunc('hour', t) = arrow_cast('23:00:00', 'Time32(Second)');
+----
+23:00:00
+23:59:59
+
+query D rowsort
+SELECT t FROM t32
+WHERE date_trunc('hour', t) > arrow_cast('22:00:00', 'Time32(Second)');
+----
+23:00:00
+23:59:59
+
+statement ok
+drop table t32;
+
##########
## Test binary temporal coercion for Date and Timestamp
##########
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@datafusion.apache.org
For additional commands, e-mail: commits-help@datafusion.apache.org