This is an automated email from the ASF dual-hosted git repository. kxiao pushed a commit to branch branch-2.0 in repository https://gitbox.apache.org/repos/asf/doris.git
commit 8b4a170742aa1710cdbe636c42fcc1735964fc7d Author: Mryange <[email protected]> AuthorDate: Thu Sep 7 19:03:49 2023 +0800 [feature](Datetime) add from_microsecond / from_millisecond function (#23902) --- .../function_date_or_datetime_computation.cpp | 6 + .../function_date_or_datetime_computation.h | 49 ++++++++ be/src/vec/functions/function_timestamp.cpp | 66 +++++++++++ .../date-time-functions/from_unixtime.md | 11 ++ .../date-time-functions/from-second.md | 81 +++++++++++++ .../date-time-functions/from_unixtime.md | 11 ++ .../date-time-functions/second-timestamp.md | 67 +++++++++++ .../apache/doris/analysis/FunctionCallExpr.java | 15 +++ .../doris/catalog/BuiltinScalarFunctions.java | 12 ++ .../functions/scalar/FromMicrosecond.java | 62 ++++++++++ .../functions/scalar/FromMillisecond.java | 62 ++++++++++ .../expressions/functions/scalar/FromSecond.java | 62 ++++++++++ .../functions/scalar/MicroSecondTimestamp.java | 62 ++++++++++ .../functions/scalar/MilliSecondTimestamp.java | 62 ++++++++++ .../functions/scalar/SecondTimestamp.java | 62 ++++++++++ gensrc/script/doris_builtins_functions.py | 10 +- .../test_from_millisecond_microsecond.out | 67 +++++++++++ .../test_from_millisecond_microsecond.groovy | 131 +++++++++++++++++++++ 18 files changed, 896 insertions(+), 2 deletions(-) diff --git a/be/src/vec/functions/function_date_or_datetime_computation.cpp b/be/src/vec/functions/function_date_or_datetime_computation.cpp index 8034d34c19..de3baad1ad 100644 --- a/be/src/vec/functions/function_date_or_datetime_computation.cpp +++ b/be/src/vec/functions/function_date_or_datetime_computation.cpp @@ -124,6 +124,9 @@ using FunctionCurrentTime = FunctionCurrentDateOrDateTime<CurrentTimeImpl<Curren using FunctionUtcTimeStamp = FunctionCurrentDateOrDateTime<UtcTimestampImpl>; using FunctionTimeToSec = FunctionCurrentDateOrDateTime<TimeToSecImpl>; using FunctionSecToTime = FunctionCurrentDateOrDateTime<SecToTimeImpl>; +using FunctionMicroSecToDateTime = TimestampToDateTime<MicroSec>; +using FunctionMilliSecToDateTime = TimestampToDateTime<MilliSec>; +using FunctionSecToDateTime = TimestampToDateTime<Sec>; /// @TEMPORARY: for be_exec_version=2 using FunctionToWeekTwoArgsOld = @@ -179,6 +182,9 @@ void register_function_date_time_computation(SimpleFunctionFactory& factory) { factory.register_function<FunctionUtcTimeStamp>(); factory.register_function<FunctionTimeToSec>(); factory.register_function<FunctionSecToTime>(); + factory.register_function<FunctionMicroSecToDateTime>(); + factory.register_function<FunctionMilliSecToDateTime>(); + factory.register_function<FunctionSecToDateTime>(); // alias factory.register_alias("days_add", "date_add"); 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 784af9eb32..68f1e794f7 100644 --- a/be/src/vec/functions/function_date_or_datetime_computation.h +++ b/be/src/vec/functions/function_date_or_datetime_computation.h @@ -1106,6 +1106,55 @@ struct SecToTimeImpl { return Status::OK(); } }; +struct MicroSec { + static constexpr auto name = "from_microsecond"; + static constexpr Int64 ratio = 1000000; +}; +struct MilliSec { + static constexpr auto name = "from_millisecond"; + static constexpr Int64 ratio = 1000; +}; +struct Sec { + static constexpr auto name = "from_second"; + static constexpr Int64 ratio = 1; +}; +template <typename Impl> +struct TimestampToDateTime : IFunction { + using ReturnType = DataTypeDateTimeV2; + static constexpr auto name = Impl::name; + static constexpr Int64 ratio_to_micro = (1000 * 1000) / Impl::ratio; + String get_name() const override { return name; } + + size_t get_number_of_arguments() const override { return 1; } + + DataTypePtr get_return_type_impl(const ColumnsWithTypeAndName& arguments) const override { + return make_nullable(std::make_shared<ReturnType>()); + } + + static FunctionPtr create() { return std::make_shared<TimestampToDateTime<Impl>>(); } + + Status execute_impl(FunctionContext* context, Block& block, const ColumnNumbers& arguments, + size_t result, size_t input_rows_count) override { + const auto& arg_col = block.get_by_position(arguments[0]).column; + const auto& column_data = assert_cast<const ColumnInt64&>(*arg_col); + auto res_col = ColumnUInt64::create(); + auto null_vector = ColumnVector<UInt8>::create(); + res_col->get_data().resize_fill(input_rows_count, 0); + null_vector->get_data().resize_fill(input_rows_count, false); + NullMap& null_map = null_vector->get_data(); + auto& res_data = res_col->get_data(); + const cctz::time_zone& time_zone = context->state()->timezone_obj(); + for (int i = 0; i < input_rows_count; ++i) { + Int64 value = column_data.get_element(i); + auto& dt = reinterpret_cast<DateV2Value<DateTimeV2ValueType>&>(res_data[i]); + null_map[i] = !dt.from_unixtime(value / Impl::ratio, time_zone); + dt.set_microsecond((value % Impl::ratio) * ratio_to_micro); + } + block.get_by_position(result).column = + ColumnNullable::create(std::move(res_col), std::move(null_vector)); + return Status::OK(); + } +}; struct UtcTimestampImpl { using ReturnType = DataTypeDateTime; diff --git a/be/src/vec/functions/function_timestamp.cpp b/be/src/vec/functions/function_timestamp.cpp index 1bca282f2e..3510365f46 100644 --- a/be/src/vec/functions/function_timestamp.cpp +++ b/be/src/vec/functions/function_timestamp.cpp @@ -782,6 +782,68 @@ public: } }; +struct MicroSec { + static constexpr auto name = "microsecond_timestamp"; + static constexpr Int64 ratio = 1000000; +}; +struct MilliSec { + static constexpr auto name = "millisecond_timestamp"; + static constexpr Int64 ratio = 1000; +}; +struct Sec { + static constexpr auto name = "second_timestamp"; + static constexpr Int64 ratio = 1; +}; +template <typename Impl> +class DateTimeToTimestamp : public IFunction { +public: + using ReturnType = Int64; + static constexpr Int64 ratio_to_micro = (1000 * 1000) / Impl::ratio; + static constexpr auto name = Impl::name; + static FunctionPtr create() { return std::make_shared<DateTimeToTimestamp<Impl>>(); } + + String get_name() const override { return name; } + + size_t get_number_of_arguments() const override { return 1; } + + DataTypePtr get_return_type_impl(const ColumnsWithTypeAndName& arguments) const override { + return make_nullable(std::make_shared<DataTypeInt64>()); + } + + Status execute_impl(FunctionContext* context, Block& block, const ColumnNumbers& arguments, + size_t result, size_t input_rows_count) override { + const auto& arg_col = block.get_by_position(arguments[0]).column; + const auto& column_data = assert_cast<const ColumnUInt64&>(*arg_col); + auto res_col = ColumnInt64::create(); + auto null_vector = ColumnVector<UInt8>::create(); + res_col->get_data().resize_fill(input_rows_count, 0); + null_vector->get_data().resize_fill(input_rows_count, false); + NullMap& null_map = null_vector->get_data(); + auto& res_data = res_col->get_data(); + const cctz::time_zone& time_zone = context->state()->timezone_obj(); + for (int i = 0; i < input_rows_count; i++) { + if (arg_col->is_null_at(i)) { + null_map[i] = true; + continue; + } + StringRef source = column_data.get_data_at(i); + const DateV2Value<DateTimeV2ValueType>& dt = + reinterpret_cast<const DateV2Value<DateTimeV2ValueType>&>(*source.data); + int64_t timestamp {0}; + if (!dt.unix_timestamp(×tamp, time_zone)) { + null_map[i] = true; + } else { + auto microsecond = dt.microsecond(); + timestamp = timestamp * Impl::ratio + microsecond / ratio_to_micro; + res_data[i] = timestamp; + } + } + block.get_by_position(result).column = + ColumnNullable::create(std::move(res_col), std::move(null_vector)); + return Status::OK(); + } +}; + template <template <typename> class Impl, typename DateType> class FunctionDateOrDateTimeToDate : public IFunction { public: @@ -1235,6 +1297,10 @@ void register_function_timestamp(SimpleFunctionFactory& factory) { factory.register_function<FunctionDateOrDateTimeToDate<MondayImpl, DataTypeDateTimeV2>>(); factory.register_function<FunctionDateOrDateTimeToDate<MondayImpl, DataTypeDate>>(); factory.register_function<FunctionDateOrDateTimeToDate<MondayImpl, DataTypeDateTime>>(); + + factory.register_function<DateTimeToTimestamp<MicroSec>>(); + factory.register_function<DateTimeToTimestamp<MilliSec>>(); + factory.register_function<DateTimeToTimestamp<Sec>>(); } } // namespace doris::vectorized diff --git a/docs/en/docs/sql-manual/sql-functions/date-time-functions/from_unixtime.md b/docs/en/docs/sql-manual/sql-functions/date-time-functions/from_unixtime.md index f34357d184..2719d13f95 100644 --- a/docs/en/docs/sql-manual/sql-functions/date-time-functions/from_unixtime.md +++ b/docs/en/docs/sql-manual/sql-functions/date-time-functions/from_unixtime.md @@ -63,6 +63,17 @@ mysql> select from_unixtime(1196440219, '%Y-%m-%d %H:%i:%s'); +--------------------------------------------------+ ``` +For timestamps that exceed the range, you can use the "from_second" function. +`DATETIME FROM_SECOND(BIGINT unix_timestamp)` +``` +mysql> select from_second(21474836470); ++--------------------------+ +| from_second(21474836470) | ++--------------------------+ +| 2650-07-06 16:21:10 | ++--------------------------+ +``` + ### keywords FROM_UNIXTIME,FROM,UNIXTIME diff --git a/docs/zh-CN/docs/sql-manual/sql-functions/date-time-functions/from-second.md b/docs/zh-CN/docs/sql-manual/sql-functions/date-time-functions/from-second.md new file mode 100644 index 0000000000..601cb4a707 --- /dev/null +++ b/docs/zh-CN/docs/sql-manual/sql-functions/date-time-functions/from-second.md @@ -0,0 +1,81 @@ +--- +{ + "title": "FROM_SECOND", + "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. +--> + +## from_second +### description +#### Syntax + +`DATETIME FROM_SECOND(BIGINT unix_timestamp)` +`DATETIME FROM_MILLISECOND(BIGINT unix_timestamp)` +`DATETIME FROM_MICROSECOND(BIGINT unix_timestamp)` + +将时间戳转化为对应的 DATETIME + +传入的是整型，返回的是DATETIME类型 + + +### example + +``` +mysql> select from_microsecond(0); ++----------------------------+ +| from_microsecond(0) | ++----------------------------+ +| 1970-01-01 08:00:00.000000 | ++----------------------------+ + +mysql> select from_microsecond(12345678); ++----------------------------+ +| from_microsecond(12345678) | ++----------------------------+ +| 1970-01-01 08:00:12.345678 | ++----------------------------+ + +mysql> select from_millisecond(0); ++-------------------------+ +| from_millisecond(0) | ++-------------------------+ +| 1970-01-01 08:00:00.000 | ++-------------------------+ + +mysql> select from_millisecond(12345678); ++----------------------------+ +| from_millisecond(12345678) | ++----------------------------+ +| 1970-01-01 11:25:45.678 | ++----------------------------+ + +mysql> select from_second(21474836470); ++--------------------------+ +| from_second(21474836470) | ++--------------------------+ +| 2650-07-06 16:21:10 | ++--------------------------+ +``` + +### keywords + + FROM_SECOND diff --git a/docs/zh-CN/docs/sql-manual/sql-functions/date-time-functions/from_unixtime.md b/docs/zh-CN/docs/sql-manual/sql-functions/date-time-functions/from_unixtime.md index 105852b234..b16465c478 100644 --- a/docs/zh-CN/docs/sql-manual/sql-functions/date-time-functions/from_unixtime.md +++ b/docs/zh-CN/docs/sql-manual/sql-functions/date-time-functions/from_unixtime.md @@ -75,6 +75,17 @@ mysql> select from_unixtime(1196440219, '%Y-%m-%d %H:%i:%s'); +--------------------------------------------------+ ``` +对于超过范围的时间戳，可以采用from_second函数 +`DATETIME FROM_SECOND(BIGINT unix_timestamp)` +``` +mysql> select from_second(21474836470); ++--------------------------+ +| from_second(21474836470) | ++--------------------------+ +| 2650-07-06 16:21:10 | ++--------------------------+ +``` + ### keywords FROM_UNIXTIME,FROM,UNIXTIME diff --git a/docs/zh-CN/docs/sql-manual/sql-functions/date-time-functions/second-timestamp.md b/docs/zh-CN/docs/sql-manual/sql-functions/date-time-functions/second-timestamp.md new file mode 100644 index 0000000000..11b4c6c8f3 --- /dev/null +++ b/docs/zh-CN/docs/sql-manual/sql-functions/date-time-functions/second-timestamp.md @@ -0,0 +1,67 @@ +--- +{ + "title": "SECOND_TIMESTAMP", + "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. +--> + +## second_timestamp +### description +#### Syntax + +`BIGINT SECOND_TIMESTAMP(DATETIME date)` +`BIGINT MILLISECOND_TIMESTAMP(DATETIME date)` +`BIGINT MICROSECOND_TIMESTAMP(DATETIME date)` + +将DATETIME类型转换成对应的时间戳 + +传入的是DATETIME类型，返回的是整型 + + +### example + +``` +mysql> select from_millisecond(89417891234789),millisecond_timestamp(from_millisecond(89417891234789)); ++----------------------------------+---------------------------------------------------------+ +| from_millisecond(89417891234789) | millisecond_timestamp(from_millisecond(89417891234789)) | ++----------------------------------+---------------------------------------------------------+ +| 4803-07-17 15:07:14.789 | 89417891234789 | ++----------------------------------+---------------------------------------------------------+ + +mysql> select from_second(89417891234),second_timestamp(from_second(89417891234)); ++--------------------------+--------------------------------------------+ +| from_second(89417891234) | second_timestamp(from_second(89417891234)) | ++--------------------------+--------------------------------------------+ +| 4803-07-17 15:07:14 | 89417891234 | ++--------------------------+--------------------------------------------+ + +mysql> select from_microsecond(89417891234),microsecond_timestamp(from_microsecond(89417891234)); ++-------------------------------+------------------------------------------------------+ +| from_microsecond(89417891234) | microsecond_timestamp(from_microsecond(89417891234)) | ++-------------------------------+------------------------------------------------------+ +| 1970-01-02 08:50:17.891234 | 89417891234 | ++-------------------------------+------------------------------------------------------+ +``` + +### keywords + + SECOND_TIMESTAMP diff --git a/fe/fe-core/src/main/java/org/apache/doris/analysis/FunctionCallExpr.java b/fe/fe-core/src/main/java/org/apache/doris/analysis/FunctionCallExpr.java index 0ffcd5ab0a..86ba7b243d 100644 --- a/fe/fe-core/src/main/java/org/apache/doris/analysis/FunctionCallExpr.java +++ b/fe/fe-core/src/main/java/org/apache/doris/analysis/FunctionCallExpr.java @@ -1638,6 +1638,21 @@ public class FunctionCallExpr extends Expr { } } + if (fn.getFunctionName().getFunction().equals("from_microsecond")) { + Type ret = ScalarType.createDatetimeV2Type(6); + fn.setReturnType(ret); + } + + if (fn.getFunctionName().getFunction().equals("from_millisecond")) { + Type ret = ScalarType.createDatetimeV2Type(3); + fn.setReturnType(ret); + } + + if (fn.getFunctionName().getFunction().equals("from_second")) { + Type ret = ScalarType.createDatetimeV2Type(0); + fn.setReturnType(ret); + } + if (fnName.getFunction().equalsIgnoreCase("map")) { if ((children.size() & 1) == 1) { throw new AnalysisException("map can't be odd parameters, need even parameters: " diff --git a/fe/fe-core/src/main/java/org/apache/doris/catalog/BuiltinScalarFunctions.java b/fe/fe-core/src/main/java/org/apache/doris/catalog/BuiltinScalarFunctions.java index 610f6fe830..5fde24724a 100644 --- a/fe/fe-core/src/main/java/org/apache/doris/catalog/BuiltinScalarFunctions.java +++ b/fe/fe-core/src/main/java/org/apache/doris/catalog/BuiltinScalarFunctions.java @@ -139,6 +139,9 @@ import org.apache.doris.nereids.trees.expressions.functions.scalar.Fmod; import org.apache.doris.nereids.trees.expressions.functions.scalar.Fpow; import org.apache.doris.nereids.trees.expressions.functions.scalar.FromBase64; import org.apache.doris.nereids.trees.expressions.functions.scalar.FromDays; +import org.apache.doris.nereids.trees.expressions.functions.scalar.FromMicrosecond; +import org.apache.doris.nereids.trees.expressions.functions.scalar.FromMillisecond; +import org.apache.doris.nereids.trees.expressions.functions.scalar.FromSecond; import org.apache.doris.nereids.trees.expressions.functions.scalar.FromUnixtime; import org.apache.doris.nereids.trees.expressions.functions.scalar.GetJsonBigInt; import org.apache.doris.nereids.trees.expressions.functions.scalar.GetJsonDouble; @@ -203,7 +206,9 @@ import org.apache.doris.nereids.trees.expressions.functions.scalar.MaskFirstN; import org.apache.doris.nereids.trees.expressions.functions.scalar.MaskLastN; import org.apache.doris.nereids.trees.expressions.functions.scalar.Md5; import org.apache.doris.nereids.trees.expressions.functions.scalar.Md5Sum; +import org.apache.doris.nereids.trees.expressions.functions.scalar.MicroSecondTimestamp; import org.apache.doris.nereids.trees.expressions.functions.scalar.Microsecond; +import org.apache.doris.nereids.trees.expressions.functions.scalar.MilliSecondTimestamp; import org.apache.doris.nereids.trees.expressions.functions.scalar.Minute; import org.apache.doris.nereids.trees.expressions.functions.scalar.MinuteCeil; import org.apache.doris.nereids.trees.expressions.functions.scalar.MinuteFloor; @@ -256,6 +261,7 @@ import org.apache.doris.nereids.trees.expressions.functions.scalar.SecToTime; import org.apache.doris.nereids.trees.expressions.functions.scalar.Second; import org.apache.doris.nereids.trees.expressions.functions.scalar.SecondCeil; import org.apache.doris.nereids.trees.expressions.functions.scalar.SecondFloor; +import org.apache.doris.nereids.trees.expressions.functions.scalar.SecondTimestamp; import org.apache.doris.nereids.trees.expressions.functions.scalar.SecondsAdd; import org.apache.doris.nereids.trees.expressions.functions.scalar.SecondsDiff; import org.apache.doris.nereids.trees.expressions.functions.scalar.SecondsSub; @@ -598,6 +604,12 @@ public class BuiltinScalarFunctions implements FunctionHelper { scalar(SecondsDiff.class, "seconds_diff"), scalar(SecondsSub.class, "seconds_sub"), scalar(SecToTime.class, "sec_to_time"), + scalar(FromMicrosecond.class, "from_microsecond"), + scalar(FromMillisecond.class, "from_millisecond"), + scalar(FromSecond.class, "from_second"), + scalar(SecondTimestamp.class, "second_timestamp"), + scalar(MilliSecondTimestamp.class, "millisecond_timestamp"), + scalar(MicroSecondTimestamp.class, "microsecond_timestamp"), scalar(Sign.class, "sign"), scalar(Sin.class, "sin"), scalar(Size.class, "size"), diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/FromMicrosecond.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/FromMicrosecond.java new file mode 100644 index 0000000000..32e38858da --- /dev/null +++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/FromMicrosecond.java @@ -0,0 +1,62 @@ +// 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. + +package org.apache.doris.nereids.trees.expressions.functions.scalar; + +import org.apache.doris.catalog.FunctionSignature; +import org.apache.doris.nereids.trees.expressions.Expression; +import org.apache.doris.nereids.trees.expressions.functions.ExplicitlyCastableSignature; +import org.apache.doris.nereids.trees.expressions.functions.PropagateNullableOnDateLikeV2Args; +import org.apache.doris.nereids.trees.expressions.shape.BinaryExpression; +import org.apache.doris.nereids.trees.expressions.visitor.ExpressionVisitor; +import org.apache.doris.nereids.types.BigIntType; +import org.apache.doris.nereids.types.DateTimeV2Type; + +import com.google.common.base.Preconditions; +import com.google.common.collect.ImmutableList; + +import java.util.List; + +/** + * ScalarFunction 'from_microsecond'. + */ +public class FromMicrosecond extends ScalarFunction + implements BinaryExpression, ExplicitlyCastableSignature, PropagateNullableOnDateLikeV2Args { + + private static final List<FunctionSignature> SIGNATURES = ImmutableList.of( + FunctionSignature.ret(DateTimeV2Type.MAX).args(BigIntType.INSTANCE)); + + public FromMicrosecond(Expression arg0) { + super("from_microsecond", arg0); + } + + @Override + public FromMicrosecond withChildren(List<Expression> children) { + Preconditions.checkArgument(children.size() == 1); + return new FromMicrosecond(children.get(0)); + } + + @Override + public List<FunctionSignature> getSignatures() { + return SIGNATURES; + } + + @Override + public <R, C> R accept(ExpressionVisitor<R, C> visitor, C context) { + return visitor.visitScalarFunction(this, context); + } +} diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/FromMillisecond.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/FromMillisecond.java new file mode 100644 index 0000000000..658f7444d8 --- /dev/null +++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/FromMillisecond.java @@ -0,0 +1,62 @@ +// 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. + +package org.apache.doris.nereids.trees.expressions.functions.scalar; + +import org.apache.doris.catalog.FunctionSignature; +import org.apache.doris.nereids.trees.expressions.Expression; +import org.apache.doris.nereids.trees.expressions.functions.ExplicitlyCastableSignature; +import org.apache.doris.nereids.trees.expressions.functions.PropagateNullableOnDateLikeV2Args; +import org.apache.doris.nereids.trees.expressions.shape.BinaryExpression; +import org.apache.doris.nereids.trees.expressions.visitor.ExpressionVisitor; +import org.apache.doris.nereids.types.BigIntType; +import org.apache.doris.nereids.types.DateTimeV2Type; + +import com.google.common.base.Preconditions; +import com.google.common.collect.ImmutableList; + +import java.util.List; + +/** + * ScalarFunction 'from_millisecond'. + */ +public class FromMillisecond extends ScalarFunction + implements BinaryExpression, ExplicitlyCastableSignature, PropagateNullableOnDateLikeV2Args { + public static final DateTimeV2Type MillisecondDateTimeV2 = DateTimeV2Type.of(3); + private static final List<FunctionSignature> SIGNATURES = ImmutableList.of( + FunctionSignature.ret(MillisecondDateTimeV2).args(BigIntType.INSTANCE)); + + public FromMillisecond(Expression arg0) { + super("from_millisecond", arg0); + } + + @Override + public FromMillisecond withChildren(List<Expression> children) { + Preconditions.checkArgument(children.size() == 1); + return new FromMillisecond(children.get(0)); + } + + @Override + public List<FunctionSignature> getSignatures() { + return SIGNATURES; + } + + @Override + public <R, C> R accept(ExpressionVisitor<R, C> visitor, C context) { + return visitor.visitScalarFunction(this, context); + } +} diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/FromSecond.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/FromSecond.java new file mode 100644 index 0000000000..46c550fe78 --- /dev/null +++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/FromSecond.java @@ -0,0 +1,62 @@ +// 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. + +package org.apache.doris.nereids.trees.expressions.functions.scalar; + +import org.apache.doris.catalog.FunctionSignature; +import org.apache.doris.nereids.trees.expressions.Expression; +import org.apache.doris.nereids.trees.expressions.functions.ExplicitlyCastableSignature; +import org.apache.doris.nereids.trees.expressions.functions.PropagateNullableOnDateLikeV2Args; +import org.apache.doris.nereids.trees.expressions.shape.BinaryExpression; +import org.apache.doris.nereids.trees.expressions.visitor.ExpressionVisitor; +import org.apache.doris.nereids.types.BigIntType; +import org.apache.doris.nereids.types.DateTimeV2Type; + +import com.google.common.base.Preconditions; +import com.google.common.collect.ImmutableList; + +import java.util.List; + +/** + * ScalarFunction 'from_second'. + */ +public class FromSecond extends ScalarFunction + implements BinaryExpression, ExplicitlyCastableSignature, PropagateNullableOnDateLikeV2Args { + + private static final List<FunctionSignature> SIGNATURES = ImmutableList.of( + FunctionSignature.ret(DateTimeV2Type.SYSTEM_DEFAULT).args(BigIntType.INSTANCE)); + + public FromSecond(Expression arg0) { + super("from_second", arg0); + } + + @Override + public FromSecond withChildren(List<Expression> children) { + Preconditions.checkArgument(children.size() == 1); + return new FromSecond(children.get(0)); + } + + @Override + public List<FunctionSignature> getSignatures() { + return SIGNATURES; + } + + @Override + public <R, C> R accept(ExpressionVisitor<R, C> visitor, C context) { + return visitor.visitScalarFunction(this, context); + } +} diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/MicroSecondTimestamp.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/MicroSecondTimestamp.java new file mode 100644 index 0000000000..48006ec481 --- /dev/null +++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/MicroSecondTimestamp.java @@ -0,0 +1,62 @@ +// 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. + +package org.apache.doris.nereids.trees.expressions.functions.scalar; + +import org.apache.doris.catalog.FunctionSignature; +import org.apache.doris.nereids.trees.expressions.Expression; +import org.apache.doris.nereids.trees.expressions.functions.ExplicitlyCastableSignature; +import org.apache.doris.nereids.trees.expressions.functions.PropagateNullableOnDateLikeV2Args; +import org.apache.doris.nereids.trees.expressions.shape.BinaryExpression; +import org.apache.doris.nereids.trees.expressions.visitor.ExpressionVisitor; +import org.apache.doris.nereids.types.BigIntType; +import org.apache.doris.nereids.types.DateTimeV2Type; + +import com.google.common.base.Preconditions; +import com.google.common.collect.ImmutableList; + +import java.util.List; + +/** + * ScalarFunction 'microsecond_timestamp'. + */ +public class MicroSecondTimestamp extends ScalarFunction + implements BinaryExpression, ExplicitlyCastableSignature, PropagateNullableOnDateLikeV2Args { + + private static final List<FunctionSignature> SIGNATURES = ImmutableList.of( + FunctionSignature.ret(BigIntType.INSTANCE).args(DateTimeV2Type.SYSTEM_DEFAULT)); + + public MicroSecondTimestamp(Expression arg0) { + super("microsecond_timestamp", arg0); + } + + @Override + public MicroSecondTimestamp withChildren(List<Expression> children) { + Preconditions.checkArgument(children.size() == 1); + return new MicroSecondTimestamp(children.get(0)); + } + + @Override + public List<FunctionSignature> getSignatures() { + return SIGNATURES; + } + + @Override + public <R, C> R accept(ExpressionVisitor<R, C> visitor, C context) { + return visitor.visitScalarFunction(this, context); + } +} diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/MilliSecondTimestamp.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/MilliSecondTimestamp.java new file mode 100644 index 0000000000..16754c5331 --- /dev/null +++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/MilliSecondTimestamp.java @@ -0,0 +1,62 @@ +// 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. + +package org.apache.doris.nereids.trees.expressions.functions.scalar; + +import org.apache.doris.catalog.FunctionSignature; +import org.apache.doris.nereids.trees.expressions.Expression; +import org.apache.doris.nereids.trees.expressions.functions.ExplicitlyCastableSignature; +import org.apache.doris.nereids.trees.expressions.functions.PropagateNullableOnDateLikeV2Args; +import org.apache.doris.nereids.trees.expressions.shape.BinaryExpression; +import org.apache.doris.nereids.trees.expressions.visitor.ExpressionVisitor; +import org.apache.doris.nereids.types.BigIntType; +import org.apache.doris.nereids.types.DateTimeV2Type; + +import com.google.common.base.Preconditions; +import com.google.common.collect.ImmutableList; + +import java.util.List; + +/** + * ScalarFunction 'millisecond_timestamp'. + */ +public class MilliSecondTimestamp extends ScalarFunction + implements BinaryExpression, ExplicitlyCastableSignature, PropagateNullableOnDateLikeV2Args { + + private static final List<FunctionSignature> SIGNATURES = ImmutableList.of( + FunctionSignature.ret(BigIntType.INSTANCE).args(DateTimeV2Type.SYSTEM_DEFAULT)); + + public MilliSecondTimestamp(Expression arg0) { + super("millisecond_timestamp", arg0); + } + + @Override + public MilliSecondTimestamp withChildren(List<Expression> children) { + Preconditions.checkArgument(children.size() == 1); + return new MilliSecondTimestamp(children.get(0)); + } + + @Override + public List<FunctionSignature> getSignatures() { + return SIGNATURES; + } + + @Override + public <R, C> R accept(ExpressionVisitor<R, C> visitor, C context) { + return visitor.visitScalarFunction(this, context); + } +} diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/SecondTimestamp.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/SecondTimestamp.java new file mode 100644 index 0000000000..fdb76506d1 --- /dev/null +++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/SecondTimestamp.java @@ -0,0 +1,62 @@ +// 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. + +package org.apache.doris.nereids.trees.expressions.functions.scalar; + +import org.apache.doris.catalog.FunctionSignature; +import org.apache.doris.nereids.trees.expressions.Expression; +import org.apache.doris.nereids.trees.expressions.functions.ExplicitlyCastableSignature; +import org.apache.doris.nereids.trees.expressions.functions.PropagateNullableOnDateLikeV2Args; +import org.apache.doris.nereids.trees.expressions.shape.BinaryExpression; +import org.apache.doris.nereids.trees.expressions.visitor.ExpressionVisitor; +import org.apache.doris.nereids.types.BigIntType; +import org.apache.doris.nereids.types.DateTimeV2Type; + +import com.google.common.base.Preconditions; +import com.google.common.collect.ImmutableList; + +import java.util.List; + +/** + * ScalarFunction 'second_timestamp'. + */ +public class SecondTimestamp extends ScalarFunction + implements BinaryExpression, ExplicitlyCastableSignature, PropagateNullableOnDateLikeV2Args { + + private static final List<FunctionSignature> SIGNATURES = ImmutableList.of( + FunctionSignature.ret(BigIntType.INSTANCE).args(DateTimeV2Type.SYSTEM_DEFAULT)); + + public SecondTimestamp(Expression arg0) { + super("second_timestamp", arg0); + } + + @Override + public SecondTimestamp withChildren(List<Expression> children) { + Preconditions.checkArgument(children.size() == 1); + return new SecondTimestamp(children.get(0)); + } + + @Override + public List<FunctionSignature> getSignatures() { + return SIGNATURES; + } + + @Override + public <R, C> R accept(ExpressionVisitor<R, C> visitor, C context) { + return visitor.visitScalarFunction(this, context); + } +} diff --git a/gensrc/script/doris_builtins_functions.py b/gensrc/script/doris_builtins_functions.py index 31d067877e..e8fd623584 100644 --- a/gensrc/script/doris_builtins_functions.py +++ b/gensrc/script/doris_builtins_functions.py @@ -840,8 +840,14 @@ visible_functions = { [['from_unixtime'], 'VARCHAR', ['INT'], 'ALWAYS_NULLABLE'], [['from_unixtime'], 'VARCHAR', ['INT', 'VARCHAR'], 'ALWAYS_NULLABLE'], [['from_unixtime'], 'VARCHAR', ['INT', 'STRING'], 'ALWAYS_NULLABLE'], - [['now', 'current_timestamp', 'localtime', 'localtimestamp'], 'DATETIME', [], 'ALWAYS_NOT_NULLABLE'], - [['now', 'current_timestamp', 'localtime', 'localtimestamp'], 'DATETIMEV2', ['INT'], 'ALWAYS_NOT_NULLABLE'], + [['from_microsecond'], 'DATETIMEV2', ['BIGINT'], 'ALWAYS_NULLABLE'], + [['from_millisecond'], 'DATETIMEV2', ['BIGINT'], 'ALWAYS_NULLABLE'], + [['from_second'], 'DATETIMEV2', ['BIGINT'], 'ALWAYS_NULLABLE'], + [['second_timestamp'], 'BIGINT', ['DATETIMEV2'], 'ALWAYS_NULLABLE'], + [['millisecond_timestamp'], 'BIGINT', ['DATETIMEV2'], 'ALWAYS_NULLABLE'], + [['microsecond_timestamp'], 'BIGINT', ['DATETIMEV2'], 'ALWAYS_NULLABLE'], + [['now', 'current_timestamp', 'localtime', 'localtimestamp'], 'DATETIME', [], 'DEPEND_ON_ARGUMENT'], + [['now', 'current_timestamp', 'localtime', 'localtimestamp'], 'DATETIMEV2', ['INT'], 'DEPEND_ON_ARGUMENT'], [['curtime', 'current_time'], 'TIME', [], 'ALWAYS_NOT_NULLABLE'], [['curdate', 'current_date'], 'DATE', [], 'ALWAYS_NOT_NULLABLE'], [['utc_timestamp'], 'DATETIME', [], 'ALWAYS_NOT_NULLABLE'], diff --git a/regression-test/data/correctness/test_from_millisecond_microsecond.out b/regression-test/data/correctness/test_from_millisecond_microsecond.out new file mode 100644 index 0000000000..50861a9400 --- /dev/null +++ b/regression-test/data/correctness/test_from_millisecond_microsecond.out @@ -0,0 +1,67 @@ +-- This file is automatically generated. You should know what you did if you want to edit this +-- !select1 -- +2030-11-02T08:35:14.514 2030-11-02T08:35:14.514 +4803-07-17T15:07:14.789 \N +2009-02-28T18:44:56.941 2009-02-28T18:44:56.941 +\N \N + +-- !select2 -- +1970-01-23T13:16:50.114514 1970-01-23T13:16:50.114514 +1972-11-01T06:18:11.234789 1972-11-01T06:18:11.234789 +1970-01-15T15:16:57.896941 1970-01-15T15:16:57.896941 +\N \N + +-- !select3 -- +2038-01-19 11:14:07 2038-01-19T11:14:07 \N 2038-01-19T11:14:08 \N 2650-07-06T16:21:10 + +-- !select4 -- +1919810114514 1919810114514 +89417891234789 488885820389 +1235817896941 1235817896941 +\N \N \N + +-- !select5 -- +1919810114514 2030-11-02T08:35:14.514 1919810114514 +89417891234789 4803-07-17T15:07:14.789 89417891234789 +1235817896941 2009-02-28T18:44:56.941 1235817896941 +\N \N \N + +-- !select6 -- +1919810114514 1970-01-23T13:16:50.114514 1919810114514 +89417891234789 1972-11-01T06:18:11.234789 89417891234789 +1235817896941 1970-01-15T15:16:57.896941 1235817896941 +\N \N \N + +-- !select7 -- +2030-11-02T08:35:14.514 +4803-07-17T15:07:14.789 +2009-02-28T18:44:56.941 +\N + +-- !select8 -- +1970-01-23T13:16:50.114514 +1972-11-01T06:18:11.234789 +1970-01-15T15:16:57.896941 +\N + +-- !select9 -- +2038-01-19 11:14:07 2038-01-19T11:14:07 \N 2038-01-19T11:14:08 \N 2650-07-06T16:21:10 + +-- !select10 -- +1919810114514 1919810114514 +89417891234789 488885820389 +1235817896941 1235817896941 +\N \N \N + +-- !select11 -- +1919810114514 2030-11-02T08:35:14.514 1919810114514 +89417891234789 4803-07-17T15:07:14.789 89417891234789 +1235817896941 2009-02-28T18:44:56.941 1235817896941 +\N \N \N + +-- !select12 -- +1919810114514 1970-01-23T13:16:50.114514 1919810114514 +89417891234789 1972-11-01T06:18:11.234789 89417891234789 +1235817896941 1970-01-15T15:16:57.896941 1235817896941 +\N \N \N + diff --git a/regression-test/suites/correctness/test_from_millisecond_microsecond.groovy b/regression-test/suites/correctness/test_from_millisecond_microsecond.groovy new file mode 100644 index 0000000000..15335cfd68 --- /dev/null +++ b/regression-test/suites/correctness/test_from_millisecond_microsecond.groovy @@ -0,0 +1,131 @@ +// 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_from_millisecond_microsecond") { + sql """ DROP TABLE IF EXISTS millimicro """ + sql """ + CREATE TABLE IF NOT EXISTS millimicro ( + `id` INT(11) NULL COMMENT "" , + `t` BigINT NULL COMMENT "" + ) ENGINE=OLAP + DUPLICATE KEY(`id`) + DISTRIBUTED BY HASH(`id`) BUCKETS 1 + PROPERTIES ( + "replication_allocation" = "tag.location.default: 1", + "storage_format" = "V2" + ); + """ + + sql """ + insert into millimicro values(1,1919810114514); + """ + sql """ + insert into millimicro values(2,89417891234789); + """ + sql """ + insert into millimicro values(3,1235817896941); + """ + + sql """ + insert into millimicro values(4,NULL); + """ + sql """ + set enable_nereids_planner=false + """ + + qt_select1 """ + select + from_millisecond(t) as t1 , + microseconds_add(cast(from_unixtime(t/1000) as datetime(3)), cast((t % 1000) * 1000 as int)) as t2 + from millimicro order by id; + """ + + qt_select2 """ + select + from_microsecond(t) as t1 , + microseconds_add(cast(from_unixtime(t/1000000) as datetime(6)), cast((t % 1000000) as int)) as t2 + from millimicro order by id; + """ + + qt_select3 """ + select + FROM_UNIXTIME(2147483647),from_second(2147483647), + FROM_UNIXTIME(2147483647 + 1),from_second(2147483647 + 1), + FROM_UNIXTIME(21474836470),from_second(21474836470); + """ + + qt_select4 """ + select + t, + from_second(t), + second_timestamp(from_second(t)) + from millimicro order by id; + """ + qt_select5 """ + select + t, + from_millisecond(t), + millisecond_timestamp(from_millisecond(t)) + from millimicro order by id; + """ + qt_select6 """ + select + t, + from_microsecond(t), + microsecond_timestamp(from_microsecond(t)) + from millimicro order by id; + """ + sql """ + set enable_nereids_planner=true,enable_fold_constant_by_be = false,forbid_unknown_col_stats = false + """ + + qt_select7 """ + select from_millisecond(t) as t1 from millimicro order by id; + """ + qt_select8 """ + select from_microsecond(t) as t1 from millimicro order by id; + """ + + qt_select9 """ + select + FROM_UNIXTIME(2147483647),from_second(2147483647), + FROM_UNIXTIME(2147483647 + 1),from_second(2147483647 + 1), + FROM_UNIXTIME(21474836470),from_second(21474836470); + """ + + qt_select10 """ + select + t, + from_second(t), + second_timestamp(from_second(t)) + from millimicro order by id; + """ + qt_select11 """ + select + t, + from_millisecond(t), + millisecond_timestamp(from_millisecond(t)) + from millimicro order by id; + """ + qt_select12 """ + select + t, + from_microsecond(t), + microsecond_timestamp(from_microsecond(t)) + from millimicro order by id; + """ +} \ No newline at end of file --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
