You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@doris.apache.org by ga...@apache.org on 2023/06/20 02:33:01 UTC
[doris] branch master updated: [Function] Support date function: microsecond() (#20044)
This is an automated email from the ASF dual-hosted git repository.
gabriellee 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 824bc02603 [Function] Support date function: microsecond() (#20044)
824bc02603 is described below
commit 824bc02603832e89b62d8633a9b9b766cce6f529
Author: zzzzzzzs <14...@qq.com>
AuthorDate: Tue Jun 20 10:32:54 2023 +0800
[Function] Support date function: microsecond() (#20044)
---
be/src/vec/functions/date_time_transforms.h | 1 +
be/src/vec/functions/to_time_function.cpp | 3 +
be/src/vec/runtime/vdatetime_value.cpp | 1 +
.../sql-functions/date-time-functions/extract.md | 15 ++---
.../{extract.md => microsecond.md} | 35 +++++------
.../sql-functions/date-time-functions/extract.md | 15 ++---
.../{extract.md => microsecond.md} | 36 +++++------
.../doris/catalog/BuiltinScalarFunctions.java | 2 +
.../executable/DateTimeExtractAndTransform.java | 12 +++-
.../expressions/functions/scalar/Microsecond.java | 71 ++++++++++++++++++++++
.../trees/expressions/literal/DateTimeLiteral.java | 4 ++
.../expressions/visitor/ScalarFunctionVisitor.java | 5 ++
gensrc/script/doris_builtins_functions.py | 5 +-
gensrc/thrift/Opcodes.thrift | 1 +
.../datetime_functions/test_date_function.out | 6 ++
.../datetime_functions/test_date_function.out | 6 ++
.../datetime_functions/test_date_function.groovy | 15 +++++
.../datetime_functions/test_date_function.groovy | 15 +++++
18 files changed, 190 insertions(+), 58 deletions(-)
diff --git a/be/src/vec/functions/date_time_transforms.h b/be/src/vec/functions/date_time_transforms.h
index 94ee40221c..d0d86ee42f 100644
--- a/be/src/vec/functions/date_time_transforms.h
+++ b/be/src/vec/functions/date_time_transforms.h
@@ -63,6 +63,7 @@ TO_TIME_FUNCTION(ToDayImpl, day);
TO_TIME_FUNCTION(ToHourImpl, hour);
TO_TIME_FUNCTION(ToMinuteImpl, minute);
TO_TIME_FUNCTION(ToSecondImpl, second);
+TO_TIME_FUNCTION(ToMicroSecondImpl, microsecond);
TIME_FUNCTION_IMPL(WeekOfYearImpl, weekofyear, week(mysql_week_mode(3)));
TIME_FUNCTION_IMPL(DayOfYearImpl, dayofyear, day_of_year());
diff --git a/be/src/vec/functions/to_time_function.cpp b/be/src/vec/functions/to_time_function.cpp
index b4b4a12f13..e3676a4aea 100644
--- a/be/src/vec/functions/to_time_function.cpp
+++ b/be/src/vec/functions/to_time_function.cpp
@@ -69,6 +69,8 @@ using FunctionDateTimeV2Minute =
FunctionDateOrDateTimeToSomething<DataTypeInt8, ToMinuteImpl<UInt64>>;
using FunctionDateTimeV2Second =
FunctionDateOrDateTimeToSomething<DataTypeInt8, ToSecondImpl<UInt64>>;
+using FunctionDateTimeV2MicroSecond =
+ FunctionDateOrDateTimeToSomething<DataTypeInt32, ToMicroSecondImpl<UInt64>>;
using FunctionDateTimeV2ToDays =
FunctionDateOrDateTimeToSomething<DataTypeInt32, ToDaysImpl<UInt64>>;
using FunctionDateTimeV2ToDate =
@@ -140,6 +142,7 @@ void register_function_to_time_function(SimpleFunctionFactory& factory) {
factory.register_function<FunctionToDaysV2>();
factory.register_function<FunctionToDateV2>();
factory.register_function<FunctionDateV2>();
+ factory.register_function<FunctionDateTimeV2MicroSecond>();
factory.register_function<FunctionDateTimeV2Second>();
factory.register_function<FunctionDateTimeV2Minute>();
factory.register_function<FunctionDateTimeV2Hour>();
diff --git a/be/src/vec/runtime/vdatetime_value.cpp b/be/src/vec/runtime/vdatetime_value.cpp
index af3d6d8223..f0ef48f1f3 100644
--- a/be/src/vec/runtime/vdatetime_value.cpp
+++ b/be/src/vec/runtime/vdatetime_value.cpp
@@ -3495,6 +3495,7 @@ template bool DateV2Value<DateV2ValueType>::datetime_trunc<TimeUnit::YEAR>();
template bool DateV2Value<DateV2ValueType>::datetime_trunc<TimeUnit::QUARTER>();
template bool DateV2Value<DateV2ValueType>::datetime_trunc<TimeUnit::WEEK>();
+template bool DateV2Value<DateTimeV2ValueType>::datetime_trunc<TimeUnit::MICROSECOND>();
template bool DateV2Value<DateTimeV2ValueType>::datetime_trunc<TimeUnit::SECOND>();
template bool DateV2Value<DateTimeV2ValueType>::datetime_trunc<TimeUnit::MINUTE>();
template bool DateV2Value<DateTimeV2ValueType>::datetime_trunc<TimeUnit::HOUR>();
diff --git a/docs/en/docs/sql-manual/sql-functions/date-time-functions/extract.md b/docs/en/docs/sql-manual/sql-functions/date-time-functions/extract.md
index 5651e4a132..e63269042b 100644
--- a/docs/en/docs/sql-manual/sql-functions/date-time-functions/extract.md
+++ b/docs/en/docs/sql-manual/sql-functions/date-time-functions/extract.md
@@ -30,7 +30,7 @@ under the License.
`INT extract(unit FROM DATETIME)`
-Extract DATETIME The value of a specified unit. The unit can be year, day, hour, minute, or second
+Extract DATETIME The value of a specified unit. The unit can be year, day, hour, minute, second or microsecond
### Example
@@ -40,12 +40,13 @@ mysql> select extract(year from '2022-09-22 17:01:30') as year,
-> extract(day from '2022-09-22 17:01:30') as day,
-> extract(hour from '2022-09-22 17:01:30') as hour,
-> extract(minute from '2022-09-22 17:01:30') as minute,
- -> extract(second from '2022-09-22 17:01:30') as second;
-+------+-------+------+------+--------+--------+
-| year | month | day | hour | minute | second |
-+------+-------+------+------+--------+--------+
-| 2022 | 9 | 22 | 17 | 1 | 30 |
-+------+-------+------+------+--------+--------+
+ -> extract(second from '2022-09-22 17:01:30') as second,
+ -> extract(microsecond from cast('2022-09-22 17:01:30.000123' as datetimev2(6))) as microsecond;
++------+-------+------+------+--------+--------+-------------+
+| year | month | day | hour | minute | second | microsecond |
++------+-------+------+------+--------+--------+-------------+
+| 2022 | 9 | 22 | 17 | 1 | 30 | 123 |
++------+-------+------+------+--------+--------+-------------+
```
### keywords
diff --git a/docs/en/docs/sql-manual/sql-functions/date-time-functions/extract.md b/docs/en/docs/sql-manual/sql-functions/date-time-functions/microsecond.md
similarity index 51%
copy from docs/en/docs/sql-manual/sql-functions/date-time-functions/extract.md
copy to docs/en/docs/sql-manual/sql-functions/date-time-functions/microsecond.md
index 5651e4a132..1ac6472e0b 100644
--- a/docs/en/docs/sql-manual/sql-functions/date-time-functions/extract.md
+++ b/docs/en/docs/sql-manual/sql-functions/date-time-functions/microsecond.md
@@ -1,11 +1,11 @@
---
{
- "title": "extract",
+ "title": "microsecond",
"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
@@ -24,30 +24,25 @@ specific language governing permissions and limitations
under the License.
-->
-## extract
+## microsecond
### description
#### Syntax
-`INT extract(unit FROM DATETIME)`
+`INT MICROSECOND(DATETIMEV2 date)`
-Extract DATETIME The value of a specified unit. The unit can be year, day, hour, minute, or second
+Returns microsecond information in the time type.
-### Example
+The parameter is Datetime type
+
+### example
```
-mysql> select extract(year from '2022-09-22 17:01:30') as year,
- -> extract(month from '2022-09-22 17:01:30') as month,
- -> extract(day from '2022-09-22 17:01:30') as day,
- -> extract(hour from '2022-09-22 17:01:30') as hour,
- -> extract(minute from '2022-09-22 17:01:30') as minute,
- -> extract(second from '2022-09-22 17:01:30') as second;
-+------+-------+------+------+--------+--------+
-| year | month | day | hour | minute | second |
-+------+-------+------+------+--------+--------+
-| 2022 | 9 | 22 | 17 | 1 | 30 |
-+------+-------+------+------+--------+--------+
+mysql> select microsecond(cast('1999-01-02 10:11:12.000123' as datetimev2(6))) as microsecond;
++-------------+
+| microsecond |
++-------------+
+| 123 |
++-------------+
```
-
### keywords
-
- extract
+ MICROSECOND
diff --git a/docs/zh-CN/docs/sql-manual/sql-functions/date-time-functions/extract.md b/docs/zh-CN/docs/sql-manual/sql-functions/date-time-functions/extract.md
index e045b035cf..db52430290 100644
--- a/docs/zh-CN/docs/sql-manual/sql-functions/date-time-functions/extract.md
+++ b/docs/zh-CN/docs/sql-manual/sql-functions/date-time-functions/extract.md
@@ -30,7 +30,7 @@ under the License.
`INT extract(unit FROM DATETIME)`
-提取DATETIME某个指定单位的值。单位可以为year, month, day, hour, minute或者second
+提取DATETIME某个指定单位的值。单位可以为year, month, day, hour, minute, second 或者 microsecond
### Example
@@ -40,12 +40,13 @@ mysql> select extract(year from '2022-09-22 17:01:30') as year,
-> extract(day from '2022-09-22 17:01:30') as day,
-> extract(hour from '2022-09-22 17:01:30') as hour,
-> extract(minute from '2022-09-22 17:01:30') as minute,
- -> extract(second from '2022-09-22 17:01:30') as second;
-+------+-------+------+------+--------+--------+
-| year | month | day | hour | minute | second |
-+------+-------+------+------+--------+--------+
-| 2022 | 9 | 22 | 17 | 1 | 30 |
-+------+-------+------+------+--------+--------+
+ -> extract(second from '2022-09-22 17:01:30') as second,
+ -> extract(microsecond from cast('2022-09-22 17:01:30.000123' as datetimev2(6))) as microsecond;
++------+-------+------+------+--------+--------+-------------+
+| year | month | day | hour | minute | second | microsecond |
++------+-------+------+------+--------+--------+-------------+
+| 2022 | 9 | 22 | 17 | 1 | 30 | 123 |
++------+-------+------+------+--------+--------+-------------+
```
### keywords
diff --git a/docs/zh-CN/docs/sql-manual/sql-functions/date-time-functions/extract.md b/docs/zh-CN/docs/sql-manual/sql-functions/date-time-functions/microsecond.md
similarity index 51%
copy from docs/zh-CN/docs/sql-manual/sql-functions/date-time-functions/extract.md
copy to docs/zh-CN/docs/sql-manual/sql-functions/date-time-functions/microsecond.md
index e045b035cf..c07c24f47c 100644
--- a/docs/zh-CN/docs/sql-manual/sql-functions/date-time-functions/extract.md
+++ b/docs/zh-CN/docs/sql-manual/sql-functions/date-time-functions/microsecond.md
@@ -1,11 +1,11 @@
---
{
- "title": "extract",
+ "title": "microsecond",
"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
@@ -24,30 +24,26 @@ specific language governing permissions and limitations
under the License.
-->
-## extract
+## microsecond
### description
#### Syntax
-`INT extract(unit FROM DATETIME)`
+`INT MICROSECOND(DATETIMEV2 date)`
-提取DATETIME某个指定单位的值。单位可以为year, month, day, hour, minute或者second
-### Example
+获得日期中的微秒信息。
+
+参数为 Datetime 类型
+
+### example
```
-mysql> select extract(year from '2022-09-22 17:01:30') as year,
- -> extract(month from '2022-09-22 17:01:30') as month,
- -> extract(day from '2022-09-22 17:01:30') as day,
- -> extract(hour from '2022-09-22 17:01:30') as hour,
- -> extract(minute from '2022-09-22 17:01:30') as minute,
- -> extract(second from '2022-09-22 17:01:30') as second;
-+------+-------+------+------+--------+--------+
-| year | month | day | hour | minute | second |
-+------+-------+------+------+--------+--------+
-| 2022 | 9 | 22 | 17 | 1 | 30 |
-+------+-------+------+------+--------+--------+
+mysql> select microsecond(cast('1999-01-02 10:11:12.000123' as datetimev2(6))) as microsecond;
++-------------+
+| microsecond |
++-------------+
+| 123 |
++-------------+
```
-
### keywords
-
- extract
+ MICROSECOND
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 974f958779..e62e866418 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
@@ -201,6 +201,7 @@ 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.Microsecond;
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;
@@ -534,6 +535,7 @@ public class BuiltinScalarFunctions implements FunctionHelper {
scalar(MaskLastN.class, "mask_last_n"),
scalar(Md5.class, "md5"),
scalar(Md5Sum.class, "md5sum"),
+ scalar(Microsecond.class, "microsecond"),
scalar(Minute.class, "minute"),
scalar(MinuteCeil.class, "minute_ceil"),
scalar(MinuteFloor.class, "minute_floor"),
diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/executable/DateTimeExtractAndTransform.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/executable/DateTimeExtractAndTransform.java
index 1872a2b19d..f8442a810e 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/executable/DateTimeExtractAndTransform.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/executable/DateTimeExtractAndTransform.java
@@ -38,7 +38,7 @@ import java.time.format.DateTimeParseException;
/**
* executable function:
- * year, quarter, month, week, dayOfYear, dayOfweek, dayOfMonth, hour, minute, second
+ * year, quarter, month, week, dayOfYear, dayOfweek, dayOfMonth, hour, minute, second, microsecond
*/
public class DateTimeExtractAndTransform {
/**
@@ -168,7 +168,7 @@ public class DateTimeExtractAndTransform {
}
/**
- * Executable datetime extract hour
+ * Executable datetime extract second
*/
@ExecFunction(name = "second", argTypes = {"DATETIME"}, returnType = "INT")
public static Expression second(DateTimeLiteral date) {
@@ -180,6 +180,14 @@ public class DateTimeExtractAndTransform {
return new IntegerLiteral(((int) date.getSecond()));
}
+ /**
+ * Executable datetime extract microsecond
+ */
+ @ExecFunction(name = "microsecond", argTypes = {"DATETIMEV2"}, returnType = "INT")
+ public static Expression microsecond(DateTimeV2Literal date) {
+ return new IntegerLiteral(((int) date.getMicroSecond()));
+ }
+
/**
* Executable datetime extract dayofyear
*/
diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/Microsecond.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/Microsecond.java
new file mode 100644
index 0000000000..6d3bf51b55
--- /dev/null
+++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/Microsecond.java
@@ -0,0 +1,71 @@
+// 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.UnaryExpression;
+import org.apache.doris.nereids.trees.expressions.visitor.ExpressionVisitor;
+import org.apache.doris.nereids.types.DateTimeV2Type;
+import org.apache.doris.nereids.types.DateV2Type;
+import org.apache.doris.nereids.types.IntegerType;
+
+import com.google.common.base.Preconditions;
+import com.google.common.collect.ImmutableList;
+
+import java.util.List;
+
+/**
+ * ScalarFunction 'microsecond'. This class is generated by GenerateFunction.
+ */
+public class Microsecond extends ScalarFunction
+ implements UnaryExpression, ExplicitlyCastableSignature, PropagateNullableOnDateLikeV2Args {
+
+ public static final List<FunctionSignature> SIGNATURES = ImmutableList.of(
+ FunctionSignature.ret(IntegerType.INSTANCE).args(DateTimeV2Type.SYSTEM_DEFAULT),
+ FunctionSignature.ret(IntegerType.INSTANCE).args(DateV2Type.INSTANCE)
+ );
+
+ /**
+ * constructor with 1 argument.
+ */
+ public Microsecond(Expression arg) {
+ super("microsecond", arg);
+ }
+
+ /**
+ * withChildren.
+ */
+ @Override
+ public Microsecond withChildren(List<Expression> children) {
+ Preconditions.checkArgument(children.size() == 1);
+ return new Microsecond(children.get(0));
+ }
+
+ @Override
+ public List<FunctionSignature> getSignatures() {
+ return SIGNATURES;
+ }
+
+ @Override
+ public <R, C> R accept(ExpressionVisitor<R, C> visitor, C context) {
+ return visitor.visitMicrosecond(this, context);
+ }
+}
diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/literal/DateTimeLiteral.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/literal/DateTimeLiteral.java
index 3bc2bd6202..56a76fb70f 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/literal/DateTimeLiteral.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/literal/DateTimeLiteral.java
@@ -317,6 +317,10 @@ public class DateTimeLiteral extends DateLiteral {
return second;
}
+ public long getMicroSecond() {
+ return microSecond;
+ }
+
@Override
public boolean equals(Object o) {
if (this == o) {
diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/visitor/ScalarFunctionVisitor.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/visitor/ScalarFunctionVisitor.java
index c674e41e18..51e29fc3a9 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/visitor/ScalarFunctionVisitor.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/visitor/ScalarFunctionVisitor.java
@@ -203,6 +203,7 @@ 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.Microsecond;
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;
@@ -1114,6 +1115,10 @@ public interface ScalarFunctionVisitor<R, C> {
return visitScalarFunction(md5Sum, context);
}
+ default R visitMicrosecond(Microsecond microsecond, C context) {
+ return visitScalarFunction(microsecond, context);
+ }
+
default R visitMinute(Minute minute, C context) {
return visitScalarFunction(minute, context);
}
diff --git a/gensrc/script/doris_builtins_functions.py b/gensrc/script/doris_builtins_functions.py
index c7e36eb76e..83db53b8a5 100644
--- a/gensrc/script/doris_builtins_functions.py
+++ b/gensrc/script/doris_builtins_functions.py
@@ -1012,6 +1012,7 @@ visible_functions = {
[['hour'], 'TINYINT', ['DATETIMEV2'], ''],
[['minute'], 'TINYINT', ['DATETIMEV2'], ''],
[['second'], 'TINYINT', ['DATETIMEV2'], ''],
+ [['microsecond'], 'INT', ['DATETIMEV2'], ''],
[['year'], 'SMALLINT', ['DATEV2'], ''],
[['month'], 'TINYINT', ['DATEV2'], ''],
@@ -2044,5 +2045,5 @@ null_result_with_one_null_param_functions = [
'ST_Contains'
]
-invisible_functions = {
-}
+invisible_functions = [
+]
diff --git a/gensrc/thrift/Opcodes.thrift b/gensrc/thrift/Opcodes.thrift
index 905858016f..f6444ebe21 100644
--- a/gensrc/thrift/Opcodes.thrift
+++ b/gensrc/thrift/Opcodes.thrift
@@ -48,6 +48,7 @@ enum TExprOpcode {
TIMESTAMP_HOUR,
TIMESTAMP_HOURS_ADD,
TIMESTAMP_HOURS_SUB,
+ TIMESTAMP_MICROSECOND,
TIMESTAMP_MICROSECONDS_ADD,
TIMESTAMP_MICROSECONDS_SUB,
TIMESTAMP_MINUTE,
diff --git a/regression-test/data/nereids_p0/sql_functions/datetime_functions/test_date_function.out b/regression-test/data/nereids_p0/sql_functions/datetime_functions/test_date_function.out
index d74dd598a1..617d55e6af 100644
--- a/regression-test/data/nereids_p0/sql_functions/datetime_functions/test_date_function.out
+++ b/regression-test/data/nereids_p0/sql_functions/datetime_functions/test_date_function.out
@@ -225,6 +225,9 @@ February
-- !sql --
0
+-- !sql --
+67890
+
-- !sql --
2014-12-21T12:34:56
@@ -377,6 +380,9 @@ February
-- !sql --
198912
+-- !sql --
+767891
+
-- !sql --
1 2022-08-01
2 2022-08-01
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 d179d5360f..2837f41d68 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
@@ -248,6 +248,9 @@ February
-- !sql --
0
+-- !sql --
+767890
+
-- !sql --
2014-12-21T12:34:56
@@ -406,6 +409,9 @@ February
-- !sql --
200
+-- !sql --
+767891
+
-- !sql --
1 2022-08-01
2 2022-08-01
diff --git a/regression-test/suites/nereids_p0/sql_functions/datetime_functions/test_date_function.groovy b/regression-test/suites/nereids_p0/sql_functions/datetime_functions/test_date_function.groovy
index d4eb266f2d..8d74e47d98 100644
--- a/regression-test/suites/nereids_p0/sql_functions/datetime_functions/test_date_function.groovy
+++ b/regression-test/suites/nereids_p0/sql_functions/datetime_functions/test_date_function.groovy
@@ -295,6 +295,9 @@ suite("test_date_function") {
qt_sql """ select second('2018-12-31 23:59:59') """
qt_sql """ select second('2018-12-31 00:00:00') """
+ // MICROSECOND
+ qt_sql """ select microsecond(cast('1999-01-02 10:11:12.067890' as datetimev2(6))); """
+
// STR_TO_DATE
sql """ truncate table ${tableName} """
sql """ insert into ${tableName} values ("2014-12-21 12:34:56") """
@@ -383,6 +386,18 @@ suite("test_date_function") {
qt_sql """ select yearweek('1989-03-21', 6) """
qt_sql """ select yearweek('1989-03-21', 7) """
+
+ // microsecond
+ sql """ drop table ${tableName} """
+ tableName = "test_microsecond"
+ sql """ DROP TABLE IF EXISTS ${tableName} """
+ sql """
+ CREATE TABLE IF NOT EXISTS ${tableName} (k1 datetimev2(6)) duplicate key(k1) distributed by hash(k1) buckets 1 properties('replication_num' = '1');
+ """
+ sql """ insert into ${tableName} values('1999-01-02 10:11:12.767891') """
+
+ qt_sql """ select microsecond(k1) from ${tableName}; """
+
// qt_sql """ select count(*) from (select * from numbers("number" = "200")) tmp1 WHERE 0 <= UNIX_TIMESTAMP(); """
sql """ drop table ${tableName} """
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 9db508a8a6..e31f410127 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
@@ -316,6 +316,9 @@ suite("test_date_function") {
qt_sql """ select second('2018-12-31 23:59:59') """
qt_sql """ select second('2018-12-31 00:00:00') """
+ // MICROSECOND
+ qt_sql """ select microsecond(cast('1999-01-02 10:11:12.767890' as datetimev2(6))) """
+
// STR_TO_DATE
sql """ truncate table ${tableName} """
sql """ insert into ${tableName} values ("2014-12-21 12:34:56") """
@@ -407,6 +410,18 @@ suite("test_date_function") {
qt_sql """ select count(*) from (select * from numbers("number" = "200")) tmp1 WHERE 0 <= UNIX_TIMESTAMP(); """
+ // microsecond
+ sql """ drop table ${tableName} """
+ tableName = "test_microsecond"
+ sql """ DROP TABLE IF EXISTS ${tableName} """
+ sql """
+ CREATE TABLE IF NOT EXISTS ${tableName} (k1 datetimev2(6)) duplicate key(k1) distributed by hash(k1) buckets 1 properties('replication_num' = '1');
+ """
+ sql """ insert into ${tableName} values('1999-01-02 10:11:12.767891') """
+
+ qt_sql """ select microsecond(k1) from ${tableName}; """
+
+ // from_unixtime
sql """ drop table ${tableName} """
tableName = "test_from_unixtime"
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org