You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@doris.apache.org by da...@apache.org on 2022/10/25 05:46:23 UTC
[doris] branch master updated: add date function 'last_day' (#13609)
This is an automated email from the ASF dual-hosted git repository.
dataroaring 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 f802fc37ff add date function 'last_day' (#13609)
f802fc37ff is described below
commit f802fc37ff93d837f1b5ed19426123bb9414b954
Author: lsy3993 <11...@users.noreply.github.com>
AuthorDate: Tue Oct 25 13:46:16 2022 +0800
add date function 'last_day' (#13609)
---
be/src/exprs/timestamp_functions.cpp | 31 +++++
be/src/exprs/timestamp_functions.h | 2 +
be/src/vec/functions/function_timestamp.cpp | 152 +++++++++++++++++++++
.../sql-functions/date-time-functions/last_day.md | 49 +++++++
docs/sidebars.json | 1 +
.../sql-functions/date-time-functions/last_day.md | 49 +++++++
gensrc/script/doris_builtins_functions.py | 12 ++
.../datetime_functions/test_date_function.out | 12 ++
.../datetime_functions/test_date_function.groovy | 48 +++++++
9 files changed, 356 insertions(+)
diff --git a/be/src/exprs/timestamp_functions.cpp b/be/src/exprs/timestamp_functions.cpp
index 3df58c48da..559cfbe50e 100644
--- a/be/src/exprs/timestamp_functions.cpp
+++ b/be/src/exprs/timestamp_functions.cpp
@@ -707,6 +707,37 @@ DateTimeVal TimestampFunctions::from_days(FunctionContext* ctx, const IntVal& da
return ts_val;
}
+DateTimeVal TimestampFunctions::last_day(FunctionContext* ctx, const DateTimeVal& ts_val) {
+ if (ts_val.is_null) {
+ return DateTimeVal::null();
+ }
+
+ DateTimeValue ts_value = DateTimeValue::from_datetime_val(ts_val);
+
+ bool is_leap_year = doris::is_leap(ts_value.year());
+ if (ts_value.month() == 2) {
+ int day = is_leap_year ? 29 : 28;
+ ts_value.set_time(ts_value.year(), ts_value.month(), day, 0, 0, 0, 0);
+ } else {
+ if (ts_value.month() == 1 || ts_value.month() == 3 || ts_value.month() == 5 ||
+ ts_value.month() == 7 || ts_value.month() == 8 || ts_value.month() == 10 ||
+ ts_value.month() == 12) {
+ ts_value.set_time(ts_value.year(), ts_value.month(), 31, 0, 0, 0, 0);
+ } else {
+ ts_value.set_time(ts_value.year(), ts_value.month(), 30, 0, 0, 0, 0);
+ }
+ }
+
+ ts_value.set_type(TIME_DATE);
+ if (!ts_value.is_valid_date()) {
+ return DateTimeVal::null();
+ }
+
+ DateTimeVal result_ts_val;
+ ts_value.to_datetime_val(&result_ts_val);
+ return result_ts_val;
+}
+
IntVal TimestampFunctions::to_days(FunctionContext* ctx, const DateTimeVal& ts_val) {
if (ts_val.is_null) {
return IntVal::null();
diff --git a/be/src/exprs/timestamp_functions.h b/be/src/exprs/timestamp_functions.h
index 22d678cb20..2183c2c5e7 100644
--- a/be/src/exprs/timestamp_functions.h
+++ b/be/src/exprs/timestamp_functions.h
@@ -150,6 +150,8 @@ public:
const doris_udf::StringVal& format);
static doris_udf::DateTimeVal from_days(doris_udf::FunctionContext* ctx,
const doris_udf::IntVal& days);
+ static doris_udf::DateTimeVal last_day(doris_udf::FunctionContext* ctx,
+ const doris_udf::DateTimeVal& ts_val);
static doris_udf::IntVal to_days(doris_udf::FunctionContext* ctx,
const doris_udf::DateTimeVal& ts_val);
static doris_udf::DateTimeVal str_to_date(doris_udf::FunctionContext* ctx,
diff --git a/be/src/vec/functions/function_timestamp.cpp b/be/src/vec/functions/function_timestamp.cpp
index 461b317bf6..63fb1d900c 100644
--- a/be/src/vec/functions/function_timestamp.cpp
+++ b/be/src/vec/functions/function_timestamp.cpp
@@ -569,6 +569,154 @@ public:
}
};
+template <template <typename> class Impl, typename DateType>
+class FunctionDateOrDateTimeToDate : public IFunction {
+public:
+ static constexpr auto name = Impl<DateType>::name;
+ static FunctionPtr create() {
+ return std::make_shared<FunctionDateOrDateTimeToDate<Impl, DateType>>();
+ }
+
+ String get_name() const override { return name; }
+
+ bool use_default_implementation_for_nulls() const override { return true; }
+
+ bool use_default_implementation_for_constants() const override { return true; }
+
+ size_t get_number_of_arguments() const override { return 1; }
+
+ bool is_variadic() const override { return true; }
+
+ // input DateTime and Date, return Date
+ // input DateTimeV2 and DateV2, return DateV2
+ DataTypePtr get_return_type_impl(const ColumnsWithTypeAndName& arguments) const override {
+ if constexpr (std::is_same_v<DateType, DataTypeDateTime> ||
+ std::is_same_v<DateType, DataTypeDate>) {
+ return make_nullable(std::make_shared<DataTypeDate>());
+ } else {
+ return make_nullable(std::make_shared<DataTypeDateV2>());
+ }
+ }
+
+ DataTypes get_variadic_argument_types_impl() const override {
+ if constexpr (std::is_same_v<DateType, DataTypeDate>) {
+ return {std::make_shared<DataTypeDate>()};
+ } else if constexpr (std::is_same_v<DateType, DataTypeDateTime>) {
+ return {std::make_shared<DataTypeDateTime>()};
+ } else if constexpr (std::is_same_v<DateType, DataTypeDateV2>) {
+ return {std::make_shared<DataTypeDateV2>()};
+ } else {
+ return {std::make_shared<DataTypeDateTimeV2>()};
+ }
+ }
+
+ Status execute_impl(FunctionContext* context, Block& block, const ColumnNumbers& arguments,
+ size_t result, size_t input_rows_count) override {
+ return Impl<DateType>::execute_impl(context, block, arguments, result, input_rows_count);
+ }
+};
+
+template <typename DateType>
+struct LastDayImpl {
+ static constexpr auto name = "last_day";
+
+ 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;
+ }
+ int day = get_last_month_day(ts_value.year(), ts_value.month());
+ ts_value.set_time(ts_value.year(), ts_value.month(), day, 0, 0, 0);
+ 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;
+ }
+ int day = get_last_month_day(ts_value.year(), ts_value.month());
+ ts_value.template set_time_unit<TimeUnit::DAY>(day);
+ 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;
+ }
+ int day = get_last_month_day(ts_value.year(), ts_value.month());
+ ts_value.template set_time_unit<TimeUnit::DAY>(day);
+ ts_value.set_time(ts_value.year(), ts_value.month(), day, 0, 0, 0, 0);
+ UInt64 cast_value = binary_cast<DateValueType, UInt64>(ts_value);
+ DataTypeDateTimeV2::cast_to_date_v2(cast_value, res_data[i]);
+ }
+ }
+ }
+
+ static int get_last_month_day(int year, int month) {
+ bool is_leap_year = doris::is_leap(year);
+ if (month == 2) {
+ return is_leap_year ? 29 : 28;
+ } else {
+ if (month == 1 || month == 3 || month == 5 || month == 7 || month == 8 || month == 10 ||
+ month == 12) {
+ return 31;
+ } else {
+ return 30;
+ }
+ }
+ }
+};
+
template <typename Impl>
class FunctionOtherTypesToDateType : public IFunction {
public:
@@ -619,6 +767,10 @@ void register_function_timestamp(SimpleFunctionFactory& factory) {
factory.register_function<
FunctionUnixTimestamp<UnixTimeStampDatetimeImpl<DataTypeDateTimeV2>>>();
factory.register_function<FunctionUnixTimestamp<UnixTimeStampStrImpl>>();
+ factory.register_function<FunctionDateOrDateTimeToDate<LastDayImpl, DataTypeDateTime>>();
+ factory.register_function<FunctionDateOrDateTimeToDate<LastDayImpl, DataTypeDate>>();
+ factory.register_function<FunctionDateOrDateTimeToDate<LastDayImpl, DataTypeDateV2>>();
+ factory.register_function<FunctionDateOrDateTimeToDate<LastDayImpl, DataTypeDateTimeV2>>();
}
} // namespace doris::vectorized
diff --git a/docs/en/docs/sql-manual/sql-functions/date-time-functions/last_day.md b/docs/en/docs/sql-manual/sql-functions/date-time-functions/last_day.md
new file mode 100644
index 0000000000..1e08eb35f9
--- /dev/null
+++ b/docs/en/docs/sql-manual/sql-functions/date-time-functions/last_day.md
@@ -0,0 +1,49 @@
+---
+{
+ "title": "last_day",
+ "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.
+-->
+
+## last_day
+### Description
+#### Syntax
+
+`DATE last_day(DATETIME date)`
+
+Return the last day of the month, the return day may be :
+'28'(February and not a leap year),
+'29'(February and a leap year),
+'30'(April, June, September, November),
+'31'(January, March, May, July, August, October, December)
+
+### example
+
+```
+mysql > select last_day('2000-02-03');
++-------------------+
+| last_day('2000-02-03 00:00:00') |
++-------------------+
+| 2000-02-29 |
++-------------------+
+```
+
+### keywords
+ LAST_DAY,DAYS
diff --git a/docs/sidebars.json b/docs/sidebars.json
index 918464e3b1..e76caf1d33 100644
--- a/docs/sidebars.json
+++ b/docs/sidebars.json
@@ -292,6 +292,7 @@
"sql-manual/sql-functions/date-time-functions/minute",
"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/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/last_day.md b/docs/zh-CN/docs/sql-manual/sql-functions/date-time-functions/last_day.md
new file mode 100644
index 0000000000..8f7a065ff4
--- /dev/null
+++ b/docs/zh-CN/docs/sql-manual/sql-functions/date-time-functions/last_day.md
@@ -0,0 +1,49 @@
+---
+{
+ "title": "last_day",
+ "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.
+-->
+
+## last_day
+### Description
+#### Syntax
+
+`DATE last_day(DATETIME date)`
+
+返回输入日期中月份的最后一天；所以返回的日期中，年和月不变，日可能是如下情况：
+'28'(非闰年的二月份),
+'29'(闰年的二月份),
+'30'(四月，六月，九月，十一月),
+'31'(一月，三月，五月，七月，八月，十月，十二月)
+
+### example
+
+```
+mysql > select last_day('2000-02-03');
++-------------------+
+| last_day('2000-02-03 00:00:00') |
++-------------------+
+| 2000-02-29 |
++-------------------+
+```
+
+### keywords
+ LAST_DAY,DAYS
diff --git a/gensrc/script/doris_builtins_functions.py b/gensrc/script/doris_builtins_functions.py
index 0976d0b284..68f1a4e48f 100755
--- a/gensrc/script/doris_builtins_functions.py
+++ b/gensrc/script/doris_builtins_functions.py
@@ -500,6 +500,18 @@ visible_functions = [
[['from_days'], 'DATE', ['INT'],
'_ZN5doris18TimestampFunctions9from_daysEPN9doris_udf15FunctionContextERKNS1_6IntValE',
'', '', 'vec', 'ALWAYS_NULLABLE'],
+ [['last_day'], 'DATE', ['DATETIME'],
+ '_ZN5doris18TimestampFunctions8last_dayEPN9doris_udf15FunctionContextERKNS1_11DateTimeValE',
+ '', '', 'vec', 'ALWAYS_NULLABLE'],
+ [['last_day'], 'DATE', ['DATE'],
+ '_ZN5doris18TimestampFunctions8last_dayEPN9doris_udf15FunctionContextERKNS1_11DateTimeValE',
+ '', '', 'vec', 'ALWAYS_NULLABLE'],
+ [['last_day'], 'DATEV2', ['DATETIMEV2'],
+ '_ZN5doris18TimestampFunctions8last_dayEPN9doris_udf15FunctionContextERKNS1_11DateTimeValE',
+ '', '', 'vec', 'ALWAYS_NULLABLE'],
+ [['last_day'], 'DATEV2', ['DATEV2'],
+ '_ZN5doris18TimestampFunctions8last_dayEPN9doris_udf15FunctionContextERKNS1_11DateTimeValE',
+ '', '', '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 4c8b24b8c7..b465af0658 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
@@ -529,3 +529,15 @@ true
-- !sql --
2019-08-01T13:21:02.111111
+-- !sql --
+\N \N \N \N
+2000-02-29 2000-02-29 2000-02-29 2000-02-29
+2022-01-31 2022-01-31 2022-01-31 2022-01-31
+2022-02-28 2022-02-28 2022-02-28 2022-02-28
+
+-- !sql --
+\N \N
+2000-02-29 2000-02-29
+2022-01-31 2022-01-31
+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 5250447e45..7ff926157f 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
@@ -429,4 +429,52 @@ suite("test_date_function") {
qt_sql """ select minutes_sub(test_time2,1) result from ${tableName}; """
//seconds_sub
qt_sql """ select seconds_sub(test_time2,1) result from ${tableName}; """
+
+ // test last_day for vec
+ 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');"""
+ qt_sql """
+ select last_day(birth), last_day(birth1),
+ last_day(birth2), last_day(birth3)
+ from ${tableName};
+ """
+ sql """ DROP TABLE IF EXISTS ${tableName}; """
+
+ // test last_day for not vec
+ sql """ SET enable_vectorized_engine = FALSE; """
+ sql """ DROP TABLE IF EXISTS ${tableName}; """
+ sql """
+ CREATE TABLE IF NOT EXISTS ${tableName} (
+ birth date,
+ birth1 datetime)
+ UNIQUE KEY(birth, birth1)
+ DISTRIBUTED BY HASH (birth) BUCKETS 1
+ PROPERTIES( "replication_allocation" = "tag.location.default: 1");
+ """
+ sql """
+ insert into ${tableName} values
+ ('2022-01-01', '2022-01-01 00:00:00'),
+ ('2000-02-01', '2000-02-01 00:00:00'),
+ ('2022-02-29', '2022-02-29 00:00:00'),
+ ('2022-02-28', '2022-02-28 23:59:59');"""
+ qt_sql """
+ select last_day(birth), last_day(birth1) 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