You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@druid.apache.org by gi...@apache.org on 2019/05/21 15:05:48 UTC
[incubator-druid] branch master updated: Add TIMESTAMPDIFF sql
support (#7695)
This is an automated email from the ASF dual-hosted git repository.
gian pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/incubator-druid.git
The following commit(s) were added to refs/heads/master by this push:
new dd7dace Add TIMESTAMPDIFF sql support (#7695)
dd7dace is described below
commit dd7dace70a26a6fdcf5617957f508b6fe7e176bc
Author: Xue Yu <27...@qq.com>
AuthorDate: Tue May 21 23:05:38 2019 +0800
Add TIMESTAMPDIFF sql support (#7695)
* add timestampdiff sql support
* feedback address
---
.../apache/druid/java/util/common/DateTimes.java | 9 ++++
.../java/org/apache/druid/math/expr/Function.java | 29 ++++++++++++
docs/content/querying/sql.md | 3 +-
.../expression/builtin/CastOperatorConversion.java | 8 ++++
.../builtin/TimeArithmeticOperatorConversion.java | 51 +++++++++++++++++-----
.../sql/calcite/planner/DruidConvertletTable.java | 1 +
.../apache/druid/sql/calcite/CalciteQueryTest.java | 44 +++++++++++++++++++
7 files changed, 132 insertions(+), 13 deletions(-)
diff --git a/core/src/main/java/org/apache/druid/java/util/common/DateTimes.java b/core/src/main/java/org/apache/druid/java/util/common/DateTimes.java
index de1fc40..c1718bd 100644
--- a/core/src/main/java/org/apache/druid/java/util/common/DateTimes.java
+++ b/core/src/main/java/org/apache/druid/java/util/common/DateTimes.java
@@ -23,6 +23,7 @@ import io.netty.util.SuppressForbidden;
import org.joda.time.Chronology;
import org.joda.time.DateTime;
import org.joda.time.DateTimeZone;
+import org.joda.time.Months;
import org.joda.time.chrono.ISOChronology;
import org.joda.time.format.DateTimeFormatter;
import org.joda.time.format.ISODateTimeFormat;
@@ -146,6 +147,14 @@ public final class DateTimes
return dt1.compareTo(dt2) < 0 ? dt1 : dt2;
}
+ public static int subMonths(long timestamp1, long timestamp2, DateTimeZone timeZone)
+ {
+ DateTime time1 = new DateTime(timestamp1, timeZone);
+ DateTime time2 = new DateTime(timestamp2, timeZone);
+
+ return Months.monthsBetween(time1, time2).getMonths();
+ }
+
private DateTimes()
{
}
diff --git a/core/src/main/java/org/apache/druid/math/expr/Function.java b/core/src/main/java/org/apache/druid/math/expr/Function.java
index 31cdd8e..14aa44b 100644
--- a/core/src/main/java/org/apache/druid/math/expr/Function.java
+++ b/core/src/main/java/org/apache/druid/math/expr/Function.java
@@ -24,6 +24,7 @@ import org.apache.druid.java.util.common.DateTimes;
import org.apache.druid.java.util.common.IAE;
import org.apache.druid.java.util.common.StringUtils;
import org.joda.time.DateTime;
+import org.joda.time.DateTimeZone;
import org.joda.time.format.DateTimeFormat;
import java.math.BigDecimal;
@@ -1424,4 +1425,32 @@ interface Function
}
}
+ class SubMonthFunc implements Function
+ {
+ @Override
+ public String name()
+ {
+ return "subtract_months";
+ }
+
+ @Override
+ public ExprEval apply(List<Expr> args, Expr.ObjectBinding bindings)
+ {
+ if (args.size() != 3) {
+ throw new IAE("Function[%s] needs 3 arguments", name());
+ }
+
+ Long left = args.get(0).eval(bindings).asLong();
+ Long right = args.get(1).eval(bindings).asLong();
+ DateTimeZone timeZone = DateTimes.inferTzFromString(args.get(2).eval(bindings).asString());
+
+ if (left == null || right == null) {
+ return ExprEval.of(null);
+ } else {
+ return ExprEval.of(DateTimes.subMonths(right, left, timeZone));
+ }
+
+ }
+ }
+
}
diff --git a/docs/content/querying/sql.md b/docs/content/querying/sql.md
index 169fa47..5b6e308 100644
--- a/docs/content/querying/sql.md
+++ b/docs/content/querying/sql.md
@@ -230,6 +230,7 @@ over the connection time zone.
|`FLOOR(timestamp_expr TO <unit>)`|Rounds down a timestamp, returning it as a new timestamp. Unit can be SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or YEAR.|
|`CEIL(timestamp_expr TO <unit>)`|Rounds up a timestamp, returning it as a new timestamp. Unit can be SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or YEAR.|
|`TIMESTAMPADD(<unit>, <count>, <timestamp>)`|Equivalent to `timestamp + count * INTERVAL '1' UNIT`.|
+|`TIMESTAMPDIFF(<unit>, <timestamp1>, <timestamp2>)`|Returns the (signed) number of `unit` between `timestamp1` and `timestamp2`. Unit can be SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or YEAR.|
|`timestamp_expr { + | - } <interval_expr>`|Add or subtract an amount of time from a timestamp. interval_expr can include interval literals like `INTERVAL '2' HOUR`, and may include interval arithmetic as well. This operator treats days as uniformly 86400 seconds long, and does not take into account daylight savings time. To account for daylight savings time, use TIME_SHIFT instead.|
### Comparison operators
@@ -744,4 +745,4 @@ Broker will emit the following metrics for SQL.
## Authorization Permissions
-Please see [Defining SQL permissions](../development/extensions-core/druid-basic-security.html#sql-permissions) for information on what permissions are needed for making SQL queries in a secured cluster.
\ No newline at end of file
+Please see [Defining SQL permissions](../development/extensions-core/druid-basic-security.html#sql-permissions) for information on what permissions are needed for making SQL queries in a secured cluster.
diff --git a/sql/src/main/java/org/apache/druid/sql/calcite/expression/builtin/CastOperatorConversion.java b/sql/src/main/java/org/apache/druid/sql/calcite/expression/builtin/CastOperatorConversion.java
index 0c2fd95..c9c752e 100644
--- a/sql/src/main/java/org/apache/druid/sql/calcite/expression/builtin/CastOperatorConversion.java
+++ b/sql/src/main/java/org/apache/druid/sql/calcite/expression/builtin/CastOperatorConversion.java
@@ -66,6 +66,14 @@ public class CastOperatorConversion implements SqlOperatorConversion
builder.put(SqlTypeName.TIMESTAMP, ExprType.LONG);
builder.put(SqlTypeName.DATE, ExprType.LONG);
+ for (SqlTypeName type : SqlTypeName.DAY_INTERVAL_TYPES) {
+ builder.put(type, ExprType.LONG);
+ }
+
+ for (SqlTypeName type : SqlTypeName.YEAR_INTERVAL_TYPES) {
+ builder.put(type, ExprType.LONG);
+ }
+
EXPRESSION_TYPES = builder.build();
}
diff --git a/sql/src/main/java/org/apache/druid/sql/calcite/expression/builtin/TimeArithmeticOperatorConversion.java b/sql/src/main/java/org/apache/druid/sql/calcite/expression/builtin/TimeArithmeticOperatorConversion.java
index 9175855..815cb7e 100644
--- a/sql/src/main/java/org/apache/druid/sql/calcite/expression/builtin/TimeArithmeticOperatorConversion.java
+++ b/sql/src/main/java/org/apache/druid/sql/calcite/expression/builtin/TimeArithmeticOperatorConversion.java
@@ -70,44 +70,71 @@ public abstract class TimeArithmeticOperatorConversion implements SqlOperatorCon
throw new IAE("Expected 2 args, got %s", operands.size());
}
- final RexNode timeRexNode = operands.get(0);
- final RexNode shiftRexNode = operands.get(1);
+ final RexNode leftRexNode = operands.get(0);
+ final RexNode rightRexNode = operands.get(1);
- final DruidExpression timeExpr = Expressions.toDruidExpression(plannerContext, rowSignature, timeRexNode);
- final DruidExpression shiftExpr = Expressions.toDruidExpression(plannerContext, rowSignature, shiftRexNode);
+ final DruidExpression leftExpr = Expressions.toDruidExpression(plannerContext, rowSignature, leftRexNode);
+ final DruidExpression rightExpr = Expressions.toDruidExpression(plannerContext, rowSignature, rightRexNode);
- if (timeExpr == null || shiftExpr == null) {
+ if (leftExpr == null || rightExpr == null) {
return null;
}
- if (shiftRexNode.getType().getFamily() == SqlTypeFamily.INTERVAL_YEAR_MONTH) {
+ if (rightRexNode.getType().getFamily() == SqlTypeFamily.INTERVAL_YEAR_MONTH) {
// timestamp_expr { + | - } <interval_expr> (year-month interval)
// Period is a value in months.
return DruidExpression.fromExpression(
DruidExpression.functionCall(
"timestamp_shift",
- timeExpr,
- shiftExpr.map(
+ leftExpr,
+ rightExpr.map(
simpleExtraction -> null,
expression -> StringUtils.format("concat('P', %s, 'M')", expression)
),
DruidExpression.fromExpression(DruidExpression.numberLiteral(direction > 0 ? 1 : -1))
)
);
- } else if (shiftRexNode.getType().getFamily() == SqlTypeFamily.INTERVAL_DAY_TIME) {
+ } else if (rightRexNode.getType().getFamily() == SqlTypeFamily.INTERVAL_DAY_TIME) {
// timestamp_expr { + | - } <interval_expr> (day-time interval)
// Period is a value in milliseconds. Ignore time zone.
return DruidExpression.fromExpression(
StringUtils.format(
"(%s %s %s)",
- timeExpr.getExpression(),
+ leftExpr.getExpression(),
direction > 0 ? "+" : "-",
- shiftExpr.getExpression()
+ rightExpr.getExpression()
)
);
+ } else if ((leftRexNode.getType().getFamily() == SqlTypeFamily.TIMESTAMP ||
+ leftRexNode.getType().getFamily() == SqlTypeFamily.DATE) &&
+ (rightRexNode.getType().getFamily() == SqlTypeFamily.TIMESTAMP ||
+ rightRexNode.getType().getFamily() == SqlTypeFamily.DATE)) {
+ // Calcite represents both TIMESTAMP - INTERVAL and TIMESTAMPDIFF (TIMESTAMP - TIMESTAMP)
+ // with a MINUS_DATE operator, so we must tell which case we're in by checking the type of
+ // the second argument.
+ Preconditions.checkState(direction < 0, "Time arithmetic require direction < 0");
+ if (call.getType().getFamily() == SqlTypeFamily.INTERVAL_YEAR_MONTH) {
+ return DruidExpression.fromExpression(
+ DruidExpression.functionCall(
+ "subtract_months",
+ leftExpr,
+ rightExpr,
+ DruidExpression.fromExpression(DruidExpression.stringLiteral(plannerContext.getTimeZone().getID()))
+ )
+ );
+ } else {
+ return DruidExpression.fromExpression(
+ StringUtils.format(
+ "(%s %s %s)",
+ leftExpr.getExpression(),
+ "-",
+ rightExpr.getExpression()
+ )
+ );
+ }
} else {
// Shouldn't happen if subclasses are behaving.
- throw new ISE("Got unexpected type period type family[%s]", shiftRexNode.getType().getFamily());
+ throw new ISE("Got unexpected type period type family[%s]", rightRexNode.getType().getFamily());
}
}
diff --git a/sql/src/main/java/org/apache/druid/sql/calcite/planner/DruidConvertletTable.java b/sql/src/main/java/org/apache/druid/sql/calcite/planner/DruidConvertletTable.java
index 1d94905..30ee069 100644
--- a/sql/src/main/java/org/apache/druid/sql/calcite/planner/DruidConvertletTable.java
+++ b/sql/src/main/java/org/apache/druid/sql/calcite/planner/DruidConvertletTable.java
@@ -64,6 +64,7 @@ public class DruidConvertletTable implements SqlRexConvertletTable
.add(SqlStdOperatorTable.SYMMETRIC_NOT_BETWEEN)
.add(SqlStdOperatorTable.ITEM)
.add(SqlStdOperatorTable.TIMESTAMP_ADD)
+ .add(SqlStdOperatorTable.TIMESTAMP_DIFF)
.add(SqlStdOperatorTable.UNION)
.add(SqlStdOperatorTable.UNION_ALL)
.build();
diff --git a/sql/src/test/java/org/apache/druid/sql/calcite/CalciteQueryTest.java b/sql/src/test/java/org/apache/druid/sql/calcite/CalciteQueryTest.java
index fb4976f..d2e1f75 100644
--- a/sql/src/test/java/org/apache/druid/sql/calcite/CalciteQueryTest.java
+++ b/sql/src/test/java/org/apache/druid/sql/calcite/CalciteQueryTest.java
@@ -7684,4 +7684,48 @@ public class CalciteQueryTest extends BaseCalciteQueryTest
)
);
}
+
+ @Test
+ public void testTimestampDiff() throws Exception
+ {
+ testQuery(
+ "SELECT TIMESTAMPDIFF(DAY, TIMESTAMP '1999-01-01 00:00:00', __time), \n"
+ + "TIMESTAMPDIFF(DAY, __time, DATE '2001-01-01'), \n"
+ + "TIMESTAMPDIFF(HOUR, TIMESTAMP '1999-12-31 01:00:00', __time), \n"
+ + "TIMESTAMPDIFF(MINUTE, TIMESTAMP '1999-12-31 23:58:03', __time), \n"
+ + "TIMESTAMPDIFF(SECOND, TIMESTAMP '1999-12-31 23:59:03', __time), \n"
+ + "TIMESTAMPDIFF(MONTH, TIMESTAMP '1999-11-01 00:00:00', __time), \n"
+ + "TIMESTAMPDIFF(YEAR, TIMESTAMP '1996-11-01 00:00:00', __time), \n"
+ + "TIMESTAMPDIFF(QUARTER, TIMESTAMP '1996-10-01 00:00:00', __time), \n"
+ + "TIMESTAMPDIFF(WEEK, TIMESTAMP '1998-10-01 00:00:00', __time) \n"
+ + "FROM druid.foo\n"
+ + "LIMIT 2",
+ ImmutableList.of(
+ newScanQueryBuilder()
+ .dataSource(CalciteTests.DATASOURCE1)
+ .intervals(querySegmentSpec(Filtration.eternity()))
+ .virtualColumns(
+ expressionVirtualColumn("v0", "div((\"__time\" - 915148800000),86400000)", ValueType.LONG),
+ expressionVirtualColumn("v1", "div((978307200000 - \"__time\"),86400000)", ValueType.LONG),
+ expressionVirtualColumn("v2", "div((\"__time\" - 946602000000),3600000)", ValueType.LONG),
+ expressionVirtualColumn("v3", "div((\"__time\" - 946684683000),60000)", ValueType.LONG),
+ expressionVirtualColumn("v4", "div((\"__time\" - 946684743000),1000)", ValueType.LONG),
+ expressionVirtualColumn("v5", "subtract_months(\"__time\",941414400000,'UTC')", ValueType.LONG),
+ expressionVirtualColumn("v6", "div(subtract_months(\"__time\",846806400000,'UTC'),12)", ValueType.LONG),
+ expressionVirtualColumn("v7", "div(subtract_months(\"__time\",844128000000,'UTC'),3)", ValueType.LONG),
+ expressionVirtualColumn("v8", "div(div((\"__time\" - 907200000000),1000),604800)", ValueType.LONG)
+ )
+ .columns("v0", "v1", "v2", "v3", "v4", "v5", "v6", "v7", "v8")
+ .limit(2)
+ .resultFormat(ScanQuery.ResultFormat.RESULT_FORMAT_COMPACTED_LIST)
+ .context(QUERY_CONTEXT_DEFAULT)
+ .build()
+
+ ),
+ ImmutableList.of(
+ new Object[]{365, 366, 23, 1, 57, 2, 3, 13, 65},
+ new Object[]{366, 365, 47, 1441, 86457, 2, 3, 13, 65}
+ )
+ );
+ }
}
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org