This is an automated email from the ASF dual-hosted git repository. kxiao pushed a commit to branch branch-2.0 in repository https://gitbox.apache.org/repos/asf/doris.git
commit 79f9ecb7eb360a1e74384a9557cf8f92df9c1609 Author: Liqf <[email protected]> AuthorDate: Tue Sep 19 18:41:59 2023 +0800 [Imporve](UNIX_TIMESTAMP) UNIX_TIMESTAMP func support 'yyyy-MM-dd HH:mm:ss' format (#24561) UNIX_TIMESTAMP function data format parameter supports 'yyyy-MM-dd HH:mm:ss' The implementation is the same as the date_format function before: ```sql mysql> select UNIX_TIMESTAMP('2023-09-18 00:00:00','yyyy-MM-dd HH:mm:ss'); +--------------------------------------------------------------+ | unix_timestamp('2023-09-18 00:00:00', 'yyyy-MM-dd HH:mm:ss') | +--------------------------------------------------------------+ | NULL | +--------------------------------------------------------------+ 1 row in set (0.04 sec) ``` now: ```sql mysql> select UNIX_TIMESTAMP('2023-09-18 00:00:00','yyyy-MM-dd HH:mm:ss'); +------------+ | 1694966400 | +------------+ | 1694966400 | +------------+ 1 row in set (0.01 sec) ``` --- .../java/org/apache/doris/analysis/FunctionCallExpr.java | 3 ++- .../rules/expression/rules/SupportJavaDateFormatter.java | 14 ++++++++++++++ .../datetime_functions/test_date_function.out | 3 +++ .../datetime_functions/test_date_function.groovy | 9 +++++++++ 4 files changed, 28 insertions(+), 1 deletion(-) 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 86ba7b243d..5496198cfd 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 @@ -1580,7 +1580,8 @@ public class FunctionCallExpr extends Expr { } if (fnName.getFunction().equalsIgnoreCase("from_unixtime") - || fnName.getFunction().equalsIgnoreCase("date_format")) { + || fnName.getFunction().equalsIgnoreCase("date_format") + || fnName.getFunction().equalsIgnoreCase("unix_timestamp")) { // if has only one child, it has default time format: yyyy-MM-dd HH:mm:ss.SSSSSS if (children.size() > 1) { final StringLiteral fmtLiteral = (StringLiteral) children.get(1); diff --git a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/expression/rules/SupportJavaDateFormatter.java b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/expression/rules/SupportJavaDateFormatter.java index bf3a698887..17f4b7d239 100644 --- a/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/expression/rules/SupportJavaDateFormatter.java +++ b/fe/fe-core/src/main/java/org/apache/doris/nereids/rules/expression/rules/SupportJavaDateFormatter.java @@ -22,6 +22,7 @@ import org.apache.doris.nereids.rules.expression.ExpressionRewriteContext; import org.apache.doris.nereids.trees.expressions.Expression; import org.apache.doris.nereids.trees.expressions.functions.scalar.DateFormat; import org.apache.doris.nereids.trees.expressions.functions.scalar.FromUnixtime; +import org.apache.doris.nereids.trees.expressions.functions.scalar.UnixTimestamp; import org.apache.doris.nereids.trees.expressions.literal.Literal; import org.apache.doris.nereids.trees.expressions.literal.VarcharLiteral; @@ -59,6 +60,19 @@ public class SupportJavaDateFormatter extends AbstractExpressionRewriteRule { return fromUnixtime; } + @Override + public Expression visitUnixTimestamp(UnixTimestamp unixTimestamp, ExpressionRewriteContext context) { + Expression expr = super.visitUnixTimestamp(unixTimestamp, context); + if (!(expr instanceof UnixTimestamp)) { + return expr; + } + unixTimestamp = (UnixTimestamp) expr; + if (unixTimestamp.arity() > 1) { + return translateJavaFormatter(unixTimestamp, 1); + } + return unixTimestamp; + } + private Expression translateJavaFormatter(Expression function, int formatterIndex) { Expression formatterExpr = function.getArgument(formatterIndex); Expression newFormatterExpr = translateJavaFormatter(formatterExpr); 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 abfeacc037..6e9ca29f3f 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 @@ -665,3 +665,6 @@ true -- !sql -- 2023-08-17T17:41:18 +-- !sql -- +1694966400 1694966400 + 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 1ca3124e4c..be2668512d 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 @@ -717,4 +717,13 @@ suite("test_date_function") { assertFalse(res.contains("date_trunc")) qt_sql """ select date_add("2023-08-17T01:41:18Z", interval 8 hour) """ + + qt_sql """ + SELECT + UNIX_TIMESTAMP(a, '%Y-%c-%d %H:%i:%s') AS a, + UNIX_TIMESTAMP(a, 'yyyy-MM-dd HH:mm:ss') as b + FROM + ( + SELECT FROM_UNIXTIME(UNIX_TIMESTAMP('20230918', '%Y%m%d'), 'yyyy-MM-dd HH:mm:ss') AS `a` + )t """ } --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
