You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@doris.apache.org by yi...@apache.org on 2022/10/28 00:41:22 UTC
[doris] branch master updated: [function](date function) add new date function 'to_monday' #13707
This is an automated email from the ASF dual-hosted git repository.
yiguolei 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 c108554f14 [function](date function) add new date function 'to_monday' #13707
c108554f14 is described below
commit c108554f14ee9be0cdf73cd69f8698e42874dd91
Author: lsy3993 <11...@users.noreply.github.com>
AuthorDate: Fri Oct 28 08:41:16 2022 +0800
[function](date function) add new date function 'to_monday' #13707
---
be/src/vec/functions/function_timestamp.cpp | 122 +++++++++++++++++++++
.../sql-functions/date-time-functions/to_monday.md | 46 ++++++++
docs/sidebars.json | 1 +
.../sql-functions/date-time-functions/to_monday.md | 45 ++++++++
gensrc/script/doris_builtins_functions.py | 8 ++
.../datetime_functions/test_date_function.out | 7 ++
.../datetime_functions/test_date_function.groovy | 27 +++++
7 files changed, 256 insertions(+)
diff --git a/be/src/vec/functions/function_timestamp.cpp b/be/src/vec/functions/function_timestamp.cpp
index 63fb1d900c..f941408dc8 100644
--- a/be/src/vec/functions/function_timestamp.cpp
+++ b/be/src/vec/functions/function_timestamp.cpp
@@ -717,6 +717,124 @@ struct LastDayImpl {
}
};
+template <typename DateType>
+struct MondayImpl {
+ static constexpr auto name = "to_monday";
+
+ static Status execute_impl(FunctionContext* context, Block& block,
+ const ColumnNumbers& arguments, size_t result,
+ size_t input_rows_count) {
+ auto null_map = ColumnUInt8::create(input_rows_count, 0);
+ ColumnPtr res_column;
+ ColumnPtr argument_column =
+ block.get_by_position(arguments[0]).column->convert_to_full_column_if_const();
+ if constexpr (std::is_same_v<DateType, DataTypeDateTime> ||
+ std::is_same_v<DateType, DataTypeDate>) {
+ auto data_col = assert_cast<const ColumnVector<Int64>*>(argument_column.get());
+ res_column = ColumnInt64::create(input_rows_count);
+ execute_straight<VecDateTimeValue, Int64, Int64>(
+ input_rows_count, null_map->get_data(), data_col->get_data(),
+ static_cast<ColumnVector<Int64>*>(res_column->assume_mutable().get())
+ ->get_data());
+
+ } else if constexpr (std::is_same_v<DateType, DataTypeDateV2>) {
+ auto data_col = assert_cast<const ColumnVector<UInt32>*>(argument_column.get());
+ res_column = ColumnVector<UInt32>::create(input_rows_count);
+ execute_straight<DateV2Value<DateV2ValueType>, UInt32, UInt32>(
+ input_rows_count, null_map->get_data(), data_col->get_data(),
+ static_cast<ColumnVector<UInt32>*>(res_column->assume_mutable().get())
+ ->get_data());
+
+ } else if constexpr (std::is_same_v<DateType, DataTypeDateTimeV2>) {
+ auto data_col = assert_cast<const ColumnVector<UInt64>*>(argument_column.get());
+ res_column = ColumnVector<UInt32>::create(input_rows_count);
+ execute_straight<DateV2Value<DateTimeV2ValueType>, UInt32, UInt64>(
+ input_rows_count, null_map->get_data(), data_col->get_data(),
+ static_cast<ColumnVector<UInt32>*>(res_column->assume_mutable().get())
+ ->get_data());
+ }
+ block.replace_by_position(
+ result, ColumnNullable::create(std::move(res_column), std::move(null_map)));
+ return Status::OK();
+ }
+
+ template <typename DateValueType, typename ReturnType, typename InputDateType>
+ static void execute_straight(size_t input_rows_count, NullMap& null_map,
+ const PaddedPODArray<InputDateType>& data_col,
+ PaddedPODArray<ReturnType>& res_data) {
+ for (int i = 0; i < input_rows_count; i++) {
+ if constexpr (std::is_same_v<DateValueType, VecDateTimeValue>) {
+ const auto& cur_data = data_col[i];
+ auto ts_value = binary_cast<Int64, VecDateTimeValue>(cur_data);
+ if (!ts_value.is_valid_date()) {
+ null_map[i] = 1;
+ continue;
+ }
+ if (is_special_day(ts_value.year(), ts_value.month(), ts_value.day())) {
+ ts_value.set_time(ts_value.year(), ts_value.month(), 1, 0, 0, 0);
+ ts_value.set_type(TIME_DATE);
+ res_data[i] = binary_cast<VecDateTimeValue, Int64>(ts_value);
+ continue;
+ }
+
+ // day_of_week, from 1(Mon) to 7(Sun)
+ int day_of_week = ts_value.weekday() + 1;
+ int gap_of_monday = day_of_week - 1;
+ TimeInterval interval(DAY, gap_of_monday, true);
+ ts_value.template date_add_interval<DAY>(interval);
+ ts_value.set_type(TIME_DATE);
+ res_data[i] = binary_cast<VecDateTimeValue, Int64>(ts_value);
+
+ } else if constexpr (std::is_same_v<DateValueType, DateV2Value<DateV2ValueType>>) {
+ const auto& cur_data = data_col[i];
+ auto ts_value = binary_cast<UInt32, DateValueType>(cur_data);
+ if (!ts_value.is_valid_date()) {
+ null_map[i] = 1;
+ continue;
+ }
+ if (is_special_day(ts_value.year(), ts_value.month(), ts_value.day())) {
+ ts_value.template set_time_unit<TimeUnit::DAY>(1);
+ res_data[i] = binary_cast<DateValueType, UInt32>(ts_value);
+ continue;
+ }
+
+ // day_of_week, from 1(Mon) to 7(Sun)
+ int day_of_week = ts_value.weekday() + 1;
+ int gap_of_monday = day_of_week - 1;
+ TimeInterval interval(DAY, gap_of_monday, true);
+ ts_value.template date_add_interval<DAY>(interval);
+ res_data[i] = binary_cast<DateValueType, UInt32>(ts_value);
+ } else {
+ const auto& cur_data = data_col[i];
+ auto ts_value = binary_cast<UInt64, DateValueType>(cur_data);
+ if (!ts_value.is_valid_date()) {
+ null_map[i] = 1;
+ continue;
+ }
+ if (is_special_day(ts_value.year(), ts_value.month(), ts_value.day())) {
+ ts_value.set_time(ts_value.year(), ts_value.month(), 1, 0, 0, 0, 0);
+ UInt64 cast_value = binary_cast<DateValueType, UInt64>(ts_value);
+ DataTypeDateTimeV2::cast_to_date_v2(cast_value, res_data[i]);
+ continue;
+ }
+ // day_of_week, from 1(Mon) to 7(Sun)
+ int day_of_week = ts_value.weekday() + 1;
+ int gap_of_monday = day_of_week - 1;
+ TimeInterval interval(DAY, gap_of_monday, true);
+ ts_value.template date_add_interval<DAY>(interval);
+ ts_value.set_time(ts_value.year(), ts_value.month(), ts_value.day(), 0, 0, 0, 0);
+ UInt64 cast_value = binary_cast<DateValueType, UInt64>(ts_value);
+ DataTypeDateTimeV2::cast_to_date_v2(cast_value, res_data[i]);
+ }
+ }
+ }
+
+ // specially, 1970-01-01, 1970-01-02, 1970-01-03 and 1970-01-04 return 1970-01-01
+ static bool is_special_day(int year, int month, int day) {
+ return year == 1970 && month == 1 && day > 0 && day < 5;
+ }
+};
+
template <typename Impl>
class FunctionOtherTypesToDateType : public IFunction {
public:
@@ -771,6 +889,10 @@ void register_function_timestamp(SimpleFunctionFactory& factory) {
factory.register_function<FunctionDateOrDateTimeToDate<LastDayImpl, DataTypeDate>>();
factory.register_function<FunctionDateOrDateTimeToDate<LastDayImpl, DataTypeDateV2>>();
factory.register_function<FunctionDateOrDateTimeToDate<LastDayImpl, DataTypeDateTimeV2>>();
+ factory.register_function<FunctionDateOrDateTimeToDate<MondayImpl, DataTypeDateV2>>();
+ factory.register_function<FunctionDateOrDateTimeToDate<MondayImpl, DataTypeDateTimeV2>>();
+ factory.register_function<FunctionDateOrDateTimeToDate<MondayImpl, DataTypeDate>>();
+ factory.register_function<FunctionDateOrDateTimeToDate<MondayImpl, DataTypeDateTime>>();
}
} // namespace doris::vectorized
diff --git a/docs/en/docs/sql-manual/sql-functions/date-time-functions/to_monday.md b/docs/en/docs/sql-manual/sql-functions/date-time-functions/to_monday.md
new file mode 100644
index 0000000000..dde3f877aa
--- /dev/null
+++ b/docs/en/docs/sql-manual/sql-functions/date-time-functions/to_monday.md
@@ -0,0 +1,46 @@
+---
+{
+ "title": "to_monday",
+ "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.
+-->
+
+## to_monday
+### Description
+#### Syntax
+
+`DATE to_monday(DATETIME date)`
+
+Round a date or datetime down to the nearest Monday, return type is Date or DateV2.
+Specially, input 1970-01-01, 1970-01-02, 1970-01-03 and 1970-01-04 will return '1970-01-01'
+
+### example
+
+```
+MySQL [(none)]> select to_monday('2022-09-10');
++----------------------------------+
+| to_monday('2022-09-10 00:00:00') |
++----------------------------------+
+| 2022-09-05 |
++----------------------------------+
+```
+
+### keywords
+ MONDAY
diff --git a/docs/sidebars.json b/docs/sidebars.json
index 4cf82d482b..6d515f636d 100644
--- a/docs/sidebars.json
+++ b/docs/sidebars.json
@@ -294,6 +294,7 @@
"sql-manual/sql-functions/date-time-functions/second",
"sql-manual/sql-functions/date-time-functions/from_days",
"sql-manual/sql-functions/date-time-functions/last_day",
+ "sql-manual/sql-functions/date-time-functions/to_monday",
"sql-manual/sql-functions/date-time-functions/from_unixtime",
"sql-manual/sql-functions/date-time-functions/unix_timestamp",
"sql-manual/sql-functions/date-time-functions/utc_timestamp",
diff --git a/docs/zh-CN/docs/sql-manual/sql-functions/date-time-functions/to_monday.md b/docs/zh-CN/docs/sql-manual/sql-functions/date-time-functions/to_monday.md
new file mode 100644
index 0000000000..55f6e46b05
--- /dev/null
+++ b/docs/zh-CN/docs/sql-manual/sql-functions/date-time-functions/to_monday.md
@@ -0,0 +1,45 @@
+---
+{
+ "title": "to_monday",
+ "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.
+-->
+
+## to_monday
+### Description
+#### Syntax
+
+`DATE to_monday(DATETIME date)`
+
+将日期或带时间的日期向下舍入到最近的星期一。作为一种特殊情况，日期参数 1970-01-01、1970-01-02、1970-01-03 和 1970-01-04 返回日期 1970-01-01
+
+### example
+
+```
+MySQL [(none)]> select to_monday('2022-09-10');
++----------------------------------+
+| to_monday('2022-09-10 00:00:00') |
++----------------------------------+
+| 2022-09-05 |
++----------------------------------+
+```
+
+### keywords
+ MONDAY
diff --git a/gensrc/script/doris_builtins_functions.py b/gensrc/script/doris_builtins_functions.py
index 8e9b447cde..444cf48716 100755
--- a/gensrc/script/doris_builtins_functions.py
+++ b/gensrc/script/doris_builtins_functions.py
@@ -532,6 +532,14 @@ visible_functions = [
[['last_day'], 'DATEV2', ['DATEV2'],
'_ZN5doris18TimestampFunctions8last_dayEPN9doris_udf15FunctionContextERKNS1_11DateTimeValE',
'', '', 'vec', 'ALWAYS_NULLABLE'],
+ [['to_monday'], 'DATEV2', ['DATETIMEV2'],
+ '', '', '', 'vec', 'ALWAYS_NULLABLE'],
+ [['to_monday'], 'DATEV2', ['DATEV2'],
+ '','', '', 'vec', 'ALWAYS_NULLABLE'],
+ [['to_monday'], 'DATE', ['DATETIME'],
+ '', '', '', 'vec', 'ALWAYS_NULLABLE'],
+ [['to_monday'], 'DATE', ['DATE'],
+ '','', '', 'vec', 'ALWAYS_NULLABLE'],
[['to_days'], 'INT', ['DATE'],
'_ZN5doris18TimestampFunctions7to_daysEPN9doris_udf15FunctionContextERKNS1_11DateTimeValE',
'', '', 'vec', 'ALWAYS_NULLABLE'],
diff --git a/regression-test/data/query_p0/sql_functions/datetime_functions/test_date_function.out b/regression-test/data/query_p0/sql_functions/datetime_functions/test_date_function.out
index b465af0658..847060d35b 100644
--- a/regression-test/data/query_p0/sql_functions/datetime_functions/test_date_function.out
+++ b/regression-test/data/query_p0/sql_functions/datetime_functions/test_date_function.out
@@ -541,3 +541,10 @@ true
2022-01-31 2022-01-31
2022-02-28 2022-02-28
+-- !sql --
+\N \N \N \N
+1970-01-01 1970-01-01 1970-01-01 1970-01-01
+2000-01-31 2000-01-31 2000-01-31 2000-01-31
+2021-12-27 2021-12-27 2021-12-27 2021-12-27
+2022-02-28 2022-02-28 2022-02-28 2022-02-28
+
diff --git a/regression-test/suites/query_p0/sql_functions/datetime_functions/test_date_function.groovy b/regression-test/suites/query_p0/sql_functions/datetime_functions/test_date_function.groovy
index 7ff926157f..b63de704e1 100644
--- a/regression-test/suites/query_p0/sql_functions/datetime_functions/test_date_function.groovy
+++ b/regression-test/suites/query_p0/sql_functions/datetime_functions/test_date_function.groovy
@@ -477,4 +477,31 @@ suite("test_date_function") {
select last_day(birth), last_day(birth1) from ${tableName};
"""
sql """ DROP TABLE IF EXISTS ${tableName}; """
+
+ // test to_monday
+ sql """ SET enable_vectorized_engine = TRUE; """
+ sql """ DROP TABLE IF EXISTS ${tableName}; """
+ sql """
+ CREATE TABLE IF NOT EXISTS ${tableName} (
+ birth date,
+ birth1 datev2,
+ birth2 datetime,
+ birth3 datetimev2)
+ UNIQUE KEY(birth, birth1, birth2, birth3)
+ DISTRIBUTED BY HASH (birth) BUCKETS 1
+ PROPERTIES( "replication_allocation" = "tag.location.default: 1");
+ """
+ sql """
+ insert into ${tableName} values
+ ('2022-01-01', '2022-01-01', '2022-01-01 00:00:00', '2022-01-01 00:00:00'),
+ ('2000-02-01', '2000-02-01', '2000-02-01 00:00:00', '2000-02-01 00:00:00.123'),
+ ('2022-02-29', '2022-02-29', '2022-02-29 00:00:00', '2022-02-29 00:00:00'),
+ ('2022-02-28', '2022-02-28', '2022-02-28 23:59:59', '2022-02-28 23:59:59'),
+ ('1970-01-02', '1970-01-02', '1970-01-02 01:02:03', '1970-01-02 02:03:04');"""
+ qt_sql """
+ select to_monday(birth), to_monday(birth1),
+ to_monday(birth2), to_monday(birth3)
+ from ${tableName};
+ """
+ sql """ DROP TABLE IF EXISTS ${tableName}; """
}
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org