This is an automated email from the ASF dual-hosted git repository.
panxiaolei pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/master by this push:
new e65a061256 [Enhancement](datetimev2-enhance) support
'microseconds_add' function for datetimev2 (#16970)
e65a061256 is described below
commit e65a061256b44b0465ed0445762623d13e37dfd9
Author: DuRipeng <[email protected]>
AuthorDate: Wed Feb 22 17:49:41 2023 +0800
[Enhancement](datetimev2-enhance) support 'microseconds_add' function for
datetimev2 (#16970)
support 'microseconds_add' function for datetimev2
---
.../function_date_or_datetime_computation.h | 1 +
.../function_date_or_datetime_computation_v2.cpp | 3 ++
be/src/vec/runtime/vdatetime_value.cpp | 38 +++++++++++++---
be/src/vec/runtime/vdatetime_value.h | 4 ++
.../date-time-functions/microseconds_add.md | 50 ++++++++++++++++++++++
docs/sidebars.json | 1 +
.../date-time-functions/microseconds_add.md | 50 ++++++++++++++++++++++
.../data/datatype_p0/datetimev2/test_exprs.out | 20 +++++++++
.../datatype_p0/datetimev2/test_exprs.groovy | 11 +++++
9 files changed, 172 insertions(+), 6 deletions(-)
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 1e9be44d88..026c8b6346 100644
--- a/be/src/vec/functions/function_date_or_datetime_computation.h
+++ b/be/src/vec/functions/function_date_or_datetime_computation.h
@@ -120,6 +120,7 @@ extern ResultType date_time_add(const Arg& t, Int64 delta,
bool& is_null) {
}
\
}
+ADD_TIME_FUNCTION_IMPL(AddMicrosecondsImpl, microseconds_add, MICROSECOND);
ADD_TIME_FUNCTION_IMPL(AddSecondsImpl, seconds_add, SECOND);
ADD_TIME_FUNCTION_IMPL(AddMinutesImpl, minutes_add, MINUTE);
ADD_TIME_FUNCTION_IMPL(AddHoursImpl, hours_add, HOUR);
diff --git a/be/src/vec/functions/function_date_or_datetime_computation_v2.cpp
b/be/src/vec/functions/function_date_or_datetime_computation_v2.cpp
index 23d7f296dc..ed701a85cb 100644
--- a/be/src/vec/functions/function_date_or_datetime_computation_v2.cpp
+++ b/be/src/vec/functions/function_date_or_datetime_computation_v2.cpp
@@ -44,6 +44,8 @@ using FunctionToYearWeekTwoArgsV2 =
using FunctionToWeekTwoArgsV2 =
FunctionDateOrDateTimeComputation<ToWeekTwoArgsImpl<DataTypeDateV2>>;
+using FunctionDatetimeV2AddMicroseconds =
+
FunctionDateOrDateTimeComputation<AddMicrosecondsImpl<DataTypeDateTimeV2>>;
using FunctionDatetimeV2AddSeconds =
FunctionDateOrDateTimeComputation<AddSecondsImpl<DataTypeDateTimeV2>>;
using FunctionDatetimeV2AddMinutes =
@@ -116,6 +118,7 @@ void
register_function_date_time_computation_v2(SimpleFunctionFactory& factory)
factory.register_function<FunctionAddYearsV2>();
factory.register_function<FunctionAddQuartersV2>();
+ factory.register_function<FunctionDatetimeV2AddMicroseconds>();
factory.register_function<FunctionDatetimeV2AddSeconds>();
factory.register_function<FunctionDatetimeV2AddMinutes>();
factory.register_function<FunctionDatetimeV2AddHours>();
diff --git a/be/src/vec/runtime/vdatetime_value.cpp
b/be/src/vec/runtime/vdatetime_value.cpp
index 69eae65d99..378ed79556 100644
--- a/be/src/vec/runtime/vdatetime_value.cpp
+++ b/be/src/vec/runtime/vdatetime_value.cpp
@@ -2553,17 +2553,26 @@ bool DateV2Value<T>::date_add_interval(const
TimeInterval& interval, DateV2Value
int sign = interval.is_neg ? -1 : 1;
- if constexpr ((unit == SECOND) || (unit == MINUTE) || (unit == HOUR) ||
+ if constexpr ((unit == MICROSECOND) || (unit == SECOND) || (unit ==
MINUTE) || (unit == HOUR) ||
(unit == SECOND_MICROSECOND) || (unit == MINUTE_MICROSECOND)
||
(unit == MINUTE_SECOND) || (unit == HOUR_MICROSECOND) ||
(unit == HOUR_SECOND) ||
(unit == HOUR_MINUTE) || (unit == DAY_MICROSECOND) || (unit
== DAY_SECOND) ||
(unit == DAY_MINUTE) || (unit == DAY_HOUR) || (unit == DAY)
|| (unit == WEEK)) {
// This may change the day information
+ constexpr int64_t microseconds_in_one_second = 1000000L;
+ int64_t microseconds = this->microsecond() + sign *
interval.microsecond;
+ int64_t extra_second = microseconds / microseconds_in_one_second;
+ microseconds -= extra_second * microseconds_in_one_second;
int64_t seconds = (this->day() - 1) * 86400L + this->hour() * 3600L +
this->minute() * 60 +
this->second() +
sign * (interval.day * 86400 + interval.hour * 3600 +
- interval.minute * 60 + interval.second);
+ interval.minute * 60 + interval.second) +
+ extra_second;
+ if (microseconds < 0) {
+ seconds--;
+ microseconds += microseconds_in_one_second;
+ }
int64_t days = seconds / 86400;
seconds %= 86400L;
if (seconds < 0) {
@@ -2574,7 +2583,7 @@ bool DateV2Value<T>::date_add_interval(const
TimeInterval& interval, DateV2Value
if (!to_value.get_date_from_daynr(day_nr)) {
return false;
}
- to_value.set_time(seconds / 3600, (seconds / 60) % 60, seconds % 60,
0);
+ to_value.set_time(seconds / 3600, (seconds / 60) % 60, seconds % 60,
microseconds);
} else if constexpr (unit == YEAR) {
// This only change year information
to_value.template set_time_unit<TimeUnit::YEAR>(date_v2_value_.year_ +
interval.year);
@@ -2611,17 +2620,26 @@ bool DateV2Value<T>::date_add_interval(const
TimeInterval& interval) {
int sign = interval.is_neg ? -1 : 1;
- if constexpr ((unit == SECOND) || (unit == MINUTE) || (unit == HOUR) ||
+ if constexpr ((unit == MICROSECOND) || (unit == SECOND) || (unit ==
MINUTE) || (unit == HOUR) ||
(unit == SECOND_MICROSECOND) || (unit == MINUTE_MICROSECOND)
||
(unit == MINUTE_SECOND) || (unit == HOUR_MICROSECOND) ||
(unit == HOUR_SECOND) ||
(unit == HOUR_MINUTE) || (unit == DAY_MICROSECOND) || (unit
== DAY_SECOND) ||
(unit == DAY_MINUTE) || (unit == DAY_HOUR) || (unit == DAY)
|| (unit == WEEK)) {
// This may change the day information
+ constexpr int64_t microseconds_in_one_second = 1000000L;
+ int64_t microseconds = this->microsecond() + sign *
interval.microsecond;
+ int64_t extra_second = microseconds / microseconds_in_one_second;
+ microseconds -= extra_second * microseconds_in_one_second;
int64_t seconds = (this->day() - 1) * 86400L + this->hour() * 3600L +
this->minute() * 60 +
this->second() +
sign * (interval.day * 86400 + interval.hour * 3600 +
- interval.minute * 60 + interval.second);
+ interval.minute * 60 + interval.second) +
+ extra_second;
+ if (microseconds < 0) {
+ seconds--;
+ microseconds += microseconds_in_one_second;
+ }
int64_t days = seconds / 86400;
seconds %= 86400L;
if (seconds < 0) {
@@ -2633,7 +2651,7 @@ bool DateV2Value<T>::date_add_interval(const
TimeInterval& interval) {
return false;
}
if constexpr (is_datetime) {
- this->set_time(seconds / 3600, (seconds / 60) % 60, seconds % 60,
this->microsecond());
+ this->set_time(seconds / 3600, (seconds / 60) % 60, seconds % 60,
microseconds);
}
} else if constexpr (unit == YEAR) {
// This only change year information
@@ -3334,6 +3352,10 @@ template int64_t
VecDateTimeValue::second_diff<DateTimeV2ValueType>(
const DateV2Value<DateTimeV2ValueType>& rhs) const;
#define DELARE_DATE_ADD_INTERVAL(DateValueType1, DateValueType2)
\
+ template bool
doris::vectorized::DateV2Value<DateValueType1>::date_add_interval< \
+ TimeUnit::MICROSECOND, DateValueType2>(
\
+ doris::vectorized::TimeInterval const&,
\
+ doris::vectorized::DateV2Value<DateValueType2>&);
\
template bool
doris::vectorized::DateV2Value<DateValueType1>::date_add_interval< \
TimeUnit::SECOND, DateValueType2>(doris::vectorized::TimeInterval
const&, \
doris::vectorized::DateV2Value<DateValueType2>&); \
@@ -3373,6 +3395,8 @@ template bool
VecDateTimeValue::date_add_interval<TimeUnit::YEAR>(const TimeInte
template bool VecDateTimeValue::date_add_interval<TimeUnit::QUARTER>(const
TimeInterval& interval);
template bool VecDateTimeValue::date_add_interval<TimeUnit::WEEK>(const
TimeInterval& interval);
+template bool
DateV2Value<DateV2ValueType>::date_add_interval<TimeUnit::MICROSECOND>(
+ const TimeInterval& interval);
template bool
DateV2Value<DateV2ValueType>::date_add_interval<TimeUnit::SECOND>(
const TimeInterval& interval);
template bool
DateV2Value<DateV2ValueType>::date_add_interval<TimeUnit::MINUTE>(
@@ -3390,6 +3414,8 @@ template bool
DateV2Value<DateV2ValueType>::date_add_interval<TimeUnit::QUARTER>
template bool DateV2Value<DateV2ValueType>::date_add_interval<TimeUnit::WEEK>(
const TimeInterval& interval);
+template bool
DateV2Value<DateTimeV2ValueType>::date_add_interval<TimeUnit::MICROSECOND>(
+ const TimeInterval& interval);
template bool
DateV2Value<DateTimeV2ValueType>::date_add_interval<TimeUnit::SECOND>(
const TimeInterval& interval);
template bool
DateV2Value<DateTimeV2ValueType>::date_add_interval<TimeUnit::MINUTE>(
diff --git a/be/src/vec/runtime/vdatetime_value.h
b/be/src/vec/runtime/vdatetime_value.h
index 426eaa3ff2..72e8e2fb12 100644
--- a/be/src/vec/runtime/vdatetime_value.h
+++ b/be/src/vec/runtime/vdatetime_value.h
@@ -36,6 +36,7 @@ class DateTimeValue;
namespace vectorized {
enum TimeUnit {
+ MICROSECOND,
SECOND,
MINUTE,
HOUR,
@@ -111,6 +112,9 @@ struct TimeInterval {
case SECOND_MICROSECOND:
microsecond = count;
break;
+ case MICROSECOND:
+ microsecond = count;
+ break;
default:
break;
}
diff --git
a/docs/en/docs/sql-manual/sql-functions/date-time-functions/microseconds_add.md
b/docs/en/docs/sql-manual/sql-functions/date-time-functions/microseconds_add.md
new file mode 100644
index 0000000000..5103ca9a03
--- /dev/null
+++
b/docs/en/docs/sql-manual/sql-functions/date-time-functions/microseconds_add.md
@@ -0,0 +1,50 @@
+---
+{
+ "title": "microseconds_add",
+ "language": "en"
+}
+---
+
+<!--
+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.
+-->
+
+## microseconds_add
+### description
+#### Syntax
+
+`DATETIMEV2 microseconds_add(DATETIMEV2 basetime, INT delta)`
+- basetime: Base time whose type is DATETIMEV2
+- delta: Microseconds to add to basetime
+- Return type of this function is DATETIMEV2
+
+### example
+```
+mysql> select now(3), microseconds_add(now(3), 100000);
++-------------------------+----------------------------------+
+| now(3) | microseconds_add(now(3), 100000) |
++-------------------------+----------------------------------+
+| 2023-02-21 11:35:56.556 | 2023-02-21 11:35:56.656 |
++-------------------------+----------------------------------+
+```
+`now(3)` returns current time as type DATETIMEV2 with precision
3d，`microseconds_add(now(3), 100000)` means 100000 microseconds after current
time
+
+### keywords
+ microseconds_add
+
+
\ No newline at end of file
diff --git a/docs/sidebars.json b/docs/sidebars.json
index dcd4eef73e..01b5551f58 100644
--- a/docs/sidebars.json
+++ b/docs/sidebars.json
@@ -342,6 +342,7 @@
"sql-manual/sql-functions/date-time-functions/date_trunc",
"sql-manual/sql-functions/date-time-functions/date_format",
"sql-manual/sql-functions/date-time-functions/datediff",
+
"sql-manual/sql-functions/date-time-functions/microseconds_add",
"sql-manual/sql-functions/date-time-functions/minutes_add",
"sql-manual/sql-functions/date-time-functions/minutes_diff",
"sql-manual/sql-functions/date-time-functions/minutes_sub",
diff --git
a/docs/zh-CN/docs/sql-manual/sql-functions/date-time-functions/microseconds_add.md
b/docs/zh-CN/docs/sql-manual/sql-functions/date-time-functions/microseconds_add.md
new file mode 100644
index 0000000000..0c260fd2a4
--- /dev/null
+++
b/docs/zh-CN/docs/sql-manual/sql-functions/date-time-functions/microseconds_add.md
@@ -0,0 +1,50 @@
+---
+{
+ "title": "microseconds_add",
+ "language": "zh-CN"
+}
+---
+
+<!--
+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.
+-->
+
+## microseconds_add
+### description
+#### Syntax
+
+`DATETIMEV2 microseconds_add(DATETIMEV2 basetime, INT delta)`
+- basetime: DATETIMEV2 类型起始时间
+- delta: 从 basetime 起需要相加的微秒数
+- 返回类型为 DATETIMEV2
+
+### example
+```
+mysql> select now(3), microseconds_add(now(3), 100000);
++-------------------------+----------------------------------+
+| now(3) | microseconds_add(now(3), 100000) |
++-------------------------+----------------------------------+
+| 2023-02-21 11:35:56.556 | 2023-02-21 11:35:56.656 |
++-------------------------+----------------------------------+
+```
+`now(3)` 返回精度位数 3 的 DATETIMEV2 类型当前时间，`microseconds_add(now(3), 100000)`
返回当前时间加上 100000 微秒后的 DATETIMEV2 类型时间
+
+### keywords
+ microseconds_add
+
+
\ No newline at end of file
diff --git a/regression-test/data/datatype_p0/datetimev2/test_exprs.out
b/regression-test/data/datatype_p0/datetimev2/test_exprs.out
index 18fdd0de4f..f1579a0397 100644
--- a/regression-test/data/datatype_p0/datetimev2/test_exprs.out
+++ b/regression-test/data/datatype_p0/datetimev2/test_exprs.out
@@ -7,3 +7,23 @@
2022-01-01T11:11:11.111
2022-01-01T11:11:11.222
+-- !sql_microseconds_add_datetimev2_1 --
+2022-01-01T11:11:11.211
+2022-01-01T11:11:11.322
+
+-- !sql_microseconds_add_datetimev2_2 --
+2022-01-01T11:11:11.311
+2022-01-01T11:11:11.422
+
+-- !sql_microseconds_add_datetimev2_3 --
+2022-01-01T11:11:11.911
+2022-01-01T11:11:12.022
+
+-- !sql_microseconds_add_datetimev2_4 --
+2022-01-01T11:11:11.011
+2022-01-01T11:11:11.122
+
+-- !sql_microseconds_add_datetimev2_5 --
+2022-01-01T11:11:10.911
+2022-01-01T11:11:11.022
+
diff --git a/regression-test/suites/datatype_p0/datetimev2/test_exprs.groovy
b/regression-test/suites/datatype_p0/datetimev2/test_exprs.groovy
index 89eba7c464..4946118661 100644
--- a/regression-test/suites/datatype_p0/datetimev2/test_exprs.groovy
+++ b/regression-test/suites/datatype_p0/datetimev2/test_exprs.groovy
@@ -41,4 +41,15 @@ suite("test_exprs") {
qt_select_all "select * from ${table1} order by col"
qt_sql_cast_datetimev2 " select cast(col as datetimev2(5)) col1 from
${table1} order by col1; "
+
+ // `microseconds_add` suites
+ // 1. Positive microseconds delta
+ qt_sql_microseconds_add_datetimev2_1 " select microseconds_add(col,
100000) col1 from ${table1} order by col1; "
+ qt_sql_microseconds_add_datetimev2_2 " select microseconds_add(col,
200000) col1 from ${table1} order by col1; "
+ // 1.1 Positive microseconds delta affects second change
+ qt_sql_microseconds_add_datetimev2_3 " select microseconds_add(col,
800000) col1 from ${table1} order by col1; "
+ // 2. Negative microseconds delta
+ qt_sql_microseconds_add_datetimev2_4 " select microseconds_add(col,
-100000) col1 from ${table1} order by col1; "
+ // 2.1 Negative microseconds delta affects second change
+ qt_sql_microseconds_add_datetimev2_5 " select microseconds_add(col,
-200000) col1 from ${table1} order by col1; "
}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
