This is an automated email from the ASF dual-hosted git repository.
morrysnow 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 3a34ec95af [FE](fucntion) add date_floor/ceil in FE function (#23539)
3a34ec95af is described below
commit 3a34ec95af4001aeca5a237feb67d6193be27272
Author: zhangstar333 <[email protected]>
AuthorDate: Thu Aug 31 19:26:47 2023 +0800
[FE](fucntion) add date_floor/ceil in FE function (#23539)
---
.../sql-functions/date-time-functions/date_ceil.md | 96 +++++++++++
.../date-time-functions/date_floor.md | 104 ++++++++++++
.../sql-functions/date-time-functions/date_ceil.md | 96 +++++++++++
.../date-time-functions/date_floor.md | 104 ++++++++++++
.../antlr4/org/apache/doris/nereids/DorisLexer.g4 | 4 +-
.../antlr4/org/apache/doris/nereids/DorisParser.g4 | 14 ++
fe/fe-core/src/main/cup/sql_parser.cup | 10 +-
.../org/apache/doris/analysis/DateLiteral.java | 16 +-
.../apache/doris/analysis/FunctionCallExpr.java | 13 ++
.../doris/nereids/parser/LogicalPlanBuilder.java | 74 +++++++++
.../trees/expressions/ExpressionEvaluator.java | 4 +-
.../functions/executable/TimeRoundSeries.java | 106 +++++++-----
.../trees/expressions/literal/DateLiteral.java | 34 +++-
.../trees/expressions/literal/DateTimeLiteral.java | 10 +-
.../expressions/literal/DateTimeV2Literal.java | 12 +-
.../trees/expressions/literal/DateV2Literal.java | 6 +-
.../java/org/apache/doris/rewrite/FEFunctions.java | 184 ++++++++++++++++++++-
gensrc/script/doris_builtins_functions.py | 4 +-
.../datetime_functions/test_date_floor_ceil.out | 37 +++++
.../datetime_functions/test_date_floor_ceil.groovy | 36 ++++
20 files changed, 892 insertions(+), 72 deletions(-)
diff --git
a/docs/en/docs/sql-manual/sql-functions/date-time-functions/date_ceil.md
b/docs/en/docs/sql-manual/sql-functions/date-time-functions/date_ceil.md
new file mode 100644
index 0000000000..d2a988295b
--- /dev/null
+++ b/docs/en/docs/sql-manual/sql-functions/date-time-functions/date_ceil.md
@@ -0,0 +1,96 @@
+---
+{
+ "title": "date_ceil",
+ "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.
+-->
+
+## date_ceil
+### description
+#### Syntax
+
+`DATETIME DATE_CEIL(DATETIME datetime, INTERVAL period type)`
+
+
+Convert the date to the nearest rounding up time of the specified time
interval period.
+
+The datetime parameter is a valid date expression.
+
+The period parameter specifies how many units each cycle consists of, starting
from 0001-01-01T00:00:00
+
+type ：YEAR, MONTH, DAY, HOUR, MINUTE, SECOND.
+
+### example
+
+```
+mysql [(none)]>select date_ceil("2023-07-13 22:28:18",interval 5 second);
++--------------------------------------------------------------+
+| second_ceil('2023-07-13 22:28:18', 5, '0001-01-01 00:00:00') |
++--------------------------------------------------------------+
+| 2023-07-13 22:28:20 |
++--------------------------------------------------------------+
+1 row in set (0.01 sec)
+
+mysql [(none)]>select date_ceil("2023-07-13 22:28:18",interval 5 minute);
++--------------------------------------------------------------+
+| minute_ceil('2023-07-13 22:28:18', 5, '0001-01-01 00:00:00') |
++--------------------------------------------------------------+
+| 2023-07-13 22:30:00 |
++--------------------------------------------------------------+
+1 row in set (0.01 sec)
+
+mysql [(none)]>select date_ceil("2023-07-13 22:28:18",interval 5 hour);
++------------------------------------------------------------+
+| hour_ceil('2023-07-13 22:28:18', 5, '0001-01-01 00:00:00') |
++------------------------------------------------------------+
+| 2023-07-13 23:00:00 |
++------------------------------------------------------------+
+1 row in set (0.01 sec)
+
+mysql [(none)]>select date_ceil("2023-07-13 22:28:18",interval 5 day);
++-----------------------------------------------------------+
+| day_ceil('2023-07-13 22:28:18', 5, '0001-01-01 00:00:00') |
++-----------------------------------------------------------+
+| 2023-07-15 00:00:00 |
++-----------------------------------------------------------+
+1 row in set (0.00 sec)
+
+mysql [(none)]>select date_ceil("2023-07-13 22:28:18",interval 5 month);
++-------------------------------------------------------------+
+| month_ceil('2023-07-13 22:28:18', 5, '0001-01-01 00:00:00') |
++-------------------------------------------------------------+
+| 2023-12-01 00:00:00 |
++-------------------------------------------------------------+
+1 row in set (0.01 sec)
+
+mysql [(none)]>select date_ceil("2023-07-13 22:28:18",interval 5 year);
++------------------------------------------------------------+
+| year_ceil('2023-07-13 22:28:18', 5, '0001-01-01 00:00:00') |
++------------------------------------------------------------+
+| 2026-01-01 00:00:00 |
++------------------------------------------------------------+
+1 row in set (0.00 sec)
+```
+
+### keywords
+
+ DATE_CEIL,DATE,CEIL
diff --git
a/docs/en/docs/sql-manual/sql-functions/date-time-functions/date_floor.md
b/docs/en/docs/sql-manual/sql-functions/date-time-functions/date_floor.md
new file mode 100644
index 0000000000..cc2ac666d9
--- /dev/null
+++ b/docs/en/docs/sql-manual/sql-functions/date-time-functions/date_floor.md
@@ -0,0 +1,104 @@
+---
+{
+ "title": "date_floor",
+ "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.
+-->
+
+## date_floor
+### description
+#### Syntax
+
+`DATETIME DATE_FLOOR(DATETIME datetime, INTERVAL period type)`
+
+
+Converts a date to the nearest rounding down time of a specified time interval
period.
+
+The datetime parameter is a valid date expression.
+
+The period parameter specifies how many units each cycle consists of, starting
from 0001-01-01T00:00:00
+
+type ：YEAR, MONTH, DAY, HOUR, MINUTE, SECOND.
+
+### example
+
+```
+mysql>select date_floor("0001-01-01 00:00:16",interval 5 second);
++---------------------------------------------------------------+
+| second_floor('0001-01-01 00:00:16', 5, '0001-01-01 00:00:00') |
++---------------------------------------------------------------+
+| 0001-01-01 00:00:15 |
++---------------------------------------------------------------+
+1 row in set (0.00 sec)
+
+mysql>select date_floor("0001-01-01 00:00:18",interval 5 second);
++---------------------------------------------------------------+
+| second_floor('0001-01-01 00:00:18', 5, '0001-01-01 00:00:00') |
++---------------------------------------------------------------+
+| 0001-01-01 00:00:15 |
++---------------------------------------------------------------+
+1 row in set (0.01 sec)
+
+mysql>select date_floor("2023-07-13 22:28:18",interval 5 minute);
++---------------------------------------------------------------+
+| minute_floor('2023-07-13 22:28:18', 5, '0001-01-01 00:00:00') |
++---------------------------------------------------------------+
+| 2023-07-13 22:25:00 |
++---------------------------------------------------------------+
+1 row in set (0.00 sec)
+
+mysql>select date_floor("2023-07-13 22:28:18",interval 5 hour);
++-------------------------------------------------------------+
+| hour_floor('2023-07-13 22:28:18', 5, '0001-01-01 00:00:00') |
++-------------------------------------------------------------+
+| 2023-07-13 18:00:00 |
++-------------------------------------------------------------+
+1 row in set (0.01 sec)
+
+mysql>select date_floor("2023-07-13 22:28:18",interval 5 day);
++------------------------------------------------------------+
+| day_floor('2023-07-13 22:28:18', 5, '0001-01-01 00:00:00') |
++------------------------------------------------------------+
+| 2023-07-10 00:00:00 |
++------------------------------------------------------------+
+1 row in set (0.00 sec)
+
+mysql>select date_floor("2023-07-13 22:28:18",interval 5 month);
++--------------------------------------------------------------+
+| month_floor('2023-07-13 22:28:18', 5, '0001-01-01 00:00:00') |
++--------------------------------------------------------------+
+| 2023-07-01 00:00:00 |
++--------------------------------------------------------------+
+1 row in set (0.01 sec)
+
+mysql>select date_floor("2023-07-13 22:28:18",interval 5 year);
++-------------------------------------------------------------+
+| year_floor('2023-07-13 22:28:18', 5, '0001-01-01 00:00:00') |
++-------------------------------------------------------------+
+| 2021-01-01 00:00:00 |
++-------------------------------------------------------------+
+
+```
+
+### keywords
+
+ DATE_FLOOR,DATE,FLOOR
diff --git
a/docs/zh-CN/docs/sql-manual/sql-functions/date-time-functions/date_ceil.md
b/docs/zh-CN/docs/sql-manual/sql-functions/date-time-functions/date_ceil.md
new file mode 100644
index 0000000000..eefe578262
--- /dev/null
+++ b/docs/zh-CN/docs/sql-manual/sql-functions/date-time-functions/date_ceil.md
@@ -0,0 +1,96 @@
+---
+{
+ "title": "date_ceil",
+ "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.
+-->
+
+## date_ceil
+### description
+#### Syntax
+
+`DATETIME DATE_CEIL(DATETIME datetime, INTERVAL period type)`
+
+
+将日期转化为指定的时间间隔周期的最近上取整时刻。
+
+datetime 参数是合法的日期表达式。
+
+period 参数是指定每个周期有多少个单位组成，开始的时间起点为0001-01-01T00:00:00.
+
+type 参数可以是下列值：YEAR, MONTH, DAY, HOUR, MINUTE, SECOND.
+
+### example
+
+```
+mysql [(none)]>select date_ceil("2023-07-13 22:28:18",interval 5 second);
++--------------------------------------------------------------+
+| second_ceil('2023-07-13 22:28:18', 5, '0001-01-01 00:00:00') |
++--------------------------------------------------------------+
+| 2023-07-13 22:28:20 |
++--------------------------------------------------------------+
+1 row in set (0.01 sec)
+
+mysql [(none)]>select date_ceil("2023-07-13 22:28:18",interval 5 minute);
++--------------------------------------------------------------+
+| minute_ceil('2023-07-13 22:28:18', 5, '0001-01-01 00:00:00') |
++--------------------------------------------------------------+
+| 2023-07-13 22:30:00 |
++--------------------------------------------------------------+
+1 row in set (0.01 sec)
+
+mysql [(none)]>select date_ceil("2023-07-13 22:28:18",interval 5 hour);
++------------------------------------------------------------+
+| hour_ceil('2023-07-13 22:28:18', 5, '0001-01-01 00:00:00') |
++------------------------------------------------------------+
+| 2023-07-13 23:00:00 |
++------------------------------------------------------------+
+1 row in set (0.01 sec)
+
+mysql [(none)]>select date_ceil("2023-07-13 22:28:18",interval 5 day);
++-----------------------------------------------------------+
+| day_ceil('2023-07-13 22:28:18', 5, '0001-01-01 00:00:00') |
++-----------------------------------------------------------+
+| 2023-07-15 00:00:00 |
++-----------------------------------------------------------+
+1 row in set (0.00 sec)
+
+mysql [(none)]>select date_ceil("2023-07-13 22:28:18",interval 5 month);
++-------------------------------------------------------------+
+| month_ceil('2023-07-13 22:28:18', 5, '0001-01-01 00:00:00') |
++-------------------------------------------------------------+
+| 2023-12-01 00:00:00 |
++-------------------------------------------------------------+
+1 row in set (0.01 sec)
+
+mysql [(none)]>select date_ceil("2023-07-13 22:28:18",interval 5 year);
++------------------------------------------------------------+
+| year_ceil('2023-07-13 22:28:18', 5, '0001-01-01 00:00:00') |
++------------------------------------------------------------+
+| 2026-01-01 00:00:00 |
++------------------------------------------------------------+
+1 row in set (0.00 sec)
+```
+
+### keywords
+
+ DATE_CEIL,DATE,CEIL
diff --git
a/docs/zh-CN/docs/sql-manual/sql-functions/date-time-functions/date_floor.md
b/docs/zh-CN/docs/sql-manual/sql-functions/date-time-functions/date_floor.md
new file mode 100644
index 0000000000..53ef6003fd
--- /dev/null
+++ b/docs/zh-CN/docs/sql-manual/sql-functions/date-time-functions/date_floor.md
@@ -0,0 +1,104 @@
+---
+{
+ "title": "date_floor",
+ "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.
+-->
+
+## date_floor
+### description
+#### Syntax
+
+`DATETIME DATE_FLOOR(DATETIME datetime, INTERVAL period type)`
+
+
+将日期转化为指定的时间间隔周期的最近下取整时刻。
+
+datetime 参数是合法的日期表达式。
+
+period 参数是指定每个周期有多少个单位组成，开始的时间起点为0001-01-01T00:00:00.
+
+type 参数可以是下列值：YEAR, MONTH, DAY, HOUR, MINUTE, SECOND.
+
+### example
+
+```
+mysql>select date_floor("0001-01-01 00:00:16",interval 5 second);
++---------------------------------------------------------------+
+| second_floor('0001-01-01 00:00:16', 5, '0001-01-01 00:00:00') |
++---------------------------------------------------------------+
+| 0001-01-01 00:00:15 |
++---------------------------------------------------------------+
+1 row in set (0.00 sec)
+
+mysql>select date_floor("0001-01-01 00:00:18",interval 5 second);
++---------------------------------------------------------------+
+| second_floor('0001-01-01 00:00:18', 5, '0001-01-01 00:00:00') |
++---------------------------------------------------------------+
+| 0001-01-01 00:00:15 |
++---------------------------------------------------------------+
+1 row in set (0.01 sec)
+
+mysql>select date_floor("2023-07-13 22:28:18",interval 5 minute);
++---------------------------------------------------------------+
+| minute_floor('2023-07-13 22:28:18', 5, '0001-01-01 00:00:00') |
++---------------------------------------------------------------+
+| 2023-07-13 22:25:00 |
++---------------------------------------------------------------+
+1 row in set (0.00 sec)
+
+mysql>select date_floor("2023-07-13 22:28:18",interval 5 hour);
++-------------------------------------------------------------+
+| hour_floor('2023-07-13 22:28:18', 5, '0001-01-01 00:00:00') |
++-------------------------------------------------------------+
+| 2023-07-13 18:00:00 |
++-------------------------------------------------------------+
+1 row in set (0.01 sec)
+
+mysql>select date_floor("2023-07-13 22:28:18",interval 5 day);
++------------------------------------------------------------+
+| day_floor('2023-07-13 22:28:18', 5, '0001-01-01 00:00:00') |
++------------------------------------------------------------+
+| 2023-07-10 00:00:00 |
++------------------------------------------------------------+
+1 row in set (0.00 sec)
+
+mysql>select date_floor("2023-07-13 22:28:18",interval 5 month);
++--------------------------------------------------------------+
+| month_floor('2023-07-13 22:28:18', 5, '0001-01-01 00:00:00') |
++--------------------------------------------------------------+
+| 2023-07-01 00:00:00 |
++--------------------------------------------------------------+
+1 row in set (0.01 sec)
+
+mysql>select date_floor("2023-07-13 22:28:18",interval 5 year);
++-------------------------------------------------------------+
+| year_floor('2023-07-13 22:28:18', 5, '0001-01-01 00:00:00') |
++-------------------------------------------------------------+
+| 2021-01-01 00:00:00 |
++-------------------------------------------------------------+
+
+```
+
+### keywords
+
+ DATE_FLOOR,DATE,FLOOR
diff --git a/fe/fe-core/src/main/antlr4/org/apache/doris/nereids/DorisLexer.g4
b/fe/fe-core/src/main/antlr4/org/apache/doris/nereids/DorisLexer.g4
index b6f1ae30e5..532f05dec2 100644
--- a/fe/fe-core/src/main/antlr4/org/apache/doris/nereids/DorisLexer.g4
+++ b/fe/fe-core/src/main/antlr4/org/apache/doris/nereids/DorisLexer.g4
@@ -160,8 +160,10 @@ DATE: 'DATE';
DATEADD: 'DATEADD';
DATEDIFF: 'DATEDIFF';
DATE_ADD: 'DATE_ADD';
-DATE_SUB: 'DATE_SUB';
+DATE_CEIL: 'DATE_CEIL';
DATE_DIFF: 'DATE_DIFF';
+DATE_FLOOR: 'DATE_FLOOR';
+DATE_SUB: 'DATE_SUB';
DBPROPERTIES: 'DBPROPERTIES';
DEFAULT: 'DEFAULT';
DEFINED: 'DEFINED';
diff --git a/fe/fe-core/src/main/antlr4/org/apache/doris/nereids/DorisParser.g4
b/fe/fe-core/src/main/antlr4/org/apache/doris/nereids/DorisParser.g4
index ee8757f8e3..c982b133ed 100644
--- a/fe/fe-core/src/main/antlr4/org/apache/doris/nereids/DorisParser.g4
+++ b/fe/fe-core/src/main/antlr4/org/apache/doris/nereids/DorisParser.g4
@@ -395,6 +395,18 @@ primaryExpression
(INTERVAL unitsAmount=valueExpression unit=datetimeUnit
| unitsAmount=valueExpression)
RIGHT_PAREN
#date_sub
+ | name=DATE_FLOOR
+ LEFT_PAREN
+ timestamp=valueExpression COMMA
+ (INTERVAL unitsAmount=valueExpression unit=datetimeUnit
+ | unitsAmount=valueExpression)
+ RIGHT_PAREN
#dateFloor
+ | name=DATE_CEIL
+ LEFT_PAREN
+ timestamp=valueExpression COMMA
+ (INTERVAL unitsAmount=valueExpression unit=datetimeUnit
+ | unitsAmount=valueExpression)
+ RIGHT_PAREN
#dateCeil
| CASE whenClause+ (ELSE elseExpression=expression)? END
#searchedCase
| CASE value=expression whenClause+ (ELSE elseExpression=expression)? END
#simpleCase
| name=CAST LEFT_PAREN expression AS dataType RIGHT_PAREN
#cast
@@ -624,8 +636,10 @@ nonReserved
| DATE
| DATEV2
| DATE_ADD
+ | DATE_CEIL
| DATEDIFF
| DATE_DIFF
+ | DATE_FLOOR
| DAY
| DBPROPERTIES
| DEFINED
diff --git a/fe/fe-core/src/main/cup/sql_parser.cup
b/fe/fe-core/src/main/cup/sql_parser.cup
index 5f3311ab0f..6f37773c7e 100644
--- a/fe/fe-core/src/main/cup/sql_parser.cup
+++ b/fe/fe-core/src/main/cup/sql_parser.cup
@@ -6873,8 +6873,14 @@ timestamp_arithmetic_expr ::=
// This function should not fully qualified
throw new Exception("interval should not be qualified by database name");
}
-
- RESULT = new TimestampArithmeticExpr(functionName.getFunction(), l.get(0),
v, u);
+ //eg: date_floor("0001-01-01 00:00:18",interval 5 second) convert to
+ //second_floor("0001-01-01 00:00:18", 5, "0001-01-01 00:00:00");
+ if ("date_floor".equalsIgnoreCase(functionName.getFunction()) ||
+ "date_ceil".equalsIgnoreCase(functionName.getFunction())) {
+ RESULT =
FunctionCallExpr.functionWithIntervalConvert(functionName.getFunction().toLowerCase(),
l.get(0), v, u);
+ } else {
+ RESULT = new TimestampArithmeticExpr(functionName.getFunction(),
l.get(0), v, u);
+ }
:}
| function_name:functionName LPAREN time_unit:u COMMA expr:e1 COMMA expr:e2
RPAREN
{:
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/analysis/DateLiteral.java
b/fe/fe-core/src/main/java/org/apache/doris/analysis/DateLiteral.java
index 11d17ec816..8a2f693a55 100644
--- a/fe/fe-core/src/main/java/org/apache/doris/analysis/DateLiteral.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/analysis/DateLiteral.java
@@ -1046,19 +1046,19 @@ public class DateLiteral extends LiteralExpr {
return LocalDateTime.of(year, month, dayOfMonth, hour, minute, second,
microSeconds * 1000);
}
- public DateLiteral plusYears(int year) throws AnalysisException {
+ public DateLiteral plusYears(long year) throws AnalysisException {
return new DateLiteral(getTimeFormatter().plusYears(year), type);
}
- public DateLiteral plusMonths(int month) throws AnalysisException {
+ public DateLiteral plusMonths(long month) throws AnalysisException {
return new DateLiteral(getTimeFormatter().plusMonths(month), type);
}
- public DateLiteral plusDays(int day) throws AnalysisException {
+ public DateLiteral plusDays(long day) throws AnalysisException {
return new DateLiteral(getTimeFormatter().plusDays(day), type);
}
- public DateLiteral plusHours(int hour) throws AnalysisException {
+ public DateLiteral plusHours(long hour) throws AnalysisException {
if (type.isDate()) {
return new DateLiteral(getTimeFormatter().plusHours(hour),
Type.DATETIME);
}
@@ -1068,7 +1068,7 @@ public class DateLiteral extends LiteralExpr {
return new DateLiteral(getTimeFormatter().plusHours(hour), type);
}
- public DateLiteral plusMinutes(int minute) {
+ public DateLiteral plusMinutes(long minute) {
if (type.isDate()) {
return new DateLiteral(getTimeFormatter().plusMinutes(minute),
Type.DATETIME);
}
@@ -1078,7 +1078,7 @@ public class DateLiteral extends LiteralExpr {
return new DateLiteral(getTimeFormatter().plusMinutes(minute), type);
}
- public DateLiteral plusSeconds(int second) {
+ public DateLiteral plusSeconds(long second) {
if (type.isDate()) {
return new DateLiteral(getTimeFormatter().plusSeconds(second),
Type.DATETIME);
}
@@ -1536,6 +1536,10 @@ public class DateLiteral extends LiteralExpr {
}
}
+ public long daynr() {
+ return calcDaynr(this.year, this.month, this.day);
+ }
+
// calculate the number of days from year 0000-00-00 to year-month-day
private long calcDaynr(long year, long month, long day) {
long delsum = 0;
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 c4c7f967d9..c9af3ed874 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
@@ -2267,4 +2267,17 @@ public class FunctionCallExpr extends Expr {
}
return fn;
}
+
+ // eg: date_floor("0001-01-01 00:00:18",interval 5 second) convert to
+ // second_floor("0001-01-01 00:00:18", 5, "0001-01-01 00:00:00");
+ public static FunctionCallExpr functionWithIntervalConvert(String
functionName, Expr str, Expr interval,
+ String timeUnitIdent) throws AnalysisException {
+ String newFunctionName = timeUnitIdent + "_" +
functionName.split("_")[1];
+ List<Expr> params = new ArrayList<>();
+ Expr defaultDatetime = new DateLiteral(0001, 01, 01, 0, 0, 0, 0,
Type.DATETIMEV2);
+ params.add(str);
+ params.add(interval);
+ params.add(defaultDatetime);
+ return new FunctionCallExpr(newFunctionName, params);
+ }
}
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/parser/LogicalPlanBuilder.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/parser/LogicalPlanBuilder.java
index bf966f574f..b4c2cf5a5c 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/parser/LogicalPlanBuilder.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/parser/LogicalPlanBuilder.java
@@ -48,6 +48,8 @@ import
org.apache.doris.nereids.DorisParser.ComplexDataTypeContext;
import org.apache.doris.nereids.DorisParser.ConstantContext;
import org.apache.doris.nereids.DorisParser.CreateRowPolicyContext;
import org.apache.doris.nereids.DorisParser.CteContext;
+import org.apache.doris.nereids.DorisParser.DateCeilContext;
+import org.apache.doris.nereids.DorisParser.DateFloorContext;
import org.apache.doris.nereids.DorisParser.Date_addContext;
import org.apache.doris.nereids.DorisParser.Date_subContext;
import org.apache.doris.nereids.DorisParser.DecimalLiteralContext;
@@ -198,26 +200,40 @@ import
org.apache.doris.nereids.trees.expressions.functions.scalar.Char;
import org.apache.doris.nereids.trees.expressions.functions.scalar.ConvertTo;
import org.apache.doris.nereids.trees.expressions.functions.scalar.CreateMap;
import
org.apache.doris.nereids.trees.expressions.functions.scalar.CreateStruct;
+import org.apache.doris.nereids.trees.expressions.functions.scalar.DayCeil;
+import org.apache.doris.nereids.trees.expressions.functions.scalar.DayFloor;
import org.apache.doris.nereids.trees.expressions.functions.scalar.DaysAdd;
import org.apache.doris.nereids.trees.expressions.functions.scalar.DaysDiff;
import org.apache.doris.nereids.trees.expressions.functions.scalar.DaysSub;
import org.apache.doris.nereids.trees.expressions.functions.scalar.ElementAt;
import
org.apache.doris.nereids.trees.expressions.functions.scalar.EncryptKeyRef;
+import org.apache.doris.nereids.trees.expressions.functions.scalar.HourCeil;
+import org.apache.doris.nereids.trees.expressions.functions.scalar.HourFloor;
import org.apache.doris.nereids.trees.expressions.functions.scalar.HoursAdd;
import org.apache.doris.nereids.trees.expressions.functions.scalar.HoursDiff;
import org.apache.doris.nereids.trees.expressions.functions.scalar.HoursSub;
+import org.apache.doris.nereids.trees.expressions.functions.scalar.MinuteCeil;
+import org.apache.doris.nereids.trees.expressions.functions.scalar.MinuteFloor;
import org.apache.doris.nereids.trees.expressions.functions.scalar.MinutesAdd;
import org.apache.doris.nereids.trees.expressions.functions.scalar.MinutesDiff;
import org.apache.doris.nereids.trees.expressions.functions.scalar.MinutesSub;
+import org.apache.doris.nereids.trees.expressions.functions.scalar.MonthCeil;
+import org.apache.doris.nereids.trees.expressions.functions.scalar.MonthFloor;
import org.apache.doris.nereids.trees.expressions.functions.scalar.MonthsAdd;
import org.apache.doris.nereids.trees.expressions.functions.scalar.MonthsDiff;
import org.apache.doris.nereids.trees.expressions.functions.scalar.MonthsSub;
+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.SecondsAdd;
import org.apache.doris.nereids.trees.expressions.functions.scalar.SecondsDiff;
import org.apache.doris.nereids.trees.expressions.functions.scalar.SecondsSub;
+import org.apache.doris.nereids.trees.expressions.functions.scalar.WeekCeil;
+import org.apache.doris.nereids.trees.expressions.functions.scalar.WeekFloor;
import org.apache.doris.nereids.trees.expressions.functions.scalar.WeeksAdd;
import org.apache.doris.nereids.trees.expressions.functions.scalar.WeeksDiff;
import org.apache.doris.nereids.trees.expressions.functions.scalar.WeeksSub;
+import org.apache.doris.nereids.trees.expressions.functions.scalar.YearCeil;
+import org.apache.doris.nereids.trees.expressions.functions.scalar.YearFloor;
import org.apache.doris.nereids.trees.expressions.functions.scalar.YearsAdd;
import org.apache.doris.nereids.trees.expressions.functions.scalar.YearsDiff;
import org.apache.doris.nereids.trees.expressions.functions.scalar.YearsSub;
@@ -1095,6 +1111,64 @@ public class LogicalPlanBuilder extends
DorisParserBaseVisitor<Object> {
+ ", supported time unit: YEAR/MONTH/DAY/HOUR/MINUTE/SECOND",
ctx);
}
+ @Override
+ public Expression visitDateFloor(DateFloorContext ctx) {
+ Expression timeStamp = (Expression) visit(ctx.timestamp);
+ Expression amount = (Expression) visit(ctx.unitsAmount);
+ if (ctx.unit == null) {
+ // use "SECOND" as unit by default
+ return new SecondFloor(timeStamp, amount);
+ }
+ Expression e = new DateTimeV2Literal(0001L, 01L, 01L, 0L, 0L, 0L, 0L);
+
+ if ("Year".equalsIgnoreCase(ctx.unit.getText())) {
+ return new YearFloor(timeStamp, amount, e);
+ } else if ("MONTH".equalsIgnoreCase(ctx.unit.getText())) {
+ return new MonthFloor(timeStamp, amount, e);
+ } else if ("WEEK".equalsIgnoreCase(ctx.unit.getText())) {
+ return new WeekFloor(timeStamp, amount, e);
+ } else if ("DAY".equalsIgnoreCase(ctx.unit.getText())) {
+ return new DayFloor(timeStamp, amount, e);
+ } else if ("Hour".equalsIgnoreCase(ctx.unit.getText())) {
+ return new HourFloor(timeStamp, amount, e);
+ } else if ("Minute".equalsIgnoreCase(ctx.unit.getText())) {
+ return new MinuteFloor(timeStamp, amount, e);
+ } else if ("Second".equalsIgnoreCase(ctx.unit.getText())) {
+ return new SecondFloor(timeStamp, amount, e);
+ }
+ throw new ParseException("Unsupported time unit: " + ctx.unit
+ + ", supported time unit:
YEAR/MONTH/WEEK/DAY/HOUR/MINUTE/SECOND", ctx);
+ }
+
+ @Override
+ public Expression visitDateCeil(DateCeilContext ctx) {
+ Expression timeStamp = (Expression) visit(ctx.timestamp);
+ Expression amount = (Expression) visit(ctx.unitsAmount);
+ if (ctx.unit == null) {
+ // use "Second" as unit by default
+ return new SecondCeil(timeStamp, amount);
+ }
+ DateTimeV2Literal e = new DateTimeV2Literal(0001L, 01L, 01L, 0L, 0L,
0L, 0L);
+
+ if ("Year".equalsIgnoreCase(ctx.unit.getText())) {
+ return new YearCeil(timeStamp, amount, e);
+ } else if ("MONTH".equalsIgnoreCase(ctx.unit.getText())) {
+ return new MonthCeil(timeStamp, amount, e);
+ } else if ("WEEK".equalsIgnoreCase(ctx.unit.getText())) {
+ return new WeekCeil(timeStamp, amount, e);
+ } else if ("DAY".equalsIgnoreCase(ctx.unit.getText())) {
+ return new DayCeil(timeStamp, amount, e);
+ } else if ("Hour".equalsIgnoreCase(ctx.unit.getText())) {
+ return new HourCeil(timeStamp, amount, e);
+ } else if ("Minute".equalsIgnoreCase(ctx.unit.getText())) {
+ return new MinuteCeil(timeStamp, amount, e);
+ } else if ("Second".equalsIgnoreCase(ctx.unit.getText())) {
+ return new SecondCeil(timeStamp, amount, e);
+ }
+ throw new ParseException("Unsupported time unit: " + ctx.unit
+ + ", supported time unit:
YEAR/MONTH/WEEK/DAY/HOUR/MINUTE/SECOND", ctx);
+ }
+
@Override
public Expression visitDoublePipes(DorisParser.DoublePipesContext ctx) {
return ParserUtils.withOrigin(ctx, () -> {
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/ExpressionEvaluator.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/ExpressionEvaluator.java
index 2964a4eeb3..a3b5fda56c 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/ExpressionEvaluator.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/ExpressionEvaluator.java
@@ -27,6 +27,7 @@ import
org.apache.doris.nereids.trees.expressions.functions.executable.DateTimeA
import
org.apache.doris.nereids.trees.expressions.functions.executable.DateTimeExtractAndTransform;
import
org.apache.doris.nereids.trees.expressions.functions.executable.ExecutableFunctions;
import
org.apache.doris.nereids.trees.expressions.functions.executable.NumericArithmetic;
+import
org.apache.doris.nereids.trees.expressions.functions.executable.TimeRoundSeries;
import org.apache.doris.nereids.trees.expressions.literal.DateLiteral;
import org.apache.doris.nereids.trees.expressions.literal.Literal;
import org.apache.doris.nereids.trees.expressions.literal.NullLiteral;
@@ -141,7 +142,8 @@ public enum ExpressionEvaluator {
ExecutableFunctions.class,
DateLiteral.class,
DateTimeArithmetic.class,
- NumericArithmetic.class
+ NumericArithmetic.class,
+ TimeRoundSeries.class
);
for (Class<?> cls : classes) {
for (Method method : cls.getDeclaredMethods()) {
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/executable/TimeRoundSeries.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/executable/TimeRoundSeries.java
index 77fd375023..cde59d8568 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/executable/TimeRoundSeries.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/functions/executable/TimeRoundSeries.java
@@ -25,10 +25,7 @@ import
org.apache.doris.nereids.trees.expressions.literal.DateTimeV2Literal;
import org.apache.doris.nereids.trees.expressions.literal.DateV2Literal;
import org.apache.doris.nereids.trees.expressions.literal.IntegerLiteral;
-import java.time.Duration;
import java.time.LocalDateTime;
-import java.time.temporal.ChronoUnit;
-import java.time.temporal.TemporalUnit;
/**
* executable functions:
@@ -48,49 +45,82 @@ public class TimeRoundSeries {
SECOND
}
- private static ChronoUnit dateEnumToUnit(DATE tag) {
+ // get it's from
be/src/vec/functions/function_datetime_floor_ceil.cpp##time_round
+ private static LocalDateTime getDateCeilOrFloor(DATE tag, LocalDateTime
date, int period, LocalDateTime origin,
+ boolean getCeil) {
+ DateTimeV2Literal dt = (DateTimeV2Literal)
DateTimeV2Literal.fromJavaDateType(date);
+ DateTimeV2Literal start = (DateTimeV2Literal)
DateTimeV2Literal.fromJavaDateType(origin);
+ long diff = 0;
+ long trivialPart = 0;
+ switch (tag) {
+ case YEAR: {
+ diff = dt.getYear() - start.getYear();
+ trivialPart = (dt.getValue() % 10000000000L) -
(start.getValue() % 10000000000L);
+ break;
+ }
+ case MONTH: {
+ diff = (dt.getYear() - start.getYear()) * 12 + (dt.getMonth()
- start.getMonth());
+ trivialPart = (dt.getValue() % 100000000L) - (start.getValue()
% 100000000L);
+ break;
+ }
+ case DAY: {
+ diff = dt.getTotalDays() - start.getTotalDays();
+ long part2 = dt.getHour() * 3600 + dt.getMinute() * 60 +
dt.getSecond();
+ long part1 = start.getHour() * 3600 + start.getMinute() * 60 +
start.getSecond();
+ trivialPart = part2 - part1;
+ break;
+ }
+ case HOUR: {
+ diff = (dt.getTotalDays() - start.getTotalDays()) * 24 +
(dt.getHour() - start.getHour());
+ trivialPart = (dt.getMinute() * 60 + dt.getSecond())
+ - (start.getMinute() * 60 + start.getSecond());
+ break;
+ }
+ case MINUTE: {
+ diff = (dt.getTotalDays() - start.getTotalDays()) * 24 * 60 +
(dt.getHour() - start.getHour()) * 60
+ + (dt.getMinute() - start.getMinute());
+ trivialPart = dt.getSecond() - start.getSecond();
+ break;
+ }
+ case SECOND: {
+ diff = (dt.getTotalDays() - start.getTotalDays()) * 24 * 60 *
60
+ + (dt.getHour() - start.getHour()) * 60 * 60
+ + (dt.getMinute() - start.getMinute()) * 60
+ + (dt.getSecond() - start.getSecond());
+ trivialPart = 0;
+ break;
+ }
+ default: {
+ return null;
+ }
+ }
+ if (getCeil) {
+ diff = diff + (trivialPart > 0 ? 1 : 0);
+ } else {
+ diff = diff - (trivialPart < 0 ? 1 : 0);
+ }
+ long deltaInsidePeriod = (diff % period + period) % period;
+ long step = diff - deltaInsidePeriod;
+ if (getCeil) {
+ step = step + (deltaInsidePeriod == 0 ? 0 : period);
+ }
switch (tag) {
case YEAR:
- return ChronoUnit.YEARS;
+ return ((DateTimeLiteral)
start.plusYears(step)).toJavaDateType();
case MONTH:
- return ChronoUnit.MONTHS;
+ return ((DateTimeLiteral)
start.plusMonths(step)).toJavaDateType();
case DAY:
- return ChronoUnit.DAYS;
+ return ((DateTimeLiteral)
start.plusDays(step)).toJavaDateType();
case HOUR:
- return ChronoUnit.HOURS;
+ return ((DateTimeLiteral)
start.plusHours(step)).toJavaDateType();
case MINUTE:
- return ChronoUnit.MINUTES;
+ return ((DateTimeLiteral)
start.plusMinutes(step)).toJavaDateType();
+ case SECOND:
+ return ((DateTimeLiteral)
start.plusSeconds(step)).toJavaDateType();
default:
- return ChronoUnit.SECONDS;
+ break;
}
- }
-
- private static LocalDateTime getDateCeilOrFloor(DATE tag, LocalDateTime
date, int period, LocalDateTime origin,
- boolean getCeil) {
- // Algorithm:
- // Firstly, get the unit distance of the two date.
- // Secondly, if the origin date is bigger than the date, subtract it
to a date before the date by unit.
- // Thirdly, re-calculate the distance of the two date.
- // Fourthly, get the ceil and floor date of the date by unit and
select the corresponding date as the answer.
-
- // handle origin > date
- TemporalUnit unit = dateEnumToUnit(tag);
- if (origin.isAfter(date)) {
- Duration duration = Duration.between(date, origin);
- long hour = Math.abs(duration.get(unit));
- long ceil = ((hour - 1) / period + 1) * period;
- origin = origin.minus(ceil, unit);
- }
-
- // get distance
- Duration duration = Duration.between(origin, date);
- long hour = Math.abs(duration.get(unit));
- long ceil = ((hour - 1) / period + 1) * period;
- long floor = hour / period * period;
- LocalDateTime floorDate = origin.plus(floor, unit);
- LocalDateTime ceilDate = origin.plus(ceil, unit);
-
- return getCeil ? ceilDate : floorDate;
+ return null;
}
/**
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/literal/DateLiteral.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/literal/DateLiteral.java
index f75e2d81da..dfb7177ebc 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/literal/DateLiteral.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/literal/DateLiteral.java
@@ -206,15 +206,15 @@ public class DateLiteral extends Literal {
return day;
}
- public Expression plusDays(int days) {
+ public Expression plusDays(long days) {
return fromJavaDateType(DateUtils.getTime(DATE_FORMATTER,
getStringValue()).plusDays(days));
}
- public Expression plusMonths(int months) {
+ public Expression plusMonths(long months) {
return fromJavaDateType(DateUtils.getTime(DATE_FORMATTER,
getStringValue()).plusMonths(months));
}
- public Expression plusYears(int years) {
+ public Expression plusYears(long years) {
return fromJavaDateType(DateUtils.getTime(DATE_FORMATTER,
getStringValue()).plusYears(years));
}
@@ -222,6 +222,34 @@ public class DateLiteral extends Literal {
return LocalDateTime.of(((int) getYear()), ((int) getMonth()), ((int)
getDay()), 0, 0, 0);
}
+ public long getTotalDays() {
+ return calculateDays(this.year, this.month, this.day);
+ }
+
+ // calculate the number of days from year 0000-00-00 to year-month-day
+ private long calculateDays(long year, long month, long day) {
+ long totalDays = 0;
+ long y = year;
+
+ if (year == 0 && month == 0) {
+ return 0;
+ }
+
+ /* Cast to int to be able to handle month == 0 */
+ totalDays = 365 * y + 31 * (month - 1) + day;
+ if (month <= 2) {
+ // No leap year
+ y--;
+ } else {
+ // This is great!!!
+ // 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12
+ // 0, 0, 3, 3, 4, 4, 5, 5, 5, 6, 7, 8
+ totalDays -= (month * 4 + 23) / 10;
+ }
+ // Every 400 year has 97 leap year, 100, 200, 300 are not leap year.
+ return totalDays + y / 4 - y / 100 + y / 400;
+ }
+
public static Expression fromJavaDateType(LocalDateTime dateTime) {
return isDateOutOfRange(dateTime)
? new NullLiteral(DateType.INSTANCE)
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 2db3dacb49..bf41c967cc 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
@@ -316,23 +316,23 @@ public class DateTimeLiteral extends DateLiteral {
return new org.apache.doris.analysis.DateLiteral(year, month, day,
hour, minute, second, Type.DATETIME);
}
- public Expression plusYears(int years) {
+ public Expression plusYears(long years) {
return fromJavaDateType(DateUtils.getTime(DATE_TIME_FORMATTER,
getStringValue()).plusYears(years));
}
- public Expression plusMonths(int months) {
+ public Expression plusMonths(long months) {
return fromJavaDateType(DateUtils.getTime(DATE_TIME_FORMATTER,
getStringValue()).plusMonths(months));
}
- public Expression plusDays(int days) {
+ public Expression plusDays(long days) {
return fromJavaDateType(DateUtils.getTime(DATE_TIME_FORMATTER,
getStringValue()).plusDays(days));
}
- public Expression plusHours(int hours) {
+ public Expression plusHours(long hours) {
return fromJavaDateType(DateUtils.getTime(DATE_TIME_FORMATTER,
getStringValue()).plusHours(hours));
}
- public Expression plusMinutes(int minutes) {
+ public Expression plusMinutes(long minutes) {
return fromJavaDateType(DateUtils.getTime(DATE_TIME_FORMATTER,
getStringValue()).plusMinutes(minutes));
}
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/literal/DateTimeV2Literal.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/literal/DateTimeV2Literal.java
index ef09399a5a..c65c921ac7 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/literal/DateTimeV2Literal.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/literal/DateTimeV2Literal.java
@@ -82,31 +82,31 @@ public class DateTimeV2Literal extends DateTimeLiteral {
}
@Override
- public Expression plusYears(int years) {
+ public Expression plusYears(long years) {
return
fromJavaDateType(DateUtils.getTime(DATE_TIME_FORMATTER_TO_MICRO_SECOND,
getStringValue())
.plusYears(years), getDataType().getScale());
}
@Override
- public Expression plusMonths(int months) {
+ public Expression plusMonths(long months) {
return
fromJavaDateType(DateUtils.getTime(DATE_TIME_FORMATTER_TO_MICRO_SECOND,
getStringValue())
.plusMonths(months), getDataType().getScale());
}
@Override
- public Expression plusDays(int days) {
+ public Expression plusDays(long days) {
return
fromJavaDateType(DateUtils.getTime(DATE_TIME_FORMATTER_TO_MICRO_SECOND,
getStringValue())
.plusDays(days), getDataType().getScale());
}
@Override
- public Expression plusHours(int hours) {
+ public Expression plusHours(long hours) {
return
fromJavaDateType(DateUtils.getTime(DATE_TIME_FORMATTER_TO_MICRO_SECOND,
getStringValue())
.plusHours(hours), getDataType().getScale());
}
@Override
- public Expression plusMinutes(int minutes) {
+ public Expression plusMinutes(long minutes) {
return
fromJavaDateType(DateUtils.getTime(DATE_TIME_FORMATTER_TO_MICRO_SECOND,
getStringValue())
.plusMinutes(minutes), getDataType().getScale());
}
@@ -117,7 +117,7 @@ public class DateTimeV2Literal extends DateTimeLiteral {
.plusSeconds(seconds), getDataType().getScale());
}
- public Expression plusMicroSeconds(int microSeconds) {
+ public Expression plusMicroSeconds(long microSeconds) {
return
fromJavaDateType(DateUtils.getTime(DATE_TIME_FORMATTER_TO_MICRO_SECOND,
getStringValue())
.plusNanos(microSeconds * 1000L), getDataType().getScale());
}
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/literal/DateV2Literal.java
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/literal/DateV2Literal.java
index 928b7ca0f2..32164c977c 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/literal/DateV2Literal.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/nereids/trees/expressions/literal/DateV2Literal.java
@@ -50,15 +50,15 @@ public class DateV2Literal extends DateLiteral {
return visitor.visitDateV2Literal(this, context);
}
- public Expression plusDays(int days) {
+ public Expression plusDays(long days) {
return fromJavaDateType(DateUtils.getTime(DATE_FORMATTER,
getStringValue()).plusDays(days));
}
- public Expression plusMonths(int months) {
+ public Expression plusMonths(long months) {
return fromJavaDateType(DateUtils.getTime(DATE_FORMATTER,
getStringValue()).plusMonths(months));
}
- public Expression plusYears(int years) {
+ public Expression plusYears(long years) {
return fromJavaDateType(DateUtils.getTime(DATE_FORMATTER,
getStringValue()).plusYears(years));
}
diff --git a/fe/fe-core/src/main/java/org/apache/doris/rewrite/FEFunctions.java
b/fe/fe-core/src/main/java/org/apache/doris/rewrite/FEFunctions.java
index bce2dd7dfa..dd1bce88cc 100755
--- a/fe/fe-core/src/main/java/org/apache/doris/rewrite/FEFunctions.java
+++ b/fe/fe-core/src/main/java/org/apache/doris/rewrite/FEFunctions.java
@@ -26,6 +26,7 @@ import org.apache.doris.analysis.LargeIntLiteral;
import org.apache.doris.analysis.LiteralExpr;
import org.apache.doris.analysis.NullLiteral;
import org.apache.doris.analysis.StringLiteral;
+import org.apache.doris.analysis.TimestampArithmeticExpr.TimeUnit;
import org.apache.doris.catalog.Type;
import org.apache.doris.common.AnalysisException;
import org.apache.doris.common.InvalidFormatException;
@@ -111,37 +112,37 @@ public class FEFunctions {
@FEFunction(name = "years_add", argTypes = { "DATETIME", "INT" },
returnType = "DATETIME")
public static DateLiteral yearsAdd(LiteralExpr date, LiteralExpr year)
throws AnalysisException {
DateLiteral dateLiteral = (DateLiteral) date;
- return dateLiteral.plusYears((int) year.getLongValue());
+ return dateLiteral.plusYears(year.getLongValue());
}
@FEFunction(name = "months_add", argTypes = { "DATETIME", "INT" },
returnType = "DATETIME")
public static DateLiteral monthsAdd(LiteralExpr date, LiteralExpr month)
throws AnalysisException {
DateLiteral dateLiteral = (DateLiteral) date;
- return dateLiteral.plusMonths((int) month.getLongValue());
+ return dateLiteral.plusMonths(month.getLongValue());
}
@FEFunction(name = "days_add", argTypes = { "DATETIME", "INT" },
returnType = "DATETIME")
public static DateLiteral daysAdd(LiteralExpr date, LiteralExpr day)
throws AnalysisException {
DateLiteral dateLiteral = (DateLiteral) date;
- return dateLiteral.plusDays((int) day.getLongValue());
+ return dateLiteral.plusDays(day.getLongValue());
}
@FEFunction(name = "hours_add", argTypes = { "DATETIME", "INT" },
returnType = "DATETIME")
public static DateLiteral hoursAdd(LiteralExpr date, LiteralExpr hour)
throws AnalysisException {
DateLiteral dateLiteral = (DateLiteral) date;
- return dateLiteral.plusHours((int) hour.getLongValue());
+ return dateLiteral.plusHours(hour.getLongValue());
}
@FEFunction(name = "minutes_add", argTypes = { "DATETIME", "INT" },
returnType = "DATETIME")
public static DateLiteral minutesAdd(LiteralExpr date, LiteralExpr minute)
throws AnalysisException {
DateLiteral dateLiteral = (DateLiteral) date;
- return dateLiteral.plusMinutes((int) minute.getLongValue());
+ return dateLiteral.plusMinutes(minute.getLongValue());
}
@FEFunction(name = "seconds_add", argTypes = { "DATETIME", "INT" },
returnType = "DATETIME")
public static DateLiteral secondsAdd(LiteralExpr date, LiteralExpr
second) throws AnalysisException {
DateLiteral dateLiteral = (DateLiteral) date;
- return dateLiteral.plusSeconds((int) second.getLongValue());
+ return dateLiteral.plusSeconds(second.getLongValue());
}
@FEFunction(name = "date_format", argTypes = { "DATETIME", "VARCHAR" },
returnType = "VARCHAR")
@@ -350,6 +351,177 @@ public class FEFunctions {
return null;
}
+ @FEFunction(name = "second_floor", argTypes = { "DATETIMEV2", "INT",
"DATETIMEV2" }, returnType = "DATETIMEV2")
+ public static DateLiteral second_floor(LiteralExpr datetime, LiteralExpr
period, LiteralExpr defaultDatetime)
+ throws AnalysisException {
+ return getFloorCeilDateLiteral(datetime, period, defaultDatetime,
false, TimeUnit.SECOND);
+ }
+
+ @FEFunction(name = "second_ceil", argTypes = { "DATETIMEV2", "INT",
"DATETIMEV2" }, returnType = "DATETIMEV2")
+ public static DateLiteral second_ceil(LiteralExpr datetime, LiteralExpr
period, LiteralExpr defaultDatetime)
+ throws AnalysisException {
+ return getFloorCeilDateLiteral(datetime, period, defaultDatetime,
true, TimeUnit.SECOND);
+ }
+
+ @FEFunction(name = "minute_floor", argTypes = { "DATETIMEV2", "INT",
"DATETIMEV2" }, returnType = "DATETIMEV2")
+ public static DateLiteral minute_floor(LiteralExpr datetime, LiteralExpr
period, LiteralExpr defaultDatetime)
+ throws AnalysisException {
+ return getFloorCeilDateLiteral(datetime, period, defaultDatetime,
false, TimeUnit.MINUTE);
+ }
+
+ @FEFunction(name = "minute_ceil", argTypes = { "DATETIMEV2", "INT",
"DATETIMEV2" }, returnType = "DATETIMEV2")
+ public static DateLiteral minute_ceil(LiteralExpr datetime, LiteralExpr
period, LiteralExpr defaultDatetime)
+ throws AnalysisException {
+ return getFloorCeilDateLiteral(datetime, period, defaultDatetime,
true, TimeUnit.MINUTE);
+ }
+
+ @FEFunction(name = "hour_floor", argTypes = { "DATETIMEV2", "INT",
"DATETIMEV2" }, returnType = "DATETIMEV2")
+ public static DateLiteral hour_floor(LiteralExpr datetime, LiteralExpr
period, LiteralExpr defaultDatetime)
+ throws AnalysisException {
+ return getFloorCeilDateLiteral(datetime, period, defaultDatetime,
false, TimeUnit.HOUR);
+ }
+
+ @FEFunction(name = "hour_ceil", argTypes = { "DATETIMEV2", "INT",
"DATETIMEV2" }, returnType = "DATETIMEV2")
+ public static DateLiteral hour_ceil(LiteralExpr datetime, LiteralExpr
period, LiteralExpr defaultDatetime)
+ throws AnalysisException {
+ return getFloorCeilDateLiteral(datetime, period, defaultDatetime,
true, TimeUnit.HOUR);
+ }
+
+ @FEFunction(name = "day_floor", argTypes = { "DATETIMEV2", "INT",
"DATETIMEV2" }, returnType = "DATETIMEV2")
+ public static DateLiteral day_floor(LiteralExpr datetime, LiteralExpr
period, LiteralExpr defaultDatetime)
+ throws AnalysisException {
+ return getFloorCeilDateLiteral(datetime, period, defaultDatetime,
false, TimeUnit.DAY);
+ }
+
+ @FEFunction(name = "day_ceil", argTypes = { "DATETIMEV2", "INT",
"DATETIMEV2" }, returnType = "DATETIMEV2")
+ public static DateLiteral day_ceil(LiteralExpr datetime, LiteralExpr
period, LiteralExpr defaultDatetime)
+ throws AnalysisException {
+ return getFloorCeilDateLiteral(datetime, period, defaultDatetime,
true, TimeUnit.DAY);
+ }
+
+ // get it's from
be/src/vec/functions/function_datetime_floor_ceil.cpp##time_round
+ public static DateLiteral getFloorCeilDateLiteral(LiteralExpr datetime,
LiteralExpr period,
+ LiteralExpr defaultDatetime, boolean isCeil, TimeUnit type) throws
AnalysisException {
+ DateLiteral dt = ((DateLiteral) datetime);
+ DateLiteral start = ((DateLiteral) defaultDatetime);
+ long periodValue = ((IntLiteral) period).getValue();
+ long diff = 0;
+ long trivialPart = 0;
+
+ switch (type) {
+ case YEAR: {
+ diff = dt.getYear() - start.getYear();
+ trivialPart = (dt.getLongValue() % 10000000000L) -
(start.getLongValue() % 10000000000L);
+ break;
+ }
+ case MONTH: {
+ diff = (dt.getYear() - start.getYear()) * 12 + (dt.getMonth()
- start.getMonth());
+ trivialPart = (dt.getLongValue() % 100000000L) -
(start.getLongValue() % 100000000L);
+ break;
+ }
+ case WEEK: {
+ diff = (dt.daynr() / 7) - (start.daynr() / 7);
+ long part2 = (dt.daynr() % 7) * 24 * 3600 + dt.getHour() *
3600 + dt.getMinute() * 60 + dt.getSecond();
+ long part1 = (start.daynr() % 7) * 24 * 3600 + start.getHour()
* 3600 + start.getMinute() * 60
+ + start.getSecond();
+ trivialPart = part2 - part1;
+ break;
+ }
+ case DAY: {
+ diff = dt.daynr() - start.daynr();
+ long part2 = dt.getHour() * 3600 + dt.getMinute() * 60 +
dt.getSecond();
+ long part1 = start.getHour() * 3600 + start.getMinute() * 60 +
start.getSecond();
+ trivialPart = part2 - part1;
+ break;
+ }
+ case HOUR: {
+ diff = (dt.daynr() - start.daynr()) * 24 + (dt.getHour() -
start.getHour());
+ trivialPart = (dt.getMinute() * 60 + dt.getSecond()) -
(start.getMinute() * 60 + start.getSecond());
+ break;
+ }
+ case MINUTE: {
+ diff = (dt.daynr() - start.daynr()) * 24 * 60 + (dt.getHour()
- start.getHour()) * 60
+ + (dt.getMinute() - start.getMinute());
+ trivialPart = dt.getSecond() - start.getSecond();
+ break;
+ }
+ case SECOND: {
+ diff = (dt.daynr() - start.daynr()) * 24 * 60 * 60 +
(dt.getHour() - start.getHour()) * 60 * 60
+ + (dt.getMinute() - start.getMinute()) * 60 +
(dt.getSecond() - start.getSecond());
+ trivialPart = 0;
+ break;
+ }
+ default:
+ break;
+ }
+
+ if (isCeil) {
+ diff = diff + (trivialPart > 0 ? 1 : 0);
+ } else {
+ diff = diff - (trivialPart < 0 ? 1 : 0);
+ }
+ long deltaInsidePeriod = (diff % periodValue + periodValue) %
periodValue;
+ long step = diff - deltaInsidePeriod;
+ if (isCeil) {
+ step = step + (deltaInsidePeriod == 0 ? 0 : periodValue);
+ }
+ switch (type) {
+ case YEAR:
+ return start.plusYears(step);
+ case MONTH:
+ return start.plusMonths(step);
+ case WEEK:
+ return start.plusDays(step * 7);
+ case DAY:
+ return start.plusDays(step);
+ case HOUR:
+ return start.plusHours(step);
+ case MINUTE:
+ return start.plusMinutes(step);
+ case SECOND:
+ return start.plusSeconds(step);
+ default:
+ break;
+ }
+ return null;
+ }
+
+ @FEFunction(name = "week_floor", argTypes = { "DATETIMEV2", "INT",
"DATETIMEV2" }, returnType = "DATETIMEV2")
+ public static DateLiteral week_floor(LiteralExpr datetime, LiteralExpr
period, LiteralExpr defaultDatetime)
+ throws AnalysisException {
+ return getFloorCeilDateLiteral(datetime, period, defaultDatetime,
false, TimeUnit.WEEK);
+ }
+
+ @FEFunction(name = "week_ceil", argTypes = { "DATETIMEV2", "INT",
"DATETIMEV2" }, returnType = "DATETIMEV2")
+ public static DateLiteral week_ceil(LiteralExpr datetime, LiteralExpr
period, LiteralExpr defaultDatetime)
+ throws AnalysisException {
+ return getFloorCeilDateLiteral(datetime, period, defaultDatetime,
true, TimeUnit.WEEK);
+ }
+
+ @FEFunction(name = "month_floor", argTypes = { "DATETIMEV2", "INT",
"DATETIMEV2" }, returnType = "DATETIMEV2")
+ public static DateLiteral month_floor(LiteralExpr datetime, LiteralExpr
period, LiteralExpr defaultDatetime)
+ throws AnalysisException {
+ return getFloorCeilDateLiteral(datetime, period, defaultDatetime,
false, TimeUnit.MONTH);
+ }
+
+ @FEFunction(name = "month_ceil", argTypes = { "DATETIMEV2", "INT",
"DATETIMEV2" }, returnType = "DATETIMEV2")
+ public static DateLiteral month_ceil(LiteralExpr datetime, LiteralExpr
period, LiteralExpr defaultDatetime)
+ throws AnalysisException {
+ return getFloorCeilDateLiteral(datetime, period, defaultDatetime,
true, TimeUnit.MONTH);
+ }
+
+ @FEFunction(name = "year_floor", argTypes = { "DATETIMEV2", "INT",
"DATETIMEV2" }, returnType = "DATETIMEV2")
+ public static DateLiteral year_floor(LiteralExpr datetime, LiteralExpr
period, LiteralExpr defaultDatetime)
+ throws AnalysisException {
+ return getFloorCeilDateLiteral(datetime, period, defaultDatetime,
false, TimeUnit.YEAR);
+ }
+
+ @FEFunction(name = "year_ceil", argTypes = { "DATETIMEV2", "INT",
"DATETIMEV2" }, returnType = "DATETIMEV2")
+ public static DateLiteral year_ceil(LiteralExpr datetime, LiteralExpr
period, LiteralExpr defaultDatetime)
+ throws AnalysisException {
+ return getFloorCeilDateLiteral(datetime, period, defaultDatetime,
true, TimeUnit.YEAR);
+ }
+
@FEFunction(name = "date_trunc", argTypes = {"DATETIME", "VARCHAR"},
returnType = "DATETIME")
public static DateLiteral dateTrunc(LiteralExpr date, LiteralExpr
truncate) {
if (date.getType().isDateLike()) {
diff --git a/gensrc/script/doris_builtins_functions.py
b/gensrc/script/doris_builtins_functions.py
index b4a50f6866..c0bec45f35 100644
--- a/gensrc/script/doris_builtins_functions.py
+++ b/gensrc/script/doris_builtins_functions.py
@@ -890,9 +890,11 @@ visible_functions = {
[['to_monday'], 'DATE', ['DATE'], 'ALWAYS_NULLABLE'],
[['to_days'], 'INT', ['DATE'], 'ALWAYS_NULLABLE'],
- [['date_trunc'], 'DATETIME', ['DATETIME', 'VARCHAR'],
'ALWAYS_NULLABLE'],
+ [['date_floor'], 'DATETIMEV2', ['DATETIMEV2', 'INT'],
'ALWAYS_NULLABLE'],
+ [['date_ceil'], 'DATETIMEV2', ['DATETIMEV2', 'INT'],
'ALWAYS_NULLABLE'],
[['date_trunc'], 'DATETIMEV2', ['DATETIMEV2', 'VARCHAR'],
'ALWAYS_NULLABLE'],
+ [['date_trunc'], 'DATETIME', ['DATETIME', 'VARCHAR'],
'ALWAYS_NULLABLE'],
[['year'], 'SMALLINT', ['DATETIME'], 'ALWAYS_NULLABLE'],
[['month'], 'TINYINT', ['DATETIME'], 'ALWAYS_NULLABLE'],
diff --git
a/regression-test/data/query_p0/sql_functions/datetime_functions/test_date_floor_ceil.out
b/regression-test/data/query_p0/sql_functions/datetime_functions/test_date_floor_ceil.out
new file mode 100644
index 0000000000..247a44b173
--- /dev/null
+++
b/regression-test/data/query_p0/sql_functions/datetime_functions/test_date_floor_ceil.out
@@ -0,0 +1,37 @@
+-- This file is automatically generated. You should know what you did if you
want to edit this
+-- !sql1 --
+2023-07-14T10:51:10
+
+-- !sql2 --
+2023-07-14T10:50
+
+-- !sql3 --
+2023-07-14T09:00
+
+-- !sql4 --
+2023-07-10T00:00
+
+-- !sql5 --
+2023-07-01T00:00
+
+-- !sql6 --
+2021-01-01T00:00
+
+-- !sql7 --
+2023-07-14T10:51:15
+
+-- !sql8 --
+2023-07-14T10:55
+
+-- !sql9 --
+2023-07-14T14:00
+
+-- !sql10 --
+2023-07-15T00:00
+
+-- !sql11 --
+2023-12-01T00:00
+
+-- !sql12 --
+2026-01-01T00:00
+
diff --git
a/regression-test/suites/query_p0/sql_functions/datetime_functions/test_date_floor_ceil.groovy
b/regression-test/suites/query_p0/sql_functions/datetime_functions/test_date_floor_ceil.groovy
new file mode 100644
index 0000000000..5d374c8fba
--- /dev/null
+++
b/regression-test/suites/query_p0/sql_functions/datetime_functions/test_date_floor_ceil.groovy
@@ -0,0 +1,36 @@
+// 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_date_floor_ceil") {
+ sql "set enable_nereids_planner=true;"
+ sql "set enable_fallback_to_original_planner=false;"
+ sql "set enable_fold_constant_by_be=false;"
+
+ qt_sql1 """select date_floor("2023-07-14 10:51:11",interval 5 second); """
+ qt_sql2 """select date_floor("2023-07-14 10:51:00",interval 5 minute); """
+ qt_sql3 """select date_floor("2023-07-14 10:51:00",interval 5 hour); """
+ qt_sql4 """select date_floor("2023-07-14 10:51:00",interval 5 day); """
+ qt_sql5 """select date_floor("2023-07-14 10:51:00",interval 5 month); """
+ qt_sql6 """select date_floor("2023-07-14 10:51:00",interval 5 year); """
+
+ qt_sql7 """select date_ceil("2023-07-14 10:51:11",interval 5 second); """
+ qt_sql8 """select date_ceil("2023-07-14 10:51:00",interval 5 minute); """
+ qt_sql9 """select date_ceil("2023-07-14 10:51:00",interval 5 hour); """
+ qt_sql10 """select date_ceil("2023-07-14 10:51:00",interval 5 day); """
+ qt_sql11 """select date_ceil("2023-07-14 10:51:00",interval 5 month); """
+ qt_sql12 """select date_ceil("2023-07-14 10:51:00",interval 5 year); """
+}
\ No newline at end of file
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
