You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@doris.apache.org by mo...@apache.org on 2023/01/18 17:05:35 UTC
[doris] branch master updated: [enhancement](Nereids) add timestampadd, timestampdiff functions (#16072)
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 d8f598eeab [enhancement](Nereids) add timestampadd, timestampdiff functions (#16072)
d8f598eeab is described below
commit d8f598eeabb9db4ac1805879466b2887d41dd873
Author: 谢健 <ji...@gmail.com>
AuthorDate: Thu Jan 19 01:05:25 2023 +0800
[enhancement](Nereids) add timestampadd, timestampdiff functions (#16072)
---
.../antlr4/org/apache/doris/nereids/DorisParser.g4 | 8 +++-
.../doris/nereids/parser/LogicalPlanBuilder.java | 31 +++++++++++++-
.../data/nereids_syntax_p0/test_date_add.out | 48 ++++++++++------------
.../data/nereids_syntax_p0/test_date_sub.out | 21 ++++++++++
.../suites/nereids_syntax_p0/test_date_add.groovy | 19 ++++-----
.../suites/nereids_syntax_p0/test_date_sub.groovy | 10 +++++
6 files changed, 97 insertions(+), 40 deletions(-)
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 46bc1e48aa..89b22431af 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
@@ -286,7 +286,13 @@ primaryExpression
startTimestamp=valueExpression COMMA
endTimestamp=valueExpression
RIGHT_PAREN #timestampdiff
- | name=(TIMESTAMPADD | ADDDATE | DAYS_ADD | DATE_ADD)
+ | name=(TIMESTAMPADD | DATEADD)
+ LEFT_PAREN
+ unit=datetimeUnit COMMA
+ startTimestamp=valueExpression COMMA
+ endTimestamp=valueExpression
+ RIGHT_PAREN #timestampadd
+ | name =(ADDDATE | DAYS_ADD | DATE_ADD)
LEFT_PAREN
timestamp=valueExpression COMMA
(INTERVAL unitsAmount=valueExpression unit=datetimeUnit
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 625716a6e5..691a6c286b 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
@@ -90,6 +90,7 @@ import org.apache.doris.nereids.DorisParser.SystemVariableContext;
import org.apache.doris.nereids.DorisParser.TableAliasContext;
import org.apache.doris.nereids.DorisParser.TableNameContext;
import org.apache.doris.nereids.DorisParser.TableValuedFunctionContext;
+import org.apache.doris.nereids.DorisParser.TimestampaddContext;
import org.apache.doris.nereids.DorisParser.TimestampdiffContext;
import org.apache.doris.nereids.DorisParser.TvfPropertyContext;
import org.apache.doris.nereids.DorisParser.TvfPropertyItemContext;
@@ -164,6 +165,7 @@ 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.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.YearsAdd;
import org.apache.doris.nereids.trees.expressions.functions.scalar.YearsDiff;
@@ -754,6 +756,8 @@ public class LogicalPlanBuilder extends DorisParserBaseVisitor<Object> {
return new YearsDiff(end, start);
} else if ("MONTH".equalsIgnoreCase(unit)) {
return new MonthsDiff(end, start);
+ } else if ("WEEK".equalsIgnoreCase(unit)) {
+ return new WeeksDiff(end, start);
} else if ("DAY".equalsIgnoreCase(unit)) {
return new DaysDiff(end, start);
} else if ("HOUR".equalsIgnoreCase(unit)) {
@@ -764,7 +768,32 @@ public class LogicalPlanBuilder extends DorisParserBaseVisitor<Object> {
return new SecondsDiff(end, start);
}
throw new ParseException("Unsupported time stamp diff time unit: " + unit
- + ", supported time unit: YEAR/MONTH/DAY/HOUR/MINUTE/SECOND", ctx);
+ + ", supported time unit: YEAR/MONTH/WEEK/DAY/HOUR/MINUTE/SECOND", ctx);
+
+ }
+
+ @Override
+ public Expression visitTimestampadd(TimestampaddContext ctx) {
+ Expression start = (Expression) visit(ctx.startTimestamp);
+ Expression end = (Expression) visit(ctx.endTimestamp);
+ String unit = ctx.unit.getText();
+ if ("YEAR".equalsIgnoreCase(unit)) {
+ return new YearsAdd(end, start);
+ } else if ("MONTH".equalsIgnoreCase(unit)) {
+ return new MonthsAdd(end, start);
+ } else if ("WEEK".equalsIgnoreCase(unit)) {
+ return new WeeksAdd(end, start);
+ } else if ("DAY".equalsIgnoreCase(unit)) {
+ return new DaysAdd(end, start);
+ } else if ("HOUR".equalsIgnoreCase(unit)) {
+ return new HoursAdd(end, start);
+ } else if ("MINUTE".equalsIgnoreCase(unit)) {
+ return new MinutesAdd(end, start);
+ } else if ("SECOND".equalsIgnoreCase(unit)) {
+ return new SecondsAdd(end, start);
+ }
+ throw new ParseException("Unsupported time stamp add time unit: " + unit
+ + ", supported time unit: YEAR/MONTH/WEEK/DAY/HOUR/MINUTE/SECOND", ctx);
}
diff --git a/regression-test/data/nereids_syntax_p0/test_date_add.out b/regression-test/data/nereids_syntax_p0/test_date_add.out
index 4754e015d8..5189522ae5 100644
--- a/regression-test/data/nereids_syntax_p0/test_date_add.out
+++ b/regression-test/data/nereids_syntax_p0/test_date_add.out
@@ -1,31 +1,4 @@
-- This file is automatically generated. You should know what you did if you want to edit this
--- !select --
-2020-01-03
-
--- !select --
-2020-01-03
-
--- !select --
-2020-01-03T00:00
-
--- !select --
-2020-01-03T00:00
-
--- !select --
-2020-01-01T02:00
-
--- !select --
-2020-01-01T02:00
-
--- !select --
-2020-01-01T02:00
-
--- !select --
-2020-01-01T02:00
-
--- !select --
-2022-01-01T00:00
-
-- !select --
2020-01-01T02:00
@@ -83,3 +56,24 @@
-- !select --
2019-12-31T23:59:56
+-- !sql --
+2004-02-01T00:00
+
+-- !sql --
+2003-03-01T00:00
+
+-- !sql --
+2003-02-08T00:00
+
+-- !sql --
+2003-02-02T00:00
+
+-- !sql --
+2003-02-01T01:00
+
+-- !sql --
+2003-02-01T00:01
+
+-- !sql --
+2003-02-01T00:00:01
+
diff --git a/regression-test/data/nereids_syntax_p0/test_date_sub.out b/regression-test/data/nereids_syntax_p0/test_date_sub.out
index cbaa9658a1..2b4d86dad8 100644
--- a/regression-test/data/nereids_syntax_p0/test_date_sub.out
+++ b/regression-test/data/nereids_syntax_p0/test_date_sub.out
@@ -89,3 +89,24 @@
-- !select --
2020-01-01T00:00:04
+-- !sql --
+3
+
+-- !sql --
+-1
+
+-- !sql --
+128885
+
+-- !sql --
+7689600
+
+-- !sql --
+2136
+
+-- !sql --
+89
+
+-- !sql --
+12
+
diff --git a/regression-test/suites/nereids_syntax_p0/test_date_add.groovy b/regression-test/suites/nereids_syntax_p0/test_date_add.groovy
index 8eb716ac36..1c9ed73278 100644
--- a/regression-test/suites/nereids_syntax_p0/test_date_add.groovy
+++ b/regression-test/suites/nereids_syntax_p0/test_date_add.groovy
@@ -20,17 +20,6 @@ suite("test_date_add") {
sql "set enable_fallback_to_original_planner=false"
sql "set enable_fold_constant_by_be=false"
- qt_select "select TIMESTAMPADD(cast('2020-01-01' as date), interval 2 day)"
- qt_select "select TIMESTAMPADD(cast('2020-01-01' as datev2),interval 2 day)"
- qt_select "select TIMESTAMPADD(cast('2020-01-01' as datetime), interval 2 day)"
- qt_select "select TIMESTAMPADD(cast('2020-01-01' as datetimev2), interval 2 day)"
-
- qt_select "select TIMESTAMPADD(cast('2020-01-01' as date), interval 2 hour)"
- qt_select "select TIMESTAMPADD(cast('2020-01-01' as datev2),interval 2 hour)"
- qt_select "select TIMESTAMPADD(cast('2020-01-01' as datetime), interval 2 hour)"
- qt_select "select TIMESTAMPADD(cast('2020-01-01' as datetimev2), interval 2 hour)"
- qt_select "select TIMESTAMPADD('2020-01-01' , interval 2 year)"
-
qt_select "SELECT DAYS_ADD('2020-01-01', interval 2 hour)"
qt_select "SELECT DAYS_ADD('2020-01-01', interval 2 minute)"
qt_select "SELECT DAYS_ADD('2020-01-01', interval 2 second)"
@@ -56,4 +45,12 @@ suite("test_date_add") {
qt_select "SELECT SECONDS_ADD('2020-01-01', 2)"
qt_select "SELECT SECONDS_ADD('2020-01-01', -4)"
+
+ qt_sql """ SELECT TIMESTAMPADD(YEAR,1,'2003-02-01'); """
+ qt_sql """ SELECT TIMESTAMPADD(MONTH,1,'2003-02-01'); """
+ qt_sql """ SELECT TIMESTAMPADD(WEEK,1,'2003-02-01'); """
+ qt_sql """ SELECT TIMESTAMPADD(DAY,1,'2003-02-01'); """
+ qt_sql """ SELECT TIMESTAMPADD(HOUR,1,'2003-02-01'); """
+ qt_sql """ SELECT TIMESTAMPADD(MINUTE,1,'2003-02-01'); """
+ qt_sql """ SELECT TIMESTAMPADD(SECOND,1,'2003-02-01'); """
}
\ No newline at end of file
diff --git a/regression-test/suites/nereids_syntax_p0/test_date_sub.groovy b/regression-test/suites/nereids_syntax_p0/test_date_sub.groovy
index 2483a940c9..7968b70dee 100644
--- a/regression-test/suites/nereids_syntax_p0/test_date_sub.groovy
+++ b/regression-test/suites/nereids_syntax_p0/test_date_sub.groovy
@@ -58,4 +58,14 @@ suite("test_date_sub") {
qt_select "SELECT SECONDS_SUB('2020-01-01', 2)"
qt_select "SELECT SECONDS_SUB('2020-01-01', -4)"
+
+ // TIMESTAMPDIFF
+ qt_sql """ SELECT TIMESTAMPDIFF(MONTH,'2003-02-01','2003-05-01') """
+ qt_sql """ SELECT TIMESTAMPDIFF(YEAR,'2002-05-01','2001-01-01') """
+ qt_sql """ SELECT TIMESTAMPDIFF(MINUTE,'2003-02-01','2003-05-01 12:05:55') """
+ qt_sql """ SELECT TIMESTAMPDIFF(SECOND,'2003-02-01','2003-05-01') """
+ qt_sql """ SELECT TIMESTAMPDIFF(HOUR,'2003-02-01','2003-05-01') """
+ qt_sql """ SELECT TIMESTAMPDIFF(DAY,'2003-02-01','2003-05-01') """
+ qt_sql """ SELECT TIMESTAMPDIFF(WEEK,'2003-02-01','2003-05-01') """
+
}
\ No newline at end of file
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org