This is an automated email from the ASF dual-hosted git repository.
yiguolei pushed a commit to branch branch-4.0
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/branch-4.0 by this push:
new 12bd9ae9b9f branch-4.0: [Feature](function) Support function
TO_SECONDS #58635 (#58868)
12bd9ae9b9f is described below
commit 12bd9ae9b9fee703930f1a9d08d92f4e25a28faf
Author: github-actions[bot]
<41898282+github-actions[bot]@users.noreply.github.com>
AuthorDate: Wed Dec 10 11:02:26 2025 +0800
branch-4.0: [Feature](function) Support function TO_SECONDS #58635 (#58868)
Cherry-picked from #58635
Co-authored-by: linrrarity <[email protected]>
---
be/src/vec/functions/date_time_transforms.h | 2 +
be/src/vec/functions/to_time_function.cpp | 3 +
.../doris/catalog/BuiltinScalarFunctions.java | 2 +
.../executable/DateTimeExtractAndTransform.java | 46 ++++++++++++--
.../expressions/functions/scalar/ToSeconds.java | 74 ++++++++++++++++++++++
.../expressions/visitor/ScalarFunctionVisitor.java | 5 ++
.../sql-functions/doc_date_functions_test.out | 48 ++++++++++++++
.../sql-functions/doc_date_functions_test.groovy | 35 ++++++++++
8 files changed, 211 insertions(+), 4 deletions(-)
diff --git a/be/src/vec/functions/date_time_transforms.h
b/be/src/vec/functions/date_time_transforms.h
index ddce29e2446..f494750cf24 100644
--- a/be/src/vec/functions/date_time_transforms.h
+++ b/be/src/vec/functions/date_time_transforms.h
@@ -106,6 +106,8 @@ TIME_FUNCTION_IMPL(DayOfWeekImpl, dayofweek, day_of_week());
TIME_FUNCTION_IMPL(WeekDayImpl, weekday, weekday());
// TODO: the method should be always not nullable
TIME_FUNCTION_IMPL(ToDaysImpl, to_days, daynr());
+TIME_FUNCTION_IMPL(ToSecondsImpl, to_seconds,
+ daynr() * 86400L + date_time_value.time_part_to_seconds());
#define TIME_FUNCTION_ONE_ARG_IMPL(CLASS, UNIT, FUNCTION)
\
template <PrimitiveType PType>
\
diff --git a/be/src/vec/functions/to_time_function.cpp
b/be/src/vec/functions/to_time_function.cpp
index e930a42eb4b..a382ddedfed 100644
--- a/be/src/vec/functions/to_time_function.cpp
+++ b/be/src/vec/functions/to_time_function.cpp
@@ -41,6 +41,8 @@ using FunctionSecondV2 =
FunctionDateOrDateTimeToSomething<DataTypeInt8, ToSecon
using FunctionToDaysV2 = FunctionDateOrDateTimeToSomething<DataTypeInt32,
ToDaysImpl<TYPE_DATEV2>>;
using FunctionToDateV2 = FunctionDateOrDateTimeToSomething<DataTypeDateV2,
ToDateImpl<TYPE_DATEV2>>;
using FunctionDateV2 = FunctionDateOrDateTimeToSomething<DataTypeDateV2,
DateImpl<TYPE_DATEV2>>;
+using FunctionToSeconds =
+ FunctionDateOrDateTimeToSomething<DataTypeInt64,
ToSecondsImpl<TYPE_DATETIMEV2>>;
using FunctionDateTimeV2Year =
FunctionDateOrDateTimeToSomething<DataTypeInt16,
ToYearImpl<TYPE_DATETIMEV2>>;
@@ -97,6 +99,7 @@ void
register_function_to_time_function(SimpleFunctionFactory& factory) {
factory.register_function<FunctionDateTimeV2Date>();
factory.register_function<FunctionCenturyV2>();
factory.register_function<FunctionDateTimeV2Century>();
+ factory.register_function<FunctionToSeconds>();
factory.register_alias("date", "datev2");
factory.register_alias("to_date", "to_datev2");
}
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 2d391a02c51..3542f983c7a 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
@@ -504,6 +504,7 @@ import
org.apache.doris.nereids.trees.expressions.functions.scalar.ToIso8601;
import org.apache.doris.nereids.trees.expressions.functions.scalar.ToJson;
import org.apache.doris.nereids.trees.expressions.functions.scalar.ToMonday;
import
org.apache.doris.nereids.trees.expressions.functions.scalar.ToQuantileState;
+import org.apache.doris.nereids.trees.expressions.functions.scalar.ToSeconds;
import org.apache.doris.nereids.trees.expressions.functions.scalar.Tokenize;
import
org.apache.doris.nereids.trees.expressions.functions.scalar.TopLevelDomain;
import org.apache.doris.nereids.trees.expressions.functions.scalar.Translate;
@@ -1056,6 +1057,7 @@ public class BuiltinScalarFunctions implements
FunctionHelper {
scalar(ToMonday.class, "to_monday"),
scalar(TopLevelDomain.class, "top_level_domain"),
scalar(ToQuantileState.class, "to_quantile_state"),
+ scalar(ToSeconds.class, "to_seconds"),
scalar(Translate.class, "translate"),
scalar(Trim.class, "trim"),
scalar(TrimIn.class, "trim_in"),
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 a6fcab95c32..ea6551687ba 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
@@ -572,14 +572,52 @@ public class DateTimeExtractAndTransform {
*/
@ExecFunction(name = "to_days")
public static Expression toDays(DateV2Literal date) {
- return new IntegerLiteral(((int) Duration.between(
- LocalDateTime.of(0, 1, 1, 0, 0, 0),
date.toJavaDateType()).toDays()));
+ return new IntegerLiteral((int) calcDayNumber(date.getYear(),
date.getMonth(), date.getDay()));
}
@ExecFunction(name = "to_days")
public static Expression toDays(DateTimeV2Literal date) {
- return new IntegerLiteral(((int) Duration.between(
- LocalDateTime.of(0, 1, 1, 0, 0, 0),
date.toJavaDateType()).toDays()));
+ return new IntegerLiteral((int) calcDayNumber(date.getYear(),
date.getMonth(), date.getDay()));
+ }
+
+ /**
+ * date transformation function: to_seconds
+ */
+ @ExecFunction(name = "to_seconds")
+ public static Expression toSeconds(DateV2Literal date) {
+ return new BigIntLiteral(calcDayNumber(date.getYear(),
date.getMonth(), date.getDay()) * 86400L);
+ }
+
+ @ExecFunction(name = "to_seconds")
+ public static Expression toSeconds(DateTimeV2Literal date) {
+ return new BigIntLiteral(calcDayNumber(date.getYear(),
date.getMonth(), date.getDay()) * 86400L
+ + date.getHour() * 3600L +
date.getMinute() * 60L + date.getSecond());
+ }
+
+ // Java Duration cannot represent days before 0000-01-01, so using it
would turn
+ // TO_DAYS('0000-01-01') into the diff between that date and itself (0).
+ // We use BE's arithmetic instead so 0000-01-01 returns 1 as expected.
+ // Previous FE logic often matched BE only because Java treats year 0 as
leap
+ // making TO_DAYS('0000-02-29') fold to 59.
+ // While BE/MySQL consider year 0 common, so:
+ // TO_DAYS('0000-02-28') == 59 and TO_DAYS('0000-02-29') == NULL. After
+ // 0000-03-01 the two implementations naturally align again.
+ private static long calcDayNumber(long year, long month, long day) {
+ if (year == 0 && month == 0) {
+ return 0;
+ }
+ if (year == 0 && month == 1 && day == 1) {
+ return 1;
+ }
+
+ long y = year;
+ long delsum = 365L * y + 31L * (month - 1) + day;
+ if (month <= 2) {
+ y -= 1;
+ } else {
+ delsum -= (month * 4 + 23) / 10;
+ }
+ return delsum + y / 4 - y / 100 + y / 400;
}
/**
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/ToSeconds.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/ToSeconds.java
new file mode 100644
index 00000000000..02a601ca5c4
--- /dev/null
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/scalar/ToSeconds.java
@@ -0,0 +1,74 @@
+// 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.PropagateNullable;
+import org.apache.doris.nereids.trees.expressions.shape.UnaryExpression;
+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 'to_seoncds'.
+ */
+public class ToSeconds extends ScalarFunction
+ implements UnaryExpression, ExplicitlyCastableSignature,
PropagateNullable {
+
+ private static final List<FunctionSignature> SIGNATURES = ImmutableList.of(
+
FunctionSignature.ret(BigIntType.INSTANCE).args(DateTimeV2Type.WILDCARD)
+ );
+
+ /**
+ * constructor with 1 argument.
+ */
+ public ToSeconds(Expression arg) {
+ super("to_seconds", arg);
+ }
+
+ /** constructor for withChildren and reuse signature */
+ private ToSeconds(ScalarFunctionParams functionParams) {
+ super(functionParams);
+ }
+
+ /**
+ * withChildren.
+ */
+ @Override
+ public ToSeconds withChildren(List<Expression> children) {
+ Preconditions.checkArgument(children.size() == 1);
+ return new ToSeconds(getFunctionParams(children));
+ }
+
+ @Override
+ public List<FunctionSignature> getSignatures() {
+ return SIGNATURES;
+ }
+
+ @Override
+ public <R, C> R accept(ExpressionVisitor<R, C> visitor, C context) {
+ return visitor.visitToSeconds(this, context);
+ }
+}
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 de862909961..0c1a0bcb254 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
@@ -504,6 +504,7 @@ import
org.apache.doris.nereids.trees.expressions.functions.scalar.ToIso8601;
import org.apache.doris.nereids.trees.expressions.functions.scalar.ToJson;
import org.apache.doris.nereids.trees.expressions.functions.scalar.ToMonday;
import
org.apache.doris.nereids.trees.expressions.functions.scalar.ToQuantileState;
+import org.apache.doris.nereids.trees.expressions.functions.scalar.ToSeconds;
import org.apache.doris.nereids.trees.expressions.functions.scalar.Tokenize;
import
org.apache.doris.nereids.trees.expressions.functions.scalar.TopLevelDomain;
import org.apache.doris.nereids.trees.expressions.functions.scalar.Translate;
@@ -2378,6 +2379,10 @@ public interface ScalarFunctionVisitor<R, C> {
return visitScalarFunction(toQuantileState, context);
}
+ default R visitToSeconds(ToSeconds toSeconds, C context) {
+ return visitScalarFunction(toSeconds, context);
+ }
+
default R visitTranslate(Translate translate, C context) {
return visitScalarFunction(translate, context);
}
diff --git
a/regression-test/data/doc/sql-manual/sql-functions/doc_date_functions_test.out
b/regression-test/data/doc/sql-manual/sql-functions/doc_date_functions_test.out
index 99924258d49..25c5b1672ab 100644
---
a/regression-test/data/doc/sql-manual/sql-functions/doc_date_functions_test.out
+++
b/regression-test/data/doc/sql-manual/sql-functions/doc_date_functions_test.out
@@ -1622,6 +1622,18 @@ da fanadur
-- !to_days_2 --
733321
+-- !to_days_3 --
+1
+
+-- !to_days_4 --
+59
+
+-- !to_days_5 --
+\N
+
+-- !to_days_6 --
+60
+
-- !to_iso8601_1 --
2023-10-05
@@ -2017,6 +2029,42 @@ da fanadur
\N
07:23:25
+-- !to_seconds_1 --
+63358934400
+
+-- !to_seconds_2 --
+63358970589
+
+-- !to_seconds_3 --
+86400
+
+-- !to_seconds_4 --
+315569519999
+
+-- !to_seconds_5 --
+\N
+
+-- !to_seconds_6 --
+\N
+
+-- !to_seconds_7 --
+\N
+
+-- !to_seconds_8 --
+63863901296
+
+-- !to_seconds_9 --
+\N
+
+-- !to_seconds_10 --
+\N
+
+-- !to_seconds_11 --
+63902908800
+
+-- !to_seconds_12 --
+63902953845
+
-- !dateceil --
2025-10-10T12:34:56 2026-01-01T00:00 2025-11-01T00:00
2025-10-11T00:00 2025-10-10T13:00 2025-10-10T12:35
2025-10-10T12:34:56
2025-01-01T00:00 2025-01-01T00:00 2025-01-01T00:00
2025-01-01T00:00 2025-01-01T00:00 2025-01-01T00:00
2025-01-01T00:00
diff --git
a/regression-test/suites/doc/sql-manual/sql-functions/doc_date_functions_test.groovy
b/regression-test/suites/doc/sql-manual/sql-functions/doc_date_functions_test.groovy
index 583394da8a4..41930efff3b 100644
---
a/regression-test/suites/doc/sql-manual/sql-functions/doc_date_functions_test.groovy
+++
b/regression-test/suites/doc/sql-manual/sql-functions/doc_date_functions_test.groovy
@@ -1202,6 +1202,11 @@ suite("doc_date_functions_test") {
// 79. TO_DAYS function tests
qt_to_days_1 """select to_days('2007-10-07')"""
qt_to_days_2 """select to_days('2007-10-07 10:03:09')"""
+ qt_to_days_3 """select to_days('0000-01-01')"""
+ qt_to_days_4 """select to_days('0000-02-28')"""
+ qt_to_days_5 """select to_days('0000-02-29')"""
+ qt_to_days_6 """select to_days('0000-03-01')"""
+
// 80. TO_ISO8601 function tests
qt_to_iso8601_1 """SELECT TO_ISO8601(CAST('2023-10-05' AS DATE)) AS
date_result"""
@@ -1400,6 +1405,32 @@ suite("doc_date_functions_test") {
testFoldConst("SELECT YEARWEEK('2023-01-02', 5) AS yearweek_mode5")
testFoldConst("SELECT YEARWEEK('2023-12-25', 1) AS date_type_mode1")
+ // TO_SECONDS function tests
+ qt_to_seconds_1 """select to_seconds('2007-10-07')"""
+ qt_to_seconds_2 """select to_seconds('2007-10-07 10:03:09')"""
+ qt_to_seconds_3 """select to_seconds('0000-01-01 00:00:00')"""
+ qt_to_seconds_4 """select to_seconds('9999-12-31 23:59:59')"""
+ qt_to_seconds_5 """select to_seconds('101-01-01 08:30:15.123456')"""
+ qt_to_seconds_6 """select to_seconds('2023-02-30 12:00:00')"""
+ qt_to_seconds_7 """select to_seconds(NULL)"""
+ qt_to_seconds_8 """select to_seconds('2023-10-07 12:34:56.654321')"""
+ qt_to_seconds_9 """select to_seconds('12:34:56')"""
+ qt_to_seconds_10 """select to_seconds('-12:34:56.123456')"""
+ qt_to_seconds_11 """select to_seconds(20250101)"""
+ qt_to_seconds_12 """select to_seconds(20250101123045)"""
+ testFoldConst("SELECT to_seconds('2007-10-07')")
+ testFoldConst("SELECT to_seconds('2007-10-07 10:03:09')")
+ testFoldConst("SELECT to_seconds('0000-01-01 00:00:00')")
+ testFoldConst("SELECT to_seconds('9999-12-31 23:59:59')")
+ testFoldConst("SELECT to_seconds('101-01-01 08:30:15.123456')")
+ testFoldConst("SELECT to_seconds('2023-02-30 12:00:00')")
+ testFoldConst("SELECT to_seconds(NULL)")
+ testFoldConst("SELECT to_seconds('2023-10-07 12:34:56.654321')")
+ testFoldConst("SELECT to_seconds('12:34:56')")
+ testFoldConst("SELECT to_seconds('-12:34:56.123456')")
+ testFoldConst("SELECT to_seconds(20250101)")
+ testFoldConst("SELECT to_seconds(20250101123045)")
+
// Test constant folding for Group 1 functions (基础日期函数)
// 1. CONVERT_TZ function constant folding tests
@@ -1889,6 +1920,10 @@ suite("doc_date_functions_test") {
// 79. TO_DAYS function constant folding tests
testFoldConst("SELECT TO_DAYS('2007-10-07')")
testFoldConst("SELECT TO_DAYS('2007-10-07 10:03:09')")
+ testFoldConst("SELECT TO_DAYS('0000-01-01 00:00:00')")
+ testFoldConst("SELECT TO_DAYS('0000-02-28')")
+ testFoldConst("SELECT TO_DAYS('0000-02-29')")
+ testFoldConst("SELECT TO_DAYS('0000-03-01')")
// 80. TO_ISO8601 function constant folding tests
testFoldConst("SELECT TO_ISO8601(CAST('2023-10-05' AS DATE)) AS
date_result")
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
