Non-constant DATE_TRUNC expression regression for values before epoch

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

Also exists on main as of commit e9431fc

@waynexia

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

@sadboy

Thanks @sadboy appreciate if you also add a regression test

Revert "speedup `date_trunc` (~7x faster) in some cases (apache#16859)"

@mhilton

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

fix: correct date_trunc for times before the epoch #18356

fix: correct date_trunc for times before the epoch (#18356)

fix: correct date_trunc for times before the epoch (apache#18356)

Revert "speedup `date_trunc` (~7x faster) in some cases (apache#16859)"

Navigation Menu

Repository navigation

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

Describe the bug

To Reproduce

Expected behavior

Additional context

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

	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)))
	}

Non-constant DATE_TRUNC expression regression for values before epoch #18334

Description

Describe the bug

To Reproduce

Expected behavior

Additional context

Activity

sadboy commented on Oct 28, 2025

sadboy commented on Oct 28, 2025

comphead commented on Oct 28, 2025

alamb commented on Oct 29, 2025

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions

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