Subtracting Timestamp from Timestamp should produce a Duration (not Timestamp) #3964
Description
Describe the bug
Subtracting two Timestamp columns results in another Timestamp which is not correct
To Reproduce
fn subtract_timestamps() {
let arr1 = TimestampNanosecondArray::from(vec![
1_000,
1_000_000,
1_000_000_000,
1_000_000_000_000,
1_000_000_000_000_000,
1_000_000_000_000_000_000,
]);
let arr2 = TimestampNanosecondArray::from(vec![
2_000,
2_000_000,
2_000_000_000,
2_000_000_000_000,
2_000_000_000_000_000,
2_000_000_000_000_000_000,
]);
println!(
"input:\n{}\n{}",
pretty_format_columns("arr1", &[Arc::new(arr1.clone()) as _]).unwrap(),
pretty_format_columns("arr2", &[Arc::new(arr2.clone()) as _]).unwrap(),
);
let interval = subtract_dyn(&arr2, &arr1).unwrap();
println!(
"output{}:\n{}",
interval.data_type(),
pretty_format_columns("interval", &[Arc::new(interval.clone()) as _]).unwrap(),
);
}Which produces
input:
+----------------------------+
| arr1 |
+----------------------------+
| 1970-01-01T00:00:00.000001 |
| 1970-01-01T00:00:00.001 |
| 1970-01-01T00:00:01 |
| 1970-01-01T00:16:40 |
| 1970-01-12T13:46:40 |
| 2001-09-09T01:46:40 |
+----------------------------+
+----------------------------+
| arr2 |
+----------------------------+
| 1970-01-01T00:00:00.000002 |
| 1970-01-01T00:00:00.002 |
| 1970-01-01T00:00:02 |
| 1970-01-01T00:33:20 |
| 1970-01-24T03:33:20 |
| 2033-05-18T03:33:20 |
+----------------------------+
output Timestamp(Nanosecond, None):
+----------------------------+
| interval |
+----------------------------+
| 1970-01-01T00:00:00.000001 |
| 1970-01-01T00:00:00.001 |
| 1970-01-01T00:00:01 |
| 1970-01-01T00:16:40 |
| 1970-01-12T13:46:40 |
| 2001-09-09T01:46:40 |
+----------------------------+
Expected behavior
I expect the output to be an interval of type Interval(MonthDayNano) (not Timestamp)
Updated (after discussion with @tustvold ) I expect the output to be a duration of type Duration(unit) where unit is the same as the source Timestamp(unit) (not Timestamp)
Additional context
Here is what postgres does:
postgres=# create table test as select now() - now();
SELECT 1
postgres=# \d test
Table "public.test"
Column | Type | Collation | Nullable | Default
----------+----------+-----------+----------+---------
?column? | interval | | |
postgres=#