[CALCITE-2699] TIMESTAMPADD function now applies to DATE and TIME as well as TIMESTAMP (xuqianjin)
Close apache/calcite#936 Project: http://git-wip-us.apache.org/repos/asf/calcite/repo Commit: http://git-wip-us.apache.org/repos/asf/calcite/commit/18caf38f Tree: http://git-wip-us.apache.org/repos/asf/calcite/tree/18caf38f Diff: http://git-wip-us.apache.org/repos/asf/calcite/diff/18caf38f Branch: refs/heads/master Commit: 18caf38f83802015043eb06ad8aa49d1efe7ff68 Parents: fbf193b Author: xuqianjin <[email protected]> Authored: Fri Nov 23 22:15:20 2018 +0800 Committer: Julian Hyde <[email protected]> Committed: Sat Dec 1 14:43:59 2018 -0800 ---------------------------------------------------------------------- .../calcite/adapter/enumerable/RexImpTable.java | 15 +++++--- .../sql/fun/SqlTimestampAddFunction.java | 12 ++++--- .../calcite/sql/test/SqlOperatorBaseTest.java | 37 ++++++++++++++++++++ site/_docs/reference.md | 2 +- 4 files changed, 56 insertions(+), 10 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/calcite/blob/18caf38f/core/src/main/java/org/apache/calcite/adapter/enumerable/RexImpTable.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/adapter/enumerable/RexImpTable.java b/core/src/main/java/org/apache/calcite/adapter/enumerable/RexImpTable.java index ada4333..7776ac9 100644 --- a/core/src/main/java/org/apache/calcite/adapter/enumerable/RexImpTable.java +++ b/core/src/main/java/org/apache/calcite/adapter/enumerable/RexImpTable.java @@ -2617,11 +2617,16 @@ public class RexImpTable { case MINUS: trop1 = Expressions.negate(trop1); } - final BuiltInMethod method = - operand0.getType().getSqlTypeName() == SqlTypeName.TIMESTAMP - ? BuiltInMethod.ADD_MONTHS - : BuiltInMethod.ADD_MONTHS_INT; - return Expressions.call(method.method, trop0, trop1); + switch (typeName) { + case TIME: + return Expressions.convert_(trop0, long.class); + default: + final BuiltInMethod method = + operand0.getType().getSqlTypeName() == SqlTypeName.TIMESTAMP + ? BuiltInMethod.ADD_MONTHS + : BuiltInMethod.ADD_MONTHS_INT; + return Expressions.call(method.method, trop0, trop1); + } case INTERVAL_DAY: case INTERVAL_DAY_HOUR: http://git-wip-us.apache.org/repos/asf/calcite/blob/18caf38f/core/src/main/java/org/apache/calcite/sql/fun/SqlTimestampAddFunction.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/sql/fun/SqlTimestampAddFunction.java b/core/src/main/java/org/apache/calcite/sql/fun/SqlTimestampAddFunction.java index bc459d0..72b00f0 100644 --- a/core/src/main/java/org/apache/calcite/sql/fun/SqlTimestampAddFunction.java +++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlTimestampAddFunction.java @@ -29,13 +29,13 @@ import org.apache.calcite.sql.type.SqlTypeName; /** * The <code>TIMESTAMPADD</code> function, which adds an interval to a - * timestamp. + * datetime (TIMESTAMP, TIME or DATE). * * <p>The SQL syntax is * * <blockquote> * <code>TIMESTAMPADD(<i>timestamp interval</i>, <i>quantity</i>, - * <i>timestamp</i>)</code> + * <i>datetime</i>)</code> * </blockquote> * * <p>The interval time unit can one of the following literals:<ul> @@ -51,7 +51,7 @@ import org.apache.calcite.sql.type.SqlTypeName; * <li>YEAR (and synonym SQL_TSI_YEAR) * </ul> * - * <p>Returns modified timestamp. + * <p>Returns modified datetime. */ public class SqlTimestampAddFunction extends SqlFunction { @@ -85,7 +85,11 @@ public class SqlTimestampAddFunction extends SqlFunction { MICROSECOND_PRECISION); break; default: - type = typeFactory.createSqlType(SqlTypeName.TIMESTAMP); + if (operandType2.getSqlTypeName() == SqlTypeName.TIME) { + type = typeFactory.createSqlType(SqlTypeName.TIME); + } else { + type = typeFactory.createSqlType(SqlTypeName.TIMESTAMP); + } } break; default: http://git-wip-us.apache.org/repos/asf/calcite/blob/18caf38f/core/src/test/java/org/apache/calcite/sql/test/SqlOperatorBaseTest.java ---------------------------------------------------------------------- diff --git a/core/src/test/java/org/apache/calcite/sql/test/SqlOperatorBaseTest.java b/core/src/test/java/org/apache/calcite/sql/test/SqlOperatorBaseTest.java index 78a2fb7..1a1b750 100644 --- a/core/src/test/java/org/apache/calcite/sql/test/SqlOperatorBaseTest.java +++ b/core/src/test/java/org/apache/calcite/sql/test/SqlOperatorBaseTest.java @@ -6957,6 +6957,43 @@ public abstract class SqlOperatorBaseTest { "2016-06-30", "DATE NOT NULL"); tester.checkScalar("timestampadd(MONTH, -1, date '2016-03-31')", "2016-02-29", "DATE NOT NULL"); + + // TIMESTAMPADD with time; returns a time value.The interval is positive. + tester.checkScalar("timestampadd(SECOND, 1, time '23:59:59')", + "00:00:00", "TIME(0) NOT NULL"); + tester.checkScalar("timestampadd(MINUTE, 1, time '00:00:00')", + "00:01:00", "TIME(0) NOT NULL"); + tester.checkScalar("timestampadd(MINUTE, 1, time '23:59:59')", + "00:00:59", "TIME(0) NOT NULL"); + tester.checkScalar("timestampadd(HOUR, 1, time '23:59:59')", + "00:59:59", "TIME(0) NOT NULL"); + tester.checkScalar("timestampadd(DAY, 15, time '23:59:59')", + "23:59:59", "TIME(0) NOT NULL"); + tester.checkScalar("timestampadd(WEEK, 3, time '23:59:59')", + "23:59:59", "TIME(0) NOT NULL"); + tester.checkScalar("timestampadd(MONTH, 6, time '23:59:59')", + "23:59:59", "TIME(0) NOT NULL"); + tester.checkScalar("timestampadd(QUARTER, 1, time '23:59:59')", + "23:59:59", "TIME(0) NOT NULL"); + tester.checkScalar("timestampadd(YEAR, 10, time '23:59:59')", + "23:59:59", "TIME(0) NOT NULL"); + // TIMESTAMPADD with time; returns a time value .The interval is negative. + tester.checkScalar("timestampadd(SECOND, -1, time '00:00:00')", + "23:59:59", "TIME(0) NOT NULL"); + tester.checkScalar("timestampadd(MINUTE, -1, time '00:00:00')", + "23:59:00", "TIME(0) NOT NULL"); + tester.checkScalar("timestampadd(HOUR, -1, time '00:00:00')", + "23:00:00", "TIME(0) NOT NULL"); + tester.checkScalar("timestampadd(DAY, -1, time '23:59:59')", + "23:59:59", "TIME(0) NOT NULL"); + tester.checkScalar("timestampadd(WEEK, -1, time '23:59:59')", + "23:59:59", "TIME(0) NOT NULL"); + tester.checkScalar("timestampadd(MONTH, -1, time '23:59:59')", + "23:59:59", "TIME(0) NOT NULL"); + tester.checkScalar("timestampadd(QUARTER, -1, time '23:59:59')", + "23:59:59", "TIME(0) NOT NULL"); + tester.checkScalar("timestampadd(YEAR, -1, time '23:59:59')", + "23:59:59", "TIME(0) NOT NULL"); } @Test public void testTimestampAddFractionalSeconds() { http://git-wip-us.apache.org/repos/asf/calcite/blob/18caf38f/site/_docs/reference.md ---------------------------------------------------------------------- diff --git a/site/_docs/reference.md b/site/_docs/reference.md index e43e255..ab2f4f3 100644 --- a/site/_docs/reference.md +++ b/site/_docs/reference.md @@ -1473,7 +1473,7 @@ Not implemented: | {fn HOUR(date)} | Equivalent to `EXTRACT(HOUR FROM date)`. Returns an integer between 0 and 23. | {fn MINUTE(date)} | Equivalent to `EXTRACT(MINUTE FROM date)`. Returns an integer between 0 and 59. | {fn SECOND(date)} | Equivalent to `EXTRACT(SECOND FROM date)`. Returns an integer between 0 and 59. -| {fn TIMESTAMPADD(timeUnit, count, timestamp)} | Adds an interval of *count* *timeUnit*s to a timestamp +| {fn TIMESTAMPADD(timeUnit, count, datetime)} | Adds an interval of *count* *timeUnit*s to a datetime | {fn TIMESTAMPDIFF(timeUnit, timestamp1, timestamp2)} | Subtracts *timestamp1* from *timestamp2* and returns the result in *timeUnit*s Not implemented:
