feat(query): support timestamp diff return interval type (#17381)

timestamp_diff(TIMESTAMP, TIMESTAMP) -> INTERVAL
databendlabs · Jan 30, 2025 · 1c57f68 · 1c57f68
1 parent c5e7355
commit 1c57f68
Show file tree

Hide file tree

Showing 3 changed files with 40 additions and 0 deletions.
diff --git a/src/query/functions/src/scalars/timestamp/src/datetime.rs b/src/query/functions/src/scalars/timestamp/src/datetime.rs
@@ -15,6 +15,7 @@
 use std::borrow::Cow;
 use std::io::Write;
 
+use databend_common_column::types::months_days_micros;
 use databend_common_exception::ErrorCode;
 use databend_common_expression::error_to_null;
 use databend_common_expression::types::date::clamp_date;
@@ -40,6 +41,7 @@ use databend_common_expression::types::Bitmap;
 use databend_common_expression::types::DateType;
 use databend_common_expression::types::Float64Type;
 use databend_common_expression::types::Int32Type;
+use databend_common_expression::types::IntervalType;
 use databend_common_expression::types::NullableType;
 use databend_common_expression::types::NumberType;
 use databend_common_expression::types::StringType;
@@ -1183,6 +1185,12 @@ fn register_diff_functions(registry: &mut FunctionRegistry) {
         |a, b, _| a - b,
     );
 
+    registry.register_2_arg::<TimestampType, TimestampType, IntervalType, _, _>(
+        "timestamp_diff",
+        |_, _, _| FunctionDomain::MayThrow,
+        |a, b, _| months_days_micros::new(0, 0, a - b),
+    );
+
     registry.register_2_arg::<TimestampType, TimestampType, Int64Type, _, _>(
         "minus",
         |_, lhs, rhs| {

diff --git a/src/query/functions/tests/it/scalars/testdata/function_list.txt b/src/query/functions/tests/it/scalars/testdata/function_list.txt
@@ -3492,6 +3492,8 @@ Functions overloads:
 1 tan(Float64 NULL) :: Float64 NULL
 0 time_slot(Timestamp) :: Timestamp
 1 time_slot(Timestamp NULL) :: Timestamp NULL
+0 timestamp_diff(Timestamp, Timestamp) :: Interval
+1 timestamp_diff(Timestamp NULL, Timestamp NULL) :: Interval NULL
 0 to_base64(Binary) :: String
 1 to_base64(Binary NULL) :: String NULL
 0 to_binary(Variant) :: Binary

diff --git a/tests/sqllogictests/suites/query/functions/02_0079_function_interval.test b/tests/sqllogictests/suites/query/functions/02_0079_function_interval.test
@@ -154,3 +154,33 @@ query T
 select to_interval('120000000000 months');
 ----
 00:00:00
+
+onlyif http
+query T
+select '2022-01-01'::timestamp - '2021-01-01'::timestamp
+----
+31536000000000
+
+onlyif http
+query T
+select timestamp_diff('2022-01-01'::timestamp,'2021-01-01'::timestamp);
+----
+8760:00:00
+
+onlyif http
+query T
+select timestamp_diff('2021-01-01'::timestamp, '2022-01-01'::timestamp);
+----
+-8760:00:00
+
+onlyif http
+query T
+select timestamp_diff('2022-01-01'::timestamp,'2021-12-01'::timestamp);
+----
+744:00:00
+
+onlyif http
+query T
+select '2022-01-01'::timestamp-'2021-12-01'::timestamp;
+----
+2678400000000