This is an automated email from the ASF dual-hosted git repository.
dataroaring pushed a commit to branch branch-3.0
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/branch-3.0 by this push:
new f7f36745431 [branch-3.0](function) fix wrong floor of function
date_diff when unit less than day (#49429) (#50608)
f7f36745431 is described below
commit f7f367454319e6a5f826ba0df7077cf84562e107
Author: zclllyybb <zhaochan...@selectdb.com>
AuthorDate: Wed May 7 14:28:40 2025 +0800
[branch-3.0](function) fix wrong floor of function date_diff when unit less
than day (#49429) (#50608)
pick https://github.com/apache/doris/pull/49429
---
be/src/util/datetype_cast.hpp | 8 +-
.../aggregate_function_sequence_match.h | 10 +-
.../vec/functions/array/function_array_range.cpp | 8 +-
be/src/vec/functions/date_time_transforms.h | 28 +--
.../function_date_or_datetime_computation.h | 8 +-
.../vec/functions/function_datetime_floor_ceil.cpp | 6 +-
be/src/vec/runtime/vdatetime_value.cpp | 4 +-
be/src/vec/runtime/vdatetime_value.h | 104 ++++++-----
be/test/vec/runtime/vdatetime_value_test.cpp | 203 +++++++++++++++++++++
.../datetime_functions/test_date_function_v2.out | Bin 0 -> 430 bytes
.../array_functions/test_array_functions.out | Bin 28028 -> 28005 bytes
.../test_date_function_v2.groovy | 53 ++++++
12 files changed, 351 insertions(+), 81 deletions(-)
diff --git a/be/src/util/datetype_cast.hpp b/be/src/util/datetype_cast.hpp
index 495631ea7e3..67df42e2568 100644
--- a/be/src/util/datetype_cast.hpp
+++ b/be/src/util/datetype_cast.hpp
@@ -29,8 +29,12 @@
/*
* We use these function family to clarify our types of datelike type. for
example:
* DataTypeDate -------------------> ColumnDate ----------------------->
Int64
- * | TypeToColumn ValueTypeOfColumn
- * | TypeToValueType
+ * | | TypeToColumn ValueTypeOfColumn
|
+ * |
↘--------------------------------------------------------------↗
+ * | | ::FieldType
|
+ * |
↖--------------------------------------------------------------↙
+ * | DateTraits<T>::DateType
+ * ↓ TypeToValueType
* VecDateTimeValue
*/
namespace doris::date_cast {
diff --git a/be/src/vec/aggregate_functions/aggregate_function_sequence_match.h
b/be/src/vec/aggregate_functions/aggregate_function_sequence_match.h
index 58600304331..b1e59c95a8b 100644
--- a/be/src/vec/aggregate_functions/aggregate_function_sequence_match.h
+++ b/be/src/vec/aggregate_functions/aggregate_function_sequence_match.h
@@ -413,7 +413,7 @@ public:
base_it = events_it;
++action_it;
} else if (action_it->type == PatternActionType::TimeLessOrEqual) {
- if (events_it->first.second_diff(base_it->first) <=
action_it->extra) {
+ if (events_it->first.datetime_diff_in_seconds(base_it->first)
<= action_it->extra) {
/// condition satisfied, move onto next action
back_stack.emplace(action_it, events_it, base_it);
base_it = events_it;
@@ -421,28 +421,28 @@ public:
} else if (!do_backtrack())
break;
} else if (action_it->type == PatternActionType::TimeLess) {
- if (events_it->first.second_diff(base_it->first) <
action_it->extra) {
+ if (events_it->first.datetime_diff_in_seconds(base_it->first)
< action_it->extra) {
back_stack.emplace(action_it, events_it, base_it);
base_it = events_it;
++action_it;
} else if (!do_backtrack())
break;
} else if (action_it->type ==
PatternActionType::TimeGreaterOrEqual) {
- if (events_it->first.second_diff(base_it->first) >=
action_it->extra) {
+ if (events_it->first.datetime_diff_in_seconds(base_it->first)
>= action_it->extra) {
back_stack.emplace(action_it, events_it, base_it);
base_it = events_it;
++action_it;
} else if (++events_it == events_end && !do_backtrack())
break;
} else if (action_it->type == PatternActionType::TimeGreater) {
- if (events_it->first.second_diff(base_it->first) >
action_it->extra) {
+ if (events_it->first.datetime_diff_in_seconds(base_it->first)
> action_it->extra) {
back_stack.emplace(action_it, events_it, base_it);
base_it = events_it;
++action_it;
} else if (++events_it == events_end && !do_backtrack())
break;
} else if (action_it->type == PatternActionType::TimeEqual) {
- if (events_it->first.second_diff(base_it->first) ==
action_it->extra) {
+ if (events_it->first.datetime_diff_in_seconds(base_it->first)
== action_it->extra) {
back_stack.emplace(action_it, events_it, base_it);
base_it = events_it;
++action_it;
diff --git a/be/src/vec/functions/array/function_array_range.cpp
b/be/src/vec/functions/array/function_array_range.cpp
index 888d7adc5f9..ecfc4b3f99f 100644
--- a/be/src/vec/functions/array/function_array_range.cpp
+++ b/be/src/vec/functions/array/function_array_range.cpp
@@ -136,9 +136,8 @@ struct RangeImplUtil {
auto dest_array_column_ptr =
ColumnArray::create(return_nested_type->create_column(),
ColumnArray::ColumnOffsets::create());
IColumn* dest_nested_column = &dest_array_column_ptr->get_data();
- ColumnNullable* dest_nested_nullable_col =
- reinterpret_cast<ColumnNullable*>(dest_nested_column);
- dest_nested_column = dest_nested_nullable_col->get_nested_column_ptr();
+ auto* dest_nested_nullable_col =
assert_cast<ColumnNullable*>(dest_nested_column);
+ dest_nested_column =
dest_nested_nullable_col->get_nested_column_ptr().get();
auto& dest_nested_null_map =
dest_nested_nullable_col->get_null_map_column().get_data();
auto args_null_map = ColumnUInt8::create(input_rows_count, 0);
@@ -159,7 +158,8 @@ struct RangeImplUtil {
assert_cast<const
ColumnVector<SourceDataType>*>(argument_columns[0].get());
auto end_column =
assert_cast<const
ColumnVector<SourceDataType>*>(argument_columns[1].get());
- auto step_column = assert_cast<const
ColumnVector<Int32>*>(argument_columns[2].get());
+ const auto* step_column =
+ assert_cast<const
ColumnVector<Int32>*>(argument_columns[2].get());
DCHECK(dest_nested_column != nullptr);
auto& dest_offsets = dest_array_column_ptr->get_offsets();
diff --git a/be/src/vec/functions/date_time_transforms.h
b/be/src/vec/functions/date_time_transforms.h
index f20e2255e26..beffaf54b4a 100644
--- a/be/src/vec/functions/date_time_transforms.h
+++ b/be/src/vec/functions/date_time_transforms.h
@@ -39,20 +39,20 @@
namespace doris::vectorized {
-#define TIME_FUNCTION_IMPL(CLASS, UNIT, FUNCTION)
\
- template <typename ArgType>
\
- struct CLASS {
\
- using OpArgType = ArgType;
\
- static constexpr auto name = #UNIT;
\
-
\
- static inline auto execute(const ArgType& t) {
\
- const auto& date_time_value = (typename
DateTraits<ArgType>::T&)(t); \
- return date_time_value.FUNCTION;
\
- }
\
-
\
- static DataTypes get_variadic_argument_types() {
\
- return {std::make_shared<typename
DateTraits<ArgType>::DateType>()}; \
- }
\
+#define TIME_FUNCTION_IMPL(CLASS, UNIT, FUNCTION)
\
+ template <typename NativeType>
\
+ struct CLASS {
\
+ using OpArgType = NativeType;
\
+ static constexpr auto name = #UNIT;
\
+
\
+ static inline auto execute(const NativeType& t) {
\
+ const auto& date_time_value = (typename
DateTraits<NativeType>::T&)(t); \
+ return date_time_value.FUNCTION;
\
+ }
\
+
\
+ static DataTypes get_variadic_argument_types() {
\
+ return {std::make_shared<typename
DateTraits<NativeType>::DateType>()}; \
+ }
\
}
#define TO_TIME_FUNCTION(CLASS, UNIT) TIME_FUNCTION_IMPL(CLASS, UNIT, UNIT())
diff --git a/be/src/vec/functions/function_date_or_datetime_computation.h
b/be/src/vec/functions/function_date_or_datetime_computation.h
index ab887812249..83b38f2e5f9 100644
--- a/be/src/vec/functions/function_date_or_datetime_computation.h
+++ b/be/src/vec/functions/function_date_or_datetime_computation.h
@@ -274,8 +274,8 @@ struct SubtractYearsImpl :
SubtractIntervalImpl<AddYearsImpl<DateType>, DateType
}
\
};
DECLARE_DATE_FUNCTIONS(DateDiffImpl, datediff, DataTypeInt32, (ts0.daynr() -
ts1.daynr()));
-// DECLARE_DATE_FUNCTIONS(TimeDiffImpl, timediff, DataTypeTime,
ts0.second_diff(ts1));
-// Expands to
+// DECLARE_DATE_FUNCTIONS(TimeDiffImpl, timediff, DataTypeTime,
ts0.datetime_diff_in_seconds(ts1));
+// Expands to below here because it use Time type which need some special deal.
template <typename DateType1, typename DateType2>
struct TimeDiffImpl {
using DateValueType1 = date_cast::TypeToValueTypeV<DateType1>;
@@ -297,7 +297,7 @@ struct TimeDiffImpl {
if constexpr (UsingTimev2) {
// refer to https://dev.mysql.com/doc/refman/5.7/en/time.html
// the time type value between '-838:59:59' and '838:59:59', so
the return value should limited
- int64_t diff_m = ts0.microsecond_diff(ts1);
+ int64_t diff_m = ts0.datetime_diff_in_microseconds(ts1);
if (diff_m > limit_value) {
return (double)limit_value;
} else if (diff_m < -1 * limit_value) {
@@ -306,7 +306,7 @@ struct TimeDiffImpl {
return (double)diff_m;
}
} else {
- return (double)((1000 * 1000) * ts0.second_diff(ts1));
+ return (double)((1000 * 1000) * ts0.datetime_diff_in_seconds(ts1));
}
}
static DataTypes get_variadic_argument_types() {
diff --git a/be/src/vec/functions/function_datetime_floor_ceil.cpp
b/be/src/vec/functions/function_datetime_floor_ceil.cpp
index bf74deaed1d..07e35f37b85 100644
--- a/be/src/vec/functions/function_datetime_floor_ceil.cpp
+++ b/be/src/vec/functions/function_datetime_floor_ceil.cpp
@@ -713,7 +713,7 @@ struct TimeRound {
trivial_part_ts1 = ts1.second();
}
if constexpr (Impl::Unit == SECOND) {
- diff = ts2.second_diff(ts1);
+ diff = ts2.datetime_diff_in_seconds(ts1);
trivial_part_ts1 = 0;
trivial_part_ts2 = 0;
}
@@ -752,7 +752,7 @@ struct TimeRound {
trivial_part_ts1 = ts1.second();
}
if constexpr (Impl::Unit == SECOND) {
- diff = ts2.second_diff(ts1);
+ diff = ts2.datetime_diff_in_seconds(ts1);
trivial_part_ts1 = 0;
trivial_part_ts2 = 0;
}
@@ -793,7 +793,7 @@ struct TimeRound {
ts1.to_date_int_val() &
MASK_YEAR_MONTH_DAY_HOUR_MINUTE_FOR_DATETIMEV2;
}
if constexpr (Impl::Unit == SECOND) {
- diff = ts2.second_diff(ts1);
+ diff = ts2.datetime_diff_in_seconds(ts1);
trivial_part_ts2 = ts2.microsecond();
trivial_part_ts1 = ts1.microsecond();
}
diff --git a/be/src/vec/runtime/vdatetime_value.cpp
b/be/src/vec/runtime/vdatetime_value.cpp
index 5cd2e5f4777..d161c64fd1f 100644
--- a/be/src/vec/runtime/vdatetime_value.cpp
+++ b/be/src/vec/runtime/vdatetime_value.cpp
@@ -3942,9 +3942,9 @@ template void
VecDateTimeValue::create_from_date_v2<DateTimeV2ValueType>(
template void VecDateTimeValue::create_from_date_v2<DateTimeV2ValueType>(
DateV2Value<DateTimeV2ValueType>&& value, TimeType type);
-template int64_t VecDateTimeValue::second_diff<DateV2Value<DateV2ValueType>>(
+template int64_t
VecDateTimeValue::datetime_diff_in_seconds<DateV2Value<DateV2ValueType>>(
const DateV2Value<DateV2ValueType>& rhs) const;
-template int64_t
VecDateTimeValue::second_diff<DateV2Value<DateTimeV2ValueType>>(
+template int64_t
VecDateTimeValue::datetime_diff_in_seconds<DateV2Value<DateTimeV2ValueType>>(
const DateV2Value<DateTimeV2ValueType>& rhs) const;
#define DELARE_DATE_ADD_INTERVAL(DateValueType1, DateValueType2)
\
diff --git a/be/src/vec/runtime/vdatetime_value.h
b/be/src/vec/runtime/vdatetime_value.h
index 0c991c14e4a..b337fea46a2 100644
--- a/be/src/vec/runtime/vdatetime_value.h
+++ b/be/src/vec/runtime/vdatetime_value.h
@@ -167,6 +167,7 @@ const int TIME_MAX_VALUE_SECONDS = 3600 * TIME_MAX_HOUR +
60 * TIME_MAX_MINUTE +
constexpr int HOUR_PER_DAY = 24;
constexpr int64_t SECOND_PER_HOUR = 3600;
constexpr int64_t SECOND_PER_MINUTE = 60;
+constexpr int64_t MS_PER_SECOND = 1000 * 1000;
inline constexpr int S_DAYS_IN_MONTH[13] = {0, 31, 28, 31, 30, 31, 30, 31, 31,
30, 31, 30, 31};
@@ -426,7 +427,7 @@ public:
void unchecked_set_time(uint32_t year, uint32_t month, uint32_t day,
uint32_t hour,
uint32_t minute, uint32_t second);
- int64_t daynr() const { return calc_daynr(_year, _month, _day); }
+ uint32_t daynr() const { return calc_daynr(_year, _month, _day); }
int year() const { return _year; }
int month() const { return _month; }
@@ -650,7 +651,7 @@ public:
}
template <typename T>
- int64_t second_diff(const T& rhs) const {
+ int64_t datetime_diff_in_seconds(const T& rhs) const {
return (daynr() - rhs.daynr()) * SECOND_PER_HOUR * HOUR_PER_DAY +
time_part_diff(rhs);
}
@@ -892,6 +893,7 @@ public:
void unchecked_set_time(uint8_t hour, uint8_t minute, uint8_t second,
uint32_t microsecond);
+ // we frequently use this to do arithmetic operation, so use signed
int64_t to avoid overflow.
int64_t daynr() const {
return calc_daynr(date_v2_value_.year_, date_v2_value_.month_,
date_v2_value_.day_);
}
@@ -1136,34 +1138,57 @@ public:
//only calculate the diff of dd:mm:ss
template <typename RHS>
- int64_t time_part_diff(const RHS& rhs) const {
+ int64_t time_part_diff_without_ms(const RHS& rhs) const {
return time_part_to_seconds() - rhs.time_part_to_seconds();
}
//only calculate the diff of dd:mm:ss.SSSSSS
template <typename RHS>
- int64_t time_part_diff_microsecond(const RHS& rhs) const {
+ int64_t time_part_diff_in_ms(const RHS& rhs) const {
return time_part_to_microsecond() - rhs.time_part_to_microsecond();
}
template <typename RHS>
- int64_t second_diff(const RHS& rhs) const {
- return (daynr() - rhs.daynr()) * SECOND_PER_HOUR * HOUR_PER_DAY +
time_part_diff(rhs);
+ int64_t datetime_diff_in_seconds(const RHS& rhs) const {
+ return (daynr() - rhs.daynr()) * SECOND_PER_HOUR * HOUR_PER_DAY +
+ time_part_diff_without_ms(rhs);
+ }
+
+ template <typename RHS>
+ int32_t date_diff_in_days(const RHS& rhs) const {
+ return daynr() - rhs.daynr(); // arithmetic calculation will auto
promote to signed int32
+ }
+
+ int32_t date_diff_in_days_round_to_zero_by_time(const auto& rhs) const {
+ int32_t day = this->date_diff_in_days(rhs);
+ int64_t ms_diff = this->time_part_diff_in_ms(rhs);
+ if (day > 0 && ms_diff < 0) {
+ day--;
+ } else if (day < 0 && ms_diff > 0) {
+ day++;
+ }
+ return day;
}
// used by INT microseconds_diff(DATETIME enddate, DATETIME startdate)
- // return it's int type, so shouldn't have any limit.
+ // return value is int type, so shouldn't have any limit.
// when used by TIME TIMEDIFF(DATETIME expr1, DATETIME expr2), it's return
time type, should have limited.
template <typename RHS>
- int64_t microsecond_diff(const RHS& rhs) const {
- int64_t diff_m = (daynr() - rhs.daynr()) * SECOND_PER_HOUR *
HOUR_PER_DAY * 1000 * 1000 +
- time_part_diff_microsecond(rhs);
+ int64_t datetime_diff_in_microseconds(const RHS& rhs) const {
+ int64_t diff_m = (daynr() - rhs.daynr()) * HOUR_PER_DAY *
SECOND_PER_HOUR * MS_PER_SECOND +
+ time_part_diff_in_ms(rhs);
return diff_m;
}
- bool can_cast_to_date_without_loss_accuracy() {
- return this->hour() == 0 && this->minute() == 0 && this->second() == 0
&&
- this->microsecond() == 0;
+ int64_t datetime_diff_in_seconds_round_to_zero_by_ms(const auto& rhs)
const {
+ int64_t second = this->datetime_diff_in_seconds(rhs);
+ int32_t ms_diff = this->microsecond() - rhs.microsecond();
+ if (second > 0 && ms_diff < 0) {
+ second--;
+ } else if (second < 0 && ms_diff > 0) {
+ second++;
+ }
+ return second;
}
underlying_value to_date_int_val() const { return int_val_; }
@@ -1388,17 +1413,17 @@ int64_t datetime_diff(const VecDateTimeValue&
ts_value1, const VecDateTimeValue&
return day;
}
case HOUR: {
- int64_t second = ts_value2.second_diff(ts_value1);
+ int64_t second = ts_value2.datetime_diff_in_seconds(ts_value1);
int64_t hour = second / 60 / 60;
return hour;
}
case MINUTE: {
- int64_t second = ts_value2.second_diff(ts_value1);
+ int64_t second = ts_value2.datetime_diff_in_seconds(ts_value1);
int64_t minute = second / 60;
return minute;
}
case SECOND: {
- int64_t second = ts_value2.second_diff(ts_value1);
+ int64_t second = ts_value2.datetime_diff_in_seconds(ts_value1);
return second;
}
}
@@ -1406,10 +1431,15 @@ int64_t datetime_diff(const VecDateTimeValue&
ts_value1, const VecDateTimeValue&
return 0;
}
-template <TimeUnit unit, typename T0, typename T1>
+// ROUND the result TO ZERO( not FLOOR). for datetime_diff<year>, everything
less than year is the remainder.
+// "ROUND TO ZERO" means `years_diff('2020-05-05', '2015-06-06')` gets 4 and
+// `years_diff('2015-06-06', '2020-05-05')` gets -4.
+template <TimeUnit UNIT, typename T0, typename T1>
int64_t datetime_diff(const DateV2Value<T0>& ts_value1, const DateV2Value<T1>&
ts_value2) {
constexpr uint64_t uint64_minus_one = -1;
- switch (unit) {
+ switch (UNIT) {
+ // for YEAR and MONTH: calculate the diff of year or month, and use
bitmask to get the remainder of all other
+ // parts. then round to zero by the remainder.
case YEAR: {
int year = (ts_value2.year() - ts_value1.year());
if constexpr (std::is_same_v<T0, T1>) {
@@ -1494,47 +1524,27 @@ int64_t datetime_diff(const DateV2Value<T0>& ts_value1,
const DateV2Value<T1>& t
return month;
}
case WEEK: {
- int day = ts_value2.daynr() - ts_value1.daynr();
- int64_t ms_diff = ts_value2.time_part_diff_microsecond(ts_value1);
- if (day > 0 && ms_diff < 0) {
- day--;
- } else if (day < 0 && ms_diff > 0) {
- day++;
- }
- return day / 7;
+ return ts_value2.date_diff_in_days_round_to_zero_by_time(ts_value1) /
7;
}
case DAY: {
- int day = ts_value2.daynr() - ts_value1.daynr();
- int64_t ms_diff = ts_value2.time_part_diff_microsecond(ts_value1);
- if (day > 0 && ms_diff < 0) {
- day--;
- } else if (day < 0 && ms_diff > 0) {
- day++;
- }
- return day;
+ return ts_value2.date_diff_in_days_round_to_zero_by_time(ts_value1);
}
case HOUR: {
- int64_t second = ts_value2.second_diff(ts_value1);
- int64_t hour = second / 60 / 60;
- return hour;
+ return
ts_value2.datetime_diff_in_seconds_round_to_zero_by_ms(ts_value1) / 60 / 60;
}
case MINUTE: {
- int64_t second = ts_value2.second_diff(ts_value1);
- int64_t minute = second / 60;
- return minute;
+ return
ts_value2.datetime_diff_in_seconds_round_to_zero_by_ms(ts_value1) / 60;
}
case SECOND: {
- int64_t second = ts_value2.second_diff(ts_value1);
- return second;
+ return
ts_value2.datetime_diff_in_seconds_round_to_zero_by_ms(ts_value1);
}
case MILLISECOND: {
- int64_t microsecond = ts_value2.microsecond_diff(ts_value1);
- int64_t millisecond = microsecond / 1000;
- return millisecond;
+ // C++ naturally rounds to zero
+ return ts_value2.datetime_diff_in_microseconds(ts_value1) / 1000;
}
case MICROSECOND: {
- int64_t microsecond = ts_value2.microsecond_diff(ts_value1);
- return microsecond;
+ // no precision loss
+ return ts_value2.datetime_diff_in_microseconds(ts_value1);
}
}
// Rethink the default return value
diff --git a/be/test/vec/runtime/vdatetime_value_test.cpp
b/be/test/vec/runtime/vdatetime_value_test.cpp
index 24d25f80ba7..e91ee626acc 100644
--- a/be/test/vec/runtime/vdatetime_value_test.cpp
+++ b/be/test/vec/runtime/vdatetime_value_test.cpp
@@ -677,4 +677,207 @@ TEST(VDateTimeValueTest,
date_v2_from_date_format_str_with_all_space) {
}
}
+TEST(VDateTimeValueTest, datetime_diff_test) {
+ // Test case 1: DATE to DATE - Different years, months, days
+ {
+ DateV2Value<DateV2ValueType> date1;
+ std::string date_str1 = "2020-01-15";
+ std::string format = "%Y-%m-%d";
+ EXPECT_TRUE(date1.from_date_format_str(format.data(), format.size(),
date_str1.data(),
+ date_str1.size()));
+
+ DateV2Value<DateV2ValueType> date2;
+ std::string date_str2 = "2023-08-20";
+ EXPECT_TRUE(date2.from_date_format_str(format.data(), format.size(),
date_str2.data(),
+ date_str2.size()));
+
+ // Test all time units for DATE to DATE
+ EXPECT_EQ(datetime_diff<TimeUnit::YEAR>(date1, date2), 3);
+ EXPECT_EQ(datetime_diff<TimeUnit::MONTH>(date1, date2), 3 * 12 + 7);
+ EXPECT_EQ(datetime_diff<TimeUnit::WEEK>(date1, date2), 187); //
Approximately
+ EXPECT_EQ(datetime_diff<TimeUnit::DAY>(date1, date2), 1313);
+ EXPECT_EQ(datetime_diff<TimeUnit::HOUR>(date1, date2), 1313 * 24);
+ EXPECT_EQ(datetime_diff<TimeUnit::MINUTE>(date1, date2), 1313 * 24 *
60);
+ EXPECT_EQ(datetime_diff<TimeUnit::SECOND>(date1, date2), 1313 * 24 *
60 * 60);
+ EXPECT_EQ(datetime_diff<TimeUnit::MILLISECOND>(date1, date2), 1313 *
24 * 60 * 60 * 1000LL);
+ EXPECT_EQ(datetime_diff<TimeUnit::MICROSECOND>(date1, date2),
+ 1313 * 24 * 60 * 60 * 1000000LL);
+ }
+
+ // Test case 2: DATETIME to DATETIME - Testing rounding consistency across
units
+ {
+ // Test 2.1: Hour rounding - less than 1 hour should truncate to 0
+ {
+ DateV2Value<DateTimeV2ValueType> dt1;
+ std::string dt_str1 = "2023-05-10 10:00:00.000000";
+ std::string format = "%Y-%m-%d %H:%i:%s.%f";
+ EXPECT_TRUE(dt1.from_date_format_str(format.data(), format.size(),
dt_str1.data(),
+ dt_str1.size()));
+
+ DateV2Value<DateTimeV2ValueType> dt2;
+ std::string dt_str2 = "2023-05-10 10:59:59.999999";
+ EXPECT_TRUE(dt2.from_date_format_str(format.data(), format.size(),
dt_str2.data(),
+ dt_str2.size()));
+
+ EXPECT_EQ(datetime_diff<TimeUnit::HOUR>(dt1, dt2), 0);
+ }
+
+ // Test 2.2: Hour rounding - exactly 1 hour
+ {
+ DateV2Value<DateTimeV2ValueType> dt1;
+ std::string dt_str1 = "2023-05-10 10:00:00.000000";
+ std::string format = "%Y-%m-%d %H:%i:%s.%f";
+ EXPECT_TRUE(dt1.from_date_format_str(format.data(), format.size(),
dt_str1.data(),
+ dt_str1.size()));
+
+ DateV2Value<DateTimeV2ValueType> dt2;
+ std::string dt_str2 = "2023-05-10 11:00:00.000000";
+ EXPECT_TRUE(dt2.from_date_format_str(format.data(), format.size(),
dt_str2.data(),
+ dt_str2.size()));
+
+ EXPECT_EQ(datetime_diff<TimeUnit::HOUR>(dt1, dt2), 1);
+ }
+
+ // Test 2.3: Minute rounding - less than 1 minute should truncate to 0
+ {
+ DateV2Value<DateTimeV2ValueType> dt1;
+ std::string dt_str1 = "2023-05-10 10:15:00.000000";
+ std::string format = "%Y-%m-%d %H:%i:%s.%f";
+ EXPECT_TRUE(dt1.from_date_format_str(format.data(), format.size(),
dt_str1.data(),
+ dt_str1.size()));
+
+ DateV2Value<DateTimeV2ValueType> dt2;
+ std::string dt_str2 = "2023-05-10 10:15:59.999999";
+ EXPECT_TRUE(dt2.from_date_format_str(format.data(), format.size(),
dt_str2.data(),
+ dt_str2.size()));
+
+ EXPECT_EQ(datetime_diff<TimeUnit::MINUTE>(dt1, dt2), 0);
+ }
+
+ // Test 2.4: Minute rounding - exactly 1 minute
+ {
+ DateV2Value<DateTimeV2ValueType> dt1;
+ std::string dt_str1 = "2023-05-10 10:15:00.000000";
+ std::string format = "%Y-%m-%d %H:%i:%s.%f";
+ EXPECT_TRUE(dt1.from_date_format_str(format.data(), format.size(),
dt_str1.data(),
+ dt_str1.size()));
+
+ DateV2Value<DateTimeV2ValueType> dt2;
+ std::string dt_str2 = "2023-05-10 10:16:00.000000";
+ EXPECT_TRUE(dt2.from_date_format_str(format.data(), format.size(),
dt_str2.data(),
+ dt_str2.size()));
+
+ EXPECT_EQ(datetime_diff<TimeUnit::MINUTE>(dt1, dt2), 1);
+ }
+
+ // Test 2.5: Second rounding - less than 1 second should truncate to 0
+ {
+ DateV2Value<DateTimeV2ValueType> dt1;
+ std::string dt_str1 = "2023-05-10 10:15:30.000000";
+ std::string format = "%Y-%m-%d %H:%i:%s.%f";
+ EXPECT_TRUE(dt1.from_date_format_str(format.data(), format.size(),
dt_str1.data(),
+ dt_str1.size()));
+
+ DateV2Value<DateTimeV2ValueType> dt2;
+ std::string dt_str2 = "2023-05-10 10:15:30.999999";
+ EXPECT_TRUE(dt2.from_date_format_str(format.data(), format.size(),
dt_str2.data(),
+ dt_str2.size()));
+
+ EXPECT_EQ(datetime_diff<TimeUnit::SECOND>(dt1, dt2), 0);
+ }
+
+ // Test 2.6: Second rounding - exactly 1 second
+ {
+ DateV2Value<DateTimeV2ValueType> dt1;
+ std::string dt_str1 = "2023-05-10 10:15:30.000000";
+ std::string format = "%Y-%m-%d %H:%i:%s.%f";
+ EXPECT_TRUE(dt1.from_date_format_str(format.data(), format.size(),
dt_str1.data(),
+ dt_str1.size()));
+
+ DateV2Value<DateTimeV2ValueType> dt2;
+ std::string dt_str2 = "2023-05-10 10:15:31.000000";
+ EXPECT_TRUE(dt2.from_date_format_str(format.data(), format.size(),
dt_str2.data(),
+ dt_str2.size()));
+
+ EXPECT_EQ(datetime_diff<TimeUnit::SECOND>(dt1, dt2), 1);
+ }
+
+ // Test 2.7: Mixed unit truncating case - complex example with
multiple units
+ {
+ DateV2Value<DateTimeV2ValueType> dt1;
+ std::string dt_str1 = "2023-05-10 10:00:00.000000";
+ std::string format = "%Y-%m-%d %H:%i:%s.%f";
+ EXPECT_TRUE(dt1.from_date_format_str(format.data(), format.size(),
dt_str1.data(),
+ dt_str1.size()));
+
+ DateV2Value<DateTimeV2ValueType> dt2;
+ std::string dt_str2 = "2023-05-10 11:29:45.750000";
+ EXPECT_TRUE(dt2.from_date_format_str(format.data(), format.size(),
dt_str2.data(),
+ dt_str2.size()));
+
+ EXPECT_EQ(datetime_diff<TimeUnit::HOUR>(dt1, dt2),
+ 1); // 1h 29m 45.75s = 1.496h, truncates to 1
+ EXPECT_EQ(datetime_diff<TimeUnit::MINUTE>(dt1, dt2),
+ 89); // 1h 29m 45.75s = 89.7625m, truncates to 89
+ EXPECT_EQ(datetime_diff<TimeUnit::SECOND>(dt1, dt2),
+ 5385); // 1h 29m 45.75s = 5385.75s, truncates to 5385
+ }
+
+ // Test 2.8: Negative differences with truncating - less than 1 unit
+ {
+ DateV2Value<DateTimeV2ValueType> dt1;
+ std::string dt_str1 = "2023-05-10 10:15:00.000000";
+ std::string format = "%Y-%m-%d %H:%i:%s.%f";
+ EXPECT_TRUE(dt1.from_date_format_str(format.data(), format.size(),
dt_str1.data(),
+ dt_str1.size()));
+
+ DateV2Value<DateTimeV2ValueType> dt2;
+ std::string dt_str2 = "2023-05-10 10:14:30.250000";
+ EXPECT_TRUE(dt2.from_date_format_str(format.data(), format.size(),
dt_str2.data(),
+ dt_str2.size()));
+
+ EXPECT_EQ(datetime_diff<TimeUnit::MINUTE>(dt1, dt2), 0); //
-0.5m truncates to 0
+ EXPECT_EQ(datetime_diff<TimeUnit::SECOND>(dt1, dt2), -29); //
-29.75s truncates to -29
+ }
+
+ // Test 2.9: Negative differences with truncating - exact unit
+ {
+ DateV2Value<DateTimeV2ValueType> dt1;
+ std::string dt_str1 = "2023-05-10 10:15:00.000000";
+ std::string format = "%Y-%m-%d %H:%i:%s.%f";
+ EXPECT_TRUE(dt1.from_date_format_str(format.data(), format.size(),
dt_str1.data(),
+ dt_str1.size()));
+
+ DateV2Value<DateTimeV2ValueType> dt2;
+ std::string dt_str2 = "2023-05-10 10:14:00.000000";
+ EXPECT_TRUE(dt2.from_date_format_str(format.data(), format.size(),
dt_str2.data(),
+ dt_str2.size()));
+
+ EXPECT_EQ(datetime_diff<TimeUnit::MINUTE>(dt1, dt2), -1); //
Exactly -1 minute
+ EXPECT_EQ(datetime_diff<TimeUnit::SECOND>(dt1, dt2), -60); //
Exactly -60 seconds
+ }
+
+ // Test 2.10: Negative differences with truncating - complex example
+ {
+ DateV2Value<DateTimeV2ValueType> dt1;
+ std::string dt_str1 = "2023-05-10 11:30:30.750000";
+ std::string format = "%Y-%m-%d %H:%i:%s.%f";
+ EXPECT_TRUE(dt1.from_date_format_str(format.data(), format.size(),
dt_str1.data(),
+ dt_str1.size()));
+
+ DateV2Value<DateTimeV2ValueType> dt2;
+ std::string dt_str2 = "2023-05-10 10:00:00.000000";
+ EXPECT_TRUE(dt2.from_date_format_str(format.data(), format.size(),
dt_str2.data(),
+ dt_str2.size()));
+
+ EXPECT_EQ(datetime_diff<TimeUnit::HOUR>(dt1, dt2),
+ -1); // -1h 30m 30.75s = -1.5085h, truncates to -1
+ EXPECT_EQ(datetime_diff<TimeUnit::MINUTE>(dt1, dt2),
+ -90); // -1h 30m 30.75s = -90.5125m, truncates to -90
+ EXPECT_EQ(datetime_diff<TimeUnit::SECOND>(dt1, dt2),
+ -5430); // -1h 30m 30.75s = -5430.75s, truncates to -5430
+ }
+ }
+}
+
} // namespace doris::vectorized
diff --git
a/regression-test/data/nereids_p0/sql_functions/datetime_functions/test_date_function_v2.out
b/regression-test/data/nereids_p0/sql_functions/datetime_functions/test_date_function_v2.out
new file mode 100644
index 00000000000..347216879a5
Binary files /dev/null and
b/regression-test/data/nereids_p0/sql_functions/datetime_functions/test_date_function_v2.out
differ
diff --git
a/regression-test/data/query_p0/sql_functions/array_functions/test_array_functions.out
b/regression-test/data/query_p0/sql_functions/array_functions/test_array_functions.out
index fbd0c8f7dc3..9e665d45b22 100644
Binary files
a/regression-test/data/query_p0/sql_functions/array_functions/test_array_functions.out
and
b/regression-test/data/query_p0/sql_functions/array_functions/test_array_functions.out
differ
diff --git
a/regression-test/suites/nereids_p0/sql_functions/datetime_functions/test_date_function_v2.groovy
b/regression-test/suites/nereids_p0/sql_functions/datetime_functions/test_date_function_v2.groovy
new file mode 100644
index 00000000000..25ead06d66f
--- /dev/null
+++
b/regression-test/suites/nereids_p0/sql_functions/datetime_functions/test_date_function_v2.groovy
@@ -0,0 +1,53 @@
+// Licensed to the Apache Software Foundation (ASF) under one
+// or more contributor license agreements. See the NOTICE file
+// distributed with this work for additional information
+// regarding copyright ownership. The ASF licenses this file
+// to you under the Apache License, Version 2.0 (the
+// "License"); you may not use this file except in compliance
+// with the License. You may obtain a copy of the License at
+//
+// http://www.apache.org/licenses/LICENSE-2.0
+//
+// Unless required by applicable law or agreed to in writing,
+// software distributed under the License is distributed on an
+// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+// KIND, either express or implied. See the License for the
+// specific language governing permissions and limitations
+// under the License.
+
+suite("test_date_function_v2") {
+ sql """
+ admin set frontend config ("enable_date_conversion"="true");
+ """
+
+ qt_sql_diff1 "select days_diff('2023-10-15 00:00:00', '2023-10-14
00:00:00.1');"
+ testFoldConst("select days_diff('2023-10-15 00:00:00', '2023-10-14
00:00:00.1');")
+ qt_sql_diff2 "select days_diff('2023-10-15 00:00:00', '2023-10-14
00:00:00.1');"
+ testFoldConst("select days_diff('2023-10-15 00:00:00', '2023-10-14
00:00:00.1');")
+
+ qt_sql_diff3 "select weeks_diff('2023-10-15 00:00:00', '2023-10-08
00:00:00.1');"
+ testFoldConst("select weeks_diff('2023-10-15 00:00:00', '2023-10-08
00:00:00.1');")
+ qt_sql_diff4 "select weeks_diff('2023-10-15 00:00:00', '2023-10-08
00:00:00');"
+ testFoldConst("select weeks_diff('2023-10-15 00:00:00', '2023-10-08
00:00:00');")
+
+ qt_sql_diff5 "select hours_diff('2023-10-15 00:00:00', '2023-10-14
00:00:00.1');"
+ testFoldConst("select hours_diff('2023-10-15 00:00:00', '2023-10-14
00:00:00.1');")
+ qt_sql_diff6 "select hours_diff('2023-10-15 00:00:00', '2023-10-14
00:00:00');"
+ testFoldConst("select hours_diff('2023-10-15 00:00:00', '2023-10-14
00:00:00');")
+ qt_sql_diff7 "select minutes_diff('2023-10-15 00:00:00', '2023-10-14
00:00:00.1');"
+ testFoldConst("select minutes_diff('2023-10-15 00:00:00', '2023-10-14
00:00:00.1');")
+ qt_sql_diff8 "select minutes_diff('2023-10-15 00:00:00', '2023-10-14
00:00:00');"
+ testFoldConst("select minutes_diff('2023-10-15 00:00:00', '2023-10-14
00:00:00');")
+ qt_sql_diff9 "select seconds_diff('2023-10-15 00:00:00', '2023-10-14
00:00:00.1');"
+ testFoldConst("select seconds_diff('2023-10-15 00:00:00', '2023-10-14
00:00:00.1');")
+ qt_sql_diff10 "select seconds_diff('2023-10-15 00:00:00', '2023-10-14
00:00:00');"
+ testFoldConst("select seconds_diff('2023-10-15 00:00:00', '2023-10-14
00:00:00');")
+ qt_sql_diff11 "select milliseconds_diff('2023-10-15 00:00:00', '2023-10-14
00:00:00.1');"
+ testFoldConst("select milliseconds_diff('2023-10-15 00:00:00', '2023-10-14
00:00:00.1');")
+ qt_sql_diff12 "select milliseconds_diff('2023-10-15 00:00:00', '2023-10-14
00:00:00');"
+ testFoldConst("select milliseconds_diff('2023-10-15 00:00:00', '2023-10-14
00:00:00');")
+ qt_sql_diff13 "select microseconds_diff('2023-10-15 00:00:00', '2023-10-14
00:00:00.1');"
+ testFoldConst("select microseconds_diff('2023-10-15 00:00:00', '2023-10-14
00:00:00.1');")
+ qt_sql_diff14 "select microseconds_diff('2023-10-15 00:00:00', '2023-10-14
00:00:00');"
+ testFoldConst("select microseconds_diff('2023-10-15 00:00:00', '2023-10-14
00:00:00');")
+}
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org
For additional commands, e-mail: commits-h...@doris.apache.org
