drill-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From ve...@apache.org
Subject drill git commit: DRILL-4549: Add support for more truncation units in date_trunc function
Date Tue, 29 Mar 2016 01:47:37 GMT
Repository: drill
Updated Branches:
  refs/heads/master 7e6de2bbc -> 4442e162a


DRILL-4549: Add support for more truncation units in date_trunc function


Project: http://git-wip-us.apache.org/repos/asf/drill/repo
Commit: http://git-wip-us.apache.org/repos/asf/drill/commit/4442e162
Tree: http://git-wip-us.apache.org/repos/asf/drill/tree/4442e162
Diff: http://git-wip-us.apache.org/repos/asf/drill/diff/4442e162

Branch: refs/heads/master
Commit: 4442e162a0c9d92281e52f66319abf6eba1dde97
Parents: 7e6de2b
Author: vkorukanti <venki@dremio.com>
Authored: Mon Mar 28 11:09:34 2016 -0700
Committer: vkorukanti <venki@dremio.com>
Committed: Mon Mar 28 17:54:18 2016 -0700

----------------------------------------------------------------------
 .../src/main/codegen/data/DateIntervalFunc.tdd  |   4 +-
 .../DateTruncFunctions.java                     | 271 +++++++++++++--
 .../drill/exec/planner/logical/DrillOptiq.java  |  31 ++
 .../org/apache/drill/TestFunctionsQuery.java    |  34 +-
 .../exec/fn/impl/TestDateTruncFunctions.java    | 340 +++++++++++++++++++
 5 files changed, 621 insertions(+), 59 deletions(-)
----------------------------------------------------------------------


http://git-wip-us.apache.org/repos/asf/drill/blob/4442e162/exec/java-exec/src/main/codegen/data/DateIntervalFunc.tdd
----------------------------------------------------------------------
diff --git a/exec/java-exec/src/main/codegen/data/DateIntervalFunc.tdd b/exec/java-exec/src/main/codegen/data/DateIntervalFunc.tdd
index 8b842c3..9f1ae37 100644
--- a/exec/java-exec/src/main/codegen/data/DateIntervalFunc.tdd
+++ b/exec/java-exec/src/main/codegen/data/DateIntervalFunc.tdd
@@ -15,5 +15,7 @@
 # limitations under the License.
 {
     {dates: ["Date", "TimeStamp", "Time"] },
-    {intervals: ["Interval", "IntervalDay", "IntervalYear", "Int", "BigInt"] }
+    {intervals: ["Interval", "IntervalDay", "IntervalYear", "Int", "BigInt"] },
+    {truncInputTypes: ["Date", "TimeStamp", "Time", "Interval", "IntervalDay", "IntervalYear"]
},
+    {truncUnits : ["Second", "Minute", "Hour", "Day", "Month", "Year", "Week", "Quarter",
"Decade", "Century", "Millennium" ] },
 }

http://git-wip-us.apache.org/repos/asf/drill/blob/4442e162/exec/java-exec/src/main/codegen/templates/DateIntervalFunctionTemplates/DateTruncFunctions.java
----------------------------------------------------------------------
diff --git a/exec/java-exec/src/main/codegen/templates/DateIntervalFunctionTemplates/DateTruncFunctions.java
b/exec/java-exec/src/main/codegen/templates/DateIntervalFunctionTemplates/DateTruncFunctions.java
index 5ffff24..ad2f4c8 100644
--- a/exec/java-exec/src/main/codegen/templates/DateIntervalFunctionTemplates/DateTruncFunctions.java
+++ b/exec/java-exec/src/main/codegen/templates/DateIntervalFunctionTemplates/DateTruncFunctions.java
@@ -39,46 +39,259 @@ import io.netty.buffer.ByteBuf;
 
 public class GDateTimeTruncateFunctions {
 
-<#list dateIntervalFunc.dates as type>
-
-@SuppressWarnings("unused")
-@FunctionTemplate(names = "date_trunc", scope = FunctionTemplate.FunctionScope.SIMPLE, nulls
= NullHandling.NULL_IF_NULL)
-public static class G${type}DateTrunc implements DrillSimpleFunc {
+<#list dateIntervalFunc.truncInputTypes as type> <#-- Start InputType Loop -->
 
+  /**
+   * This class merely act as a placeholder so that Calcite allows 'trunc('truncationUnit',
col)'
+   * function in SQL.
+   */
+  @SuppressWarnings("unused")
+  @FunctionTemplate(name = "date_trunc", scope = FunctionTemplate.FunctionScope.SIMPLE, nulls
= NullHandling.NULL_IF_NULL)
+  public static class G${type}DummyDateTrunc implements DrillSimpleFunc {
     @Param  VarCharHolder left;
     @Param  ${type}Holder right;
     @Output ${type}Holder out;
+
+    @Override
+    public void setup() { }
+
+    @Override
+    public void eval() { }
+  }
+  <#list dateIntervalFunc.truncUnits as toUnit> <#-- Start UnitType Loop -->
+
+    <#-- Filter out unsupported combinations -->
+    <#if !(type == "Time" && toUnit == "Week") &&
+      !(type == "Interval" && toUnit == "Week") &&
+      !(type == "IntervalDay" && toUnit == "Week") &&
+      !(type == "IntervalYear" && toUnit == "Week")
+    > <#-- Truncate by Week not supported for Time input type -->
+
+  @SuppressWarnings("unused")
+  @FunctionTemplate(names = "date_trunc_${toUnit}", scope = FunctionTemplate.FunctionScope.SIMPLE,
nulls = NullHandling.NULL_IF_NULL)
+  public static class G${type}${toUnit}DateTrunc implements DrillSimpleFunc {
+    @Param  ${type}Holder right;
+    @Output ${type}Holder out;
     @Workspace org.joda.time.MutableDateTime dateTime;
+    <#if toUnit == "Quarter" || toUnit == "Decade" || toUnit == "Century" || toUnit ==
"Millennium">
+    @Workspace org.joda.time.MutableDateTime dateTime2;
+    </#if>
 
     public void setup() {
       dateTime = new org.joda.time.MutableDateTime(org.joda.time.DateTimeZone.UTC);
+      <#if toUnit == "Quarter" || toUnit == "Decade" || toUnit == "Century" || toUnit
== "Millennium">
+      dateTime2 = new org.joda.time.MutableDateTime(org.joda.time.DateTimeZone.UTC);
+      </#if>
     }
 
     public void eval() {
-        dateTime.setMillis(right.value);
-        <#if type != "Time">
-        if (org.apache.drill.exec.expr.fn.impl.StringFunctionHelpers.toStringFromUTF8(left.start,
left.end, left.buffer).equalsIgnoreCase("YEAR")) dateTime.setRounding(dateTime.getChronology().year());
-        else if (org.apache.drill.exec.expr.fn.impl.StringFunctionHelpers.toStringFromUTF8(left.start,
left.end, left.buffer).equalsIgnoreCase("MONTH")) dateTime.setRounding(dateTime.getChronology().monthOfYear());
-        else if (org.apache.drill.exec.expr.fn.impl.StringFunctionHelpers.toStringFromUTF8(left.start,
left.end, left.buffer).equalsIgnoreCase("DAY")) dateTime.setRounding(dateTime.getChronology().dayOfMonth());
-        else
-        </#if>
-        <#if type != "Date">
-        if (org.apache.drill.exec.expr.fn.impl.StringFunctionHelpers.toStringFromUTF8(left.start,
left.end, left.buffer).equalsIgnoreCase("HOUR")) dateTime.setRounding(dateTime.getChronology().hourOfDay());
-        else if (org.apache.drill.exec.expr.fn.impl.StringFunctionHelpers.toStringFromUTF8(left.start,
left.end, left.buffer).equalsIgnoreCase("MINUTE")) dateTime.setRounding(dateTime.getChronology().minuteOfHour());
-        else if (org.apache.drill.exec.expr.fn.impl.StringFunctionHelpers.toStringFromUTF8(left.start,
left.end, left.buffer).equalsIgnoreCase("SECOND")) dateTime.setRounding(dateTime.getChronology().secondOfMinute());
-        else
-        </#if>
-        <#if type == "TimeStamp">
-            throw new UnsupportedOperationException("date_trunc function supports the following
time units for TimeStamp: YEAR, MONTH, DAY, HOUR, MINUTE, SECOND");
-        out.value = dateTime.getMillis();
-        <#elseif type == "Date">
-            throw new UnsupportedOperationException("date_trunc function supports the following
time units for Date: YEAR, MONTH, DAY");
-        out.value = dateTime.getMillis();
-        <#elseif type == "Time">
-            throw new UnsupportedOperationException("date_trunc function supports the following
time units for Time: HOUR, MINUTE, SECOND");
-        out.value = (int) dateTime.getMillis();
-        </#if>
+      <#if type == "Time"> <#-- Start InputType -->
+        <#if toUnit == "Hour"> <#-- Start UnitType -->
+      dateTime.setMillis(right.value);
+      dateTime.setRounding(dateTime.getChronology().hourOfDay());
+      out.value = (int) dateTime.getMillis();
+        <#elseif toUnit == "Minute">
+      dateTime.setMillis(right.value);
+      dateTime.setRounding(dateTime.getChronology().minuteOfHour());
+      out.value = (int) dateTime.getMillis();
+        <#elseif toUnit == "Second">
+      dateTime.setMillis(right.value);
+      dateTime.setRounding(dateTime.getChronology().secondOfMinute());
+      out.value = (int) dateTime.getMillis();
+        <#else>
+        <#-- For all other units truncate the whole thing -->
+      out.value = 0;
+        </#if> <#-- End UnitType -->
+      <#elseif type == "Date">
+        <#if toUnit == "Second" || toUnit == "Minute" || toUnit == "Hour" || toUnit ==
"Day"> <#-- Start UnitType -->
+      // No truncation as there is no time part in date
+      out.value = right.value;
+        <#elseif toUnit == "Year">
+      dateTime.setMillis(right.value);
+      dateTime.setRounding(dateTime.getChronology().year());
+      out.value = dateTime.getMillis();
+        <#elseif toUnit == "Month">
+      dateTime.setMillis(right.value);
+      dateTime.setRounding(dateTime.getChronology().monthOfYear());
+      out.value = dateTime.getMillis();
+        <#elseif toUnit == "Week">
+      dateTime.setMillis(right.value);
+      dateTime.setRounding(dateTime.getChronology().weekOfWeekyear());
+      out.value = dateTime.getMillis();
+        <#elseif toUnit == "Quarter">
+      dateTime.setMillis(right.value);
+      int month = dateTime.getMonthOfYear();
+      dateTime.setRounding(dateTime.getChronology().year());
+      dateTime2.setMillis(dateTime.getMillis());
+      dateTime2.add(org.joda.time.DurationFieldType.months(), ((month-1)/3)*3);
+      out.value = dateTime2.getMillis();
+        <#elseif toUnit == "Decade">
+      dateTime.setMillis(right.value);
+      int year = dateTime.getYear();
+      dateTime.setRounding(dateTime.getChronology().centuryOfEra());
+      dateTime2.setMillis(dateTime.getMillis());
+      dateTime2.add(org.joda.time.DurationFieldType.years(), ((year%100)/10)*10);
+      out.value = dateTime2.getMillis();
+        <#elseif toUnit == "Century">
+      dateTime.setMillis(right.value);
+      dateTime.add(org.joda.time.DurationFieldType.years(), -1);
+      dateTime.setRounding(dateTime.getChronology().centuryOfEra());
+      dateTime2.setMillis(dateTime.getMillis());
+      dateTime2.add(org.joda.time.DurationFieldType.years(), 1);
+      out.value = dateTime2.getMillis();
+        <#elseif toUnit == "Millennium">
+      dateTime.setMillis(right.value);
+      int year = dateTime.getYear();
+      dateTime.setRounding(dateTime.getChronology().era());
+      dateTime2.setMillis(dateTime.getMillis());
+      dateTime2.add(org.joda.time.DurationFieldType.years(), ((year-1)/1000)*1000);
+      out.value = dateTime2.getMillis();
+        </#if> <#-- End UnitType -->
+      <#elseif type == "TimeStamp">
+        <#if toUnit == "Year"> <#--  Start UnitType -->
+      dateTime.setMillis(right.value);
+      dateTime.setRounding(dateTime.getChronology().year());
+      out.value = dateTime.getMillis();
+        <#elseif toUnit == "Month">
+      dateTime.setMillis(right.value);
+      dateTime.setRounding(dateTime.getChronology().monthOfYear());
+      out.value = dateTime.getMillis();
+        <#elseif toUnit == "Day">
+      dateTime.setMillis(right.value);
+      dateTime.setRounding(dateTime.getChronology().dayOfMonth());
+      out.value = dateTime.getMillis();
+        <#elseif toUnit == "Hour">
+      dateTime.setMillis(right.value);
+      dateTime.setRounding(dateTime.getChronology().hourOfDay());
+      out.value = dateTime.getMillis();
+        <#elseif toUnit == "Minute">
+      dateTime.setMillis(right.value);
+      dateTime.setRounding(dateTime.getChronology().minuteOfHour());
+      out.value = dateTime.getMillis();
+        <#elseif toUnit == "Second">
+      dateTime.setMillis(right.value);
+      dateTime.setRounding(dateTime.getChronology().secondOfMinute());
+      out.value = dateTime.getMillis();
+        <#elseif toUnit == "Week">
+      dateTime.setMillis(right.value);
+      dateTime.setRounding(dateTime.getChronology().weekOfWeekyear());
+      out.value = dateTime.getMillis();
+        <#elseif toUnit == "Quarter">
+      dateTime.setMillis(right.value);
+      int month = dateTime.getMonthOfYear();
+      dateTime.setRounding(dateTime.getChronology().year());
+      dateTime2.setMillis(dateTime.getMillis());
+      dateTime2.add(org.joda.time.DurationFieldType.months(), ((month-1)/3)*3);
+      out.value = dateTime2.getMillis();
+        <#elseif toUnit == "Decade">
+      dateTime.setMillis(right.value);
+      int year = dateTime.getYear();
+      dateTime.setRounding(dateTime.getChronology().centuryOfEra());
+      dateTime2.setMillis(dateTime.getMillis());
+      dateTime2.add(org.joda.time.DurationFieldType.years(), ((year%100)/10)*10);
+      out.value = dateTime2.getMillis();
+        <#elseif toUnit == "Century">
+      dateTime.setMillis(right.value);
+      dateTime.add(org.joda.time.DurationFieldType.years(), -1);
+      dateTime.setRounding(dateTime.getChronology().centuryOfEra());
+      dateTime2.setMillis(dateTime.getMillis());
+      dateTime2.add(org.joda.time.DurationFieldType.years(), 1);
+      out.value = dateTime2.getMillis();
+        <#elseif toUnit == "Millennium">
+      dateTime.setMillis(right.value);
+      int year = dateTime.getYear();
+      dateTime.setRounding(dateTime.getChronology().era());
+      dateTime2.setMillis(dateTime.getMillis());
+      dateTime2.add(org.joda.time.DurationFieldType.years(), ((year-1)/1000)*1000);
+      out.value = dateTime2.getMillis();
+        </#if> <#-- End UnitType -->
+      <#elseif type == "Interval">
+        <#if toUnit == "Second"> <#--  Start UnitType -->
+      out.months = right.months;
+      out.days = right.days;
+      out.milliseconds = (right.milliseconds/(org.apache.drill.exec.expr.fn.impl.DateUtility.secondsToMillis))*
+          (org.apache.drill.exec.expr.fn.impl.DateUtility.secondsToMillis);
+        <#elseif toUnit == "Minute">
+      out.months = right.months;
+      out.days = right.days;
+      out.milliseconds = (right.milliseconds/(org.apache.drill.exec.expr.fn.impl.DateUtility.minutesToMillis))*
+          (org.apache.drill.exec.expr.fn.impl.DateUtility.minutesToMillis);
+        <#elseif toUnit == "Hour">
+      out.months = right.months;
+      out.days = right.days;
+      out.milliseconds =
+          (right.milliseconds/(org.apache.drill.exec.expr.fn.impl.DateUtility.hoursToMillis))*
+              (org.apache.drill.exec.expr.fn.impl.DateUtility.hoursToMillis);
+        <#elseif toUnit == "Day">
+      out.months = right.months;
+      out.days = right.days;
+      out.milliseconds = 0;
+        <#elseif toUnit == "Month">
+      out.months = right.months;
+      out.days = 0;
+      out.milliseconds = 0;
+        <#elseif toUnit == "Quarter">
+      out.months = (right.months / 3) * 3;
+      out.days = 0;
+      out.milliseconds = 0;
+        <#elseif toUnit == "Year">
+      out.months = (right.months / 12) * 12;
+      out.days = 0;
+      out.milliseconds = 0;
+        <#elseif toUnit == "Decade">
+      out.months = (right.months / 120) * 120;
+      out.days = 0;
+      out.milliseconds = 0;
+        <#elseif toUnit == "Century">
+      out.months = (right.months / 1200) * 1200;
+      out.days = 0;
+      out.milliseconds = 0;
+        <#elseif toUnit == "Millennium">
+      out.months = (right.months / 12000) * 12000;
+      out.days = 0;
+      out.milliseconds = 0;
+        </#if> <#-- End UnitType -->
+      <#elseif type == "IntervalDay">
+        <#if toUnit == "Second"> <#--  Start UnitType -->
+      out.days = right.days;
+      out.milliseconds = (right.milliseconds/(org.apache.drill.exec.expr.fn.impl.DateUtility.secondsToMillis))*
+        (org.apache.drill.exec.expr.fn.impl.DateUtility.secondsToMillis);
+        <#elseif toUnit == "Minute">
+      out.days = right.days;
+      out.milliseconds = (right.milliseconds/(org.apache.drill.exec.expr.fn.impl.DateUtility.minutesToMillis))*
+          (org.apache.drill.exec.expr.fn.impl.DateUtility.minutesToMillis);
+        <#elseif toUnit == "Hour">
+      out.days = right.days;
+      out.milliseconds =
+          (right.milliseconds/(org.apache.drill.exec.expr.fn.impl.DateUtility.hoursToMillis))*
+              (org.apache.drill.exec.expr.fn.impl.DateUtility.hoursToMillis);
+        <#elseif toUnit == "Day">
+      out.days = right.days;
+      out.milliseconds = 0;
+        <#elseif toUnit == "Month" || toUnit == "Quarter" || toUnit == "Year" || toUnit
== "Decade" || toUnit == "Century" || toUnit == "Millennium">
+      out.days = 0;
+      out.milliseconds = 0;
+        </#if> <#-- End UnitType -->
+      <#elseif type == "IntervalYear">
+        <#if toUnit == "Second" || toUnit == "Minute" || toUnit == "Hour" || toUnit ==
"Day"> <#--  Start UnitType -->
+      out.value = right.value;
+        <#elseif toUnit == "Month">
+      out.value = right.value;
+        <#elseif toUnit == "Quarter">
+      out.value = (right.value / 3) * 3;
+        <#elseif toUnit == "Year">
+      out.value = (right.value / 12) * 12;
+        <#elseif toUnit == "Decade">
+      out.value = (right.value / 120) * 120;
+        <#elseif toUnit == "Century">
+      out.value = (right.value / 1200) * 1200;
+        <#elseif toUnit == "Millennium">
+      out.value = (right.value / 12000) * 12000;
+        </#if> <#-- End UnitType -->
+      </#if> <#-- End InputType -->
     }
 }
-</#list>
+   </#if> <#-- Filter out unsupported combinations -->
+  </#list> <#-- End UnitType Loop -->
+</#list> <#-- End InputType Loop -->
 }
\ No newline at end of file

http://git-wip-us.apache.org/repos/asf/drill/blob/4442e162/exec/java-exec/src/main/java/org/apache/drill/exec/planner/logical/DrillOptiq.java
----------------------------------------------------------------------
diff --git a/exec/java-exec/src/main/java/org/apache/drill/exec/planner/logical/DrillOptiq.java
b/exec/java-exec/src/main/java/org/apache/drill/exec/planner/logical/DrillOptiq.java
index cae6796..87b76ae 100644
--- a/exec/java-exec/src/main/java/org/apache/drill/exec/planner/logical/DrillOptiq.java
+++ b/exec/java-exec/src/main/java/org/apache/drill/exec/planner/logical/DrillOptiq.java
@@ -22,6 +22,7 @@ import java.util.GregorianCalendar;
 import java.util.LinkedList;
 import java.util.List;
 
+import org.apache.calcite.rel.logical.LogicalAggregate;
 import org.apache.drill.common.exceptions.UserException;
 import org.apache.drill.common.expression.ExpressionPosition;
 import org.apache.drill.common.expression.FieldReference;
@@ -422,11 +423,41 @@ public class DrillOptiq {
       } else if ((functionName.equals("convert_from") || functionName.equals("convert_to"))
                     && args.get(1) instanceof QuotedString) {
         return FunctionCallFactory.createConvert(functionName, ((QuotedString)args.get(1)).value,
args.get(0), ExpressionPosition.UNKNOWN);
+      } else if (functionName.equals("date_trunc")) {
+        return handleDateTruncFunction(args);
       }
 
       return FunctionCallFactory.createExpression(functionName, args);
     }
 
+    private LogicalExpression handleDateTruncFunction(final List<LogicalExpression>
args) {
+      // Assert that the first argument to extract is a QuotedString
+      assert args.get(0) instanceof ValueExpressions.QuotedString;
+
+      // Get the unit of time to be extracted
+      String timeUnitStr = ((ValueExpressions.QuotedString)args.get(0)).value.toUpperCase();
+
+      switch (timeUnitStr){
+        case ("YEAR"):
+        case ("MONTH"):
+        case ("DAY"):
+        case ("HOUR"):
+        case ("MINUTE"):
+        case ("SECOND"):
+        case ("WEEK"):
+        case ("QUARTER"):
+        case ("DECADE"):
+        case ("CENTURY"):
+        case ("MILLENNIUM"):
+          final String functionPostfix = timeUnitStr.substring(0, 1).toUpperCase() + timeUnitStr.substring(1).toLowerCase();
+          return FunctionCallFactory.createExpression("date_trunc_" + functionPostfix, args.subList(1,
2));
+      }
+
+      throw new UnsupportedOperationException("date_trunc function supports the following
time units: " +
+          "YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, WEEK, QUARTER, DECADE, CENTURY, MILLENNIUM");
+    }
+
+
     @Override
     public LogicalExpression visitLiteral(RexLiteral literal) {
       switch(literal.getType().getSqlTypeName()){

http://git-wip-us.apache.org/repos/asf/drill/blob/4442e162/exec/java-exec/src/test/java/org/apache/drill/TestFunctionsQuery.java
----------------------------------------------------------------------
diff --git a/exec/java-exec/src/test/java/org/apache/drill/TestFunctionsQuery.java b/exec/java-exec/src/test/java/org/apache/drill/TestFunctionsQuery.java
index 24021f8..53a86bb 100644
--- a/exec/java-exec/src/test/java/org/apache/drill/TestFunctionsQuery.java
+++ b/exec/java-exec/src/test/java/org/apache/drill/TestFunctionsQuery.java
@@ -17,7 +17,6 @@
  */
 package org.apache.drill;
 
-import org.apache.drill.exec.expr.fn.impl.DateUtility;
 import org.apache.drill.exec.planner.physical.PlannerSettings;
 import org.joda.time.DateTime;
 import org.junit.AfterClass;
@@ -27,6 +26,9 @@ import org.junit.Test;
 
 import java.math.BigDecimal;
 
+import static org.apache.drill.exec.expr.fn.impl.DateUtility.formatDate;
+import static org.apache.drill.exec.expr.fn.impl.DateUtility.formatTimeStamp;
+
 public class TestFunctionsQuery extends BaseTestQuery {
 
   // enable decimal data type
@@ -543,7 +545,7 @@ public class TestFunctionsQuery extends BaseTestQuery {
         "timestamp '2008-2-23 12:23:23' as TS " +
         "FROM cp.`tpch/region.parquet` limit 1";
 
-    DateTime date = DateUtility.formatTimeStamp.parseDateTime("2008-02-23 12:23:23.0");
+    DateTime date = formatTimeStamp.parseDateTime("2008-02-23 12:23:23.0");
     testBuilder()
         .sqlQuery(query)
         .unOrdered()
@@ -701,7 +703,7 @@ public class TestFunctionsQuery extends BaseTestQuery {
         "To_DaTe('2003/07/09', 'yyyy/MM/dd') as col3 " +
         "from cp.`employee.json` LIMIT 1";
 
-    DateTime date = DateUtility.formatDate.parseDateTime("2003-07-09");
+    DateTime date = formatDate.parseDateTime("2003-07-09");
 
     testBuilder()
         .sqlQuery(query)
@@ -780,32 +782,6 @@ public class TestFunctionsQuery extends BaseTestQuery {
   }
 
   @Test
-  public void testDateTrunc() throws Exception {
-    String query = "select "
-        + "date_trunc('MINUTE', time '2:30:21.5') as TIME1, "
-        + "date_trunc('SECOND', time '2:30:21.5') as TIME2, "
-        + "date_trunc('HOUR', timestamp '1991-05-05 10:11:12.100') as TS1, "
-        + "date_trunc('SECOND', timestamp '1991-05-05 10:11:12.100') as TS2, "
-        + "date_trunc('MONTH', date '2011-2-2') as DATE1, "
-        + "date_trunc('YEAR', date '2011-2-2') as DATE2 "
-        + "from cp.`employee.json` where employee_id < 2";
-
-    DateTime time1 = DateUtility.formatTime.parseDateTime("2:30:00.0");
-    DateTime time2 = DateUtility.formatTime.parseDateTime("2:30:21.0");
-    DateTime ts1 = DateUtility.formatTimeStamp.parseDateTime("1991-05-05 10:00:00.0");
-    DateTime ts2 = DateUtility.formatTimeStamp.parseDateTime("1991-05-05 10:11:12.0");
-    DateTime date1 = DateUtility.formatDate.parseDateTime("2011-02-01");
-    DateTime date2 = DateUtility.formatDate.parseDateTime("2011-01-01");
-
-    testBuilder()
-        .sqlQuery(query)
-        .unOrdered()
-        .baselineColumns("TIME1", "TIME2", "TS1", "TS2", "DATE1", "DATE2")
-        .baselineValues(time1, time2, ts1, ts2, date1, date2)
-        .go();
-  }
-
-  @Test
   public void testCaseWithDecimalExpressions() throws Exception {
     String query = "select " +
         "case when true then cast(employee_id as decimal(15, 5)) else cast('0.0' as decimal(2,
1)) end as col1 " +

http://git-wip-us.apache.org/repos/asf/drill/blob/4442e162/exec/java-exec/src/test/java/org/apache/drill/exec/fn/impl/TestDateTruncFunctions.java
----------------------------------------------------------------------
diff --git a/exec/java-exec/src/test/java/org/apache/drill/exec/fn/impl/TestDateTruncFunctions.java
b/exec/java-exec/src/test/java/org/apache/drill/exec/fn/impl/TestDateTruncFunctions.java
new file mode 100644
index 0000000..db456b2
--- /dev/null
+++ b/exec/java-exec/src/test/java/org/apache/drill/exec/fn/impl/TestDateTruncFunctions.java
@@ -0,0 +1,340 @@
+/**
+ * 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
+ * <p/>
+ * http://www.apache.org/licenses/LICENSE-2.0
+ * <p/>
+ * 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.
+ */
+package org.apache.drill.exec.fn.impl;
+
+import org.apache.drill.BaseTestQuery;
+import org.joda.time.DateTime;
+import org.joda.time.Period;
+import org.junit.Test;
+
+import static org.apache.drill.exec.expr.fn.impl.DateUtility.formatDate;
+import static org.apache.drill.exec.expr.fn.impl.DateUtility.formatTime;
+import static org.apache.drill.exec.expr.fn.impl.DateUtility.formatTimeStamp;
+
+public class TestDateTruncFunctions extends BaseTestQuery {
+
+  @Test
+  public void dateTruncOnTime() throws Exception {
+    final String query = "SELECT " +
+        "date_trunc('SECOND', time '2:30:21.5') as `second`, " +
+        "date_trunc('MINUTE', time '2:30:21.5') as `minute`, " +
+        "date_trunc('HOUR', time '2:30:21.5') as `hour`, " +
+        "date_trunc('DAY', time '2:30:21.5') as `day`, " +
+        "date_trunc('MONTH', time '2:30:21.5') as `month`, " +
+        "date_trunc('YEAR', time '2:30:21.5') as `year`, " +
+        "date_trunc('QUARTER', time '2:30:21.5') as `quarter`, " +
+        "date_trunc('DECADE', time '2:30:21.5') as `decade`, " +
+        "date_trunc('CENTURY', time '2:30:21.5') as `century`, " +
+        "date_trunc('MILLENNIUM', time '2:30:21.5') as `millennium` " +
+        "FROM sys.version";
+
+    testBuilder()
+        .sqlQuery(query)
+        .unOrdered()
+        .baselineColumns("second", "minute", "hour", "day", "month", "year", "quarter", "decade",
"century", "millennium")
+        .baselineValues(
+            formatTime.parseDateTime("2:30:21.0"), // seconds
+            formatTime.parseDateTime("2:30:00.0"), // minute
+            formatTime.parseDateTime("2:00:00.0"), // hour
+            formatTime.parseDateTime("0:00:00.0"), // day
+            formatTime.parseDateTime("0:00:00.0"), // month
+            formatTime.parseDateTime("0:00:00.0"), // year
+            formatTime.parseDateTime("0:00:00.0"), // quarter
+            formatTime.parseDateTime("0:00:00.0"), // decade
+            formatTime.parseDateTime("0:00:00.0"), // century
+            formatTime.parseDateTime("0:00:00.0")) // millennium
+        .go();
+  }
+
+  @Test
+  public void dateTruncOnDateSimpleUnits() throws Exception {
+    final String query = "SELECT " +
+        "date_trunc('SECOND', date '2011-2-3') as `second`, " +
+        "date_trunc('MINUTE', date '2011-2-3') as `minute`, " +
+        "date_trunc('HOUR', date '2011-2-3') as `hour`, " +
+        "date_trunc('DAY', date '2011-2-3') as `day`, " +
+        "date_trunc('WEEK', date '2011-2-3') as `week`, " +
+        "date_trunc('MONTH', date '2011-2-3') as `month`, " +
+        "date_trunc('YEAR', date '2011-2-3') as `year`, " +
+        "date_trunc('QUARTER', date '2011-5-3') as `q1`, " +
+        "date_trunc('QUARTER', date '2011-7-13') as `q2`, " +
+        "date_trunc('QUARTER', date '2011-9-13') as `q3`, " +
+        "date_trunc('DECADE', date '2011-2-3') as `decade1`, " +
+        "date_trunc('DECADE', date '2072-2-3') as `decade2`, " +
+        "date_trunc('DECADE', date '1978-2-3') as `decade3` " +
+        "FROM sys.version";
+
+    testBuilder()
+        .sqlQuery(query)
+        .unOrdered()
+        .baselineColumns("second", "minute", "hour", "day", "month", "week" , "year", "q1",
"q2", "q3", "decade1", "decade2", "decade3")
+        .baselineValues(
+            formatDate.parseDateTime("2011-02-03"), // seconds
+            formatDate.parseDateTime("2011-02-03"), // minute
+            formatDate.parseDateTime("2011-02-03"), // hour
+            formatDate.parseDateTime("2011-02-03"), // day
+            formatDate.parseDateTime("2011-02-01"), // month
+            formatDate.parseDateTime("2011-01-31"), // week
+            formatDate.parseDateTime("2011-01-01"), // year
+            formatDate.parseDateTime("2011-04-01"), // quarter-1
+            formatDate.parseDateTime("2011-07-01"), // quarter-2
+            formatDate.parseDateTime("2011-07-01"), // quarter-3
+            formatDate.parseDateTime("2010-01-01"), // decade-1
+            formatDate.parseDateTime("2070-01-01"), // decade-2
+            formatDate.parseDateTime("1970-01-01")) // decade-3
+        .go();
+  }
+
+  @Test
+  public void dateTruncOnDateCentury() throws Exception {
+    // TODO: It would be good to have some tests on dates in BC period, but looks like currently
Calcite parser is
+    // not accepting date literals in BC.
+    final String query = "SELECT " +
+        "date_trunc('CENTURY', date '2011-2-3') as c1, " +
+        "date_trunc('CENTURY', date '2000-2-3') as c2, " +
+        "date_trunc('CENTURY', date '1901-11-3') as c3, " +
+        "date_trunc('CENTURY', date '900-2-3') as c4, " +
+        "date_trunc('CENTURY', date '0001-1-3') as c5 " +
+        "FROM sys.version";
+
+    testBuilder()
+        .sqlQuery(query)
+        .unOrdered()
+        .baselineColumns("c1", "c2", "c3", "c4", "c5")
+        .baselineValues(
+            formatDate.parseDateTime("2001-01-01"), // c1
+            formatDate.parseDateTime("1901-01-01"), // c2
+            formatDate.parseDateTime("1901-01-01"), // c3
+            formatDate.parseDateTime("0801-01-01"), // c4
+            formatDate.parseDateTime("0001-01-01")) // c5
+        .go();
+  }
+
+  @Test
+  public void test() throws Exception {
+    org.joda.time.MutableDateTime dateTime = new org.joda.time.MutableDateTime(org.joda.time.DateTimeZone.UTC);
+    dateTime.setMillis(formatDate.parseDateTime("2001-01-01"));
+    dateTime.setRounding(dateTime.getChronology().centuryOfEra());
+  }
+
+  @Test
+  public void dateTruncOnDateMillennium() throws Exception {
+    // TODO: It would be good to have some tests on dates in BC period, but looks like currently
Calcite parser is
+    // not accepting date literals in BC.
+    final String query = "SELECT " +
+        "date_trunc('MILLENNIUM', date '2011-2-3') as `m1`, " +
+        "date_trunc('MILLENNIUM', date '2000-11-3') as `m2`, " +
+        "date_trunc('MILLENNIUM', date '1983-05-18') as `m3`, " +
+        "date_trunc('MILLENNIUM', date '990-11-3') as `m4`, " +
+        "date_trunc('MILLENNIUM', date '0001-11-3') as `m5` " +
+        "FROM sys.version";
+
+    testBuilder()
+        .sqlQuery(query)
+        .unOrdered()
+        .baselineColumns("m1", "m2", "m3", "m4", "m5")
+        .baselineValues(
+            formatDate.parseDateTime("2001-01-01"), // m1
+            formatDate.parseDateTime("1001-01-01"), // m2
+            formatDate.parseDateTime("1001-01-01"), // m3
+            formatDate.parseDateTime("0001-01-01"), // m4
+            formatDate.parseDateTime("0001-01-01")) // m5
+        .go();
+  }
+
+  @Test
+  public void dateTruncOnTimeStampSimpleUnits() throws Exception {
+    final String query = "SELECT " +
+        "date_trunc('SECOND', timestamp '2011-2-3 10:11:12.100') as `second`, " +
+        "date_trunc('MINUTE', timestamp '2011-2-3 10:11:12.100') as `minute`, " +
+        "date_trunc('HOUR', timestamp '2011-2-3 10:11:12.100') as `hour`, " +
+        "date_trunc('DAY', timestamp '2011-2-3 10:11:12.100') as `day`, " +
+        "date_trunc('WEEK', timestamp '2011-2-3 10:11:12.100') as `week`, " +
+        "date_trunc('MONTH', timestamp '2011-2-3 10:11:12.100') as `month`, " +
+        "date_trunc('YEAR', timestamp '2011-2-3 10:11:12.100') as `year`, " +
+        "date_trunc('QUARTER', timestamp '2011-5-3 10:11:12.100') as `q1`, " +
+        "date_trunc('QUARTER', timestamp '2011-7-13 10:11:12.100') as `q2`, " +
+        "date_trunc('QUARTER', timestamp '2011-9-13 10:11:12.100') as `q3`, " +
+        "date_trunc('DECADE', timestamp '2011-2-3 10:11:12.100') as `decade1`, " +
+        "date_trunc('DECADE', timestamp '2072-2-3 10:11:12.100') as `decade2`, " +
+        "date_trunc('DECADE', timestamp '1978-2-3 10:11:12.100') as `decade3` " +
+        "FROM sys.version";
+
+    testBuilder()
+        .sqlQuery(query)
+        .unOrdered()
+        .baselineColumns("second", "minute", "hour", "day", "month", "week" , "year", "q1",
"q2", "q3", "decade1", "decade2", "decade3")
+        .baselineValues(
+            formatTimeStamp.parseDateTime("2011-02-03 10:11:12.0"), // seconds
+            formatTimeStamp.parseDateTime("2011-02-03 10:11:00.0"), // minute
+            formatTimeStamp.parseDateTime("2011-02-03 10:00:00.0"), // hour
+            formatTimeStamp.parseDateTime("2011-02-03 00:00:00.0"), // day
+            formatTimeStamp.parseDateTime("2011-02-01 00:00:00.0"), // month
+            formatTimeStamp.parseDateTime("2011-01-31 00:00:00.0"), // week
+            formatTimeStamp.parseDateTime("2011-01-01 00:00:00.0"), // year
+            formatTimeStamp.parseDateTime("2011-04-01 00:00:00.0"), // quarter-1
+            formatTimeStamp.parseDateTime("2011-07-01 00:00:00.0"), // quarter-2
+            formatTimeStamp.parseDateTime("2011-07-01 00:00:00.0"), // quarter-3
+            formatTimeStamp.parseDateTime("2010-01-01 00:00:00.0"), // decade-1
+            formatTimeStamp.parseDateTime("2070-01-01 00:00:00.0"), // decade-2
+            formatTimeStamp.parseDateTime("1970-01-01 00:00:00.0")) // decade-3
+        .go();
+  }
+
+  @Test
+  public void dateTruncOnTimeStampCentury() throws Exception {
+    // TODO: It would be good to have some tests on dates in BC period, but looks like currently
Calcite parser is
+    // not accepting date literals in BC.
+    final String query = "SELECT " +
+        "date_trunc('CENTURY', timestamp '2011-2-3 10:11:12.100') as c1, " +
+        "date_trunc('CENTURY', timestamp '2000-2-3 10:11:12.100') as c2, " +
+        "date_trunc('CENTURY', timestamp '1901-11-3 10:11:12.100') as c3, " +
+        "date_trunc('CENTURY', timestamp '900-2-3 10:11:12.100') as c4, " +
+        "date_trunc('CENTURY', timestamp '0001-1-3 10:11:12.100') as c5 " +
+        "FROM sys.version";
+
+    testBuilder()
+        .sqlQuery(query)
+        .unOrdered()
+        .baselineColumns("c1", "c2", "c3", "c4", "c5")
+        .baselineValues(
+            formatTimeStamp.parseDateTime("2001-01-01 00:00:00.0"), // c1
+            formatTimeStamp.parseDateTime("1901-01-01 00:00:00.0"), // c2
+            formatTimeStamp.parseDateTime("1901-01-01 00:00:00.0"), // c3
+            formatTimeStamp.parseDateTime("0801-01-01 00:00:00.0"), // c4
+            formatTimeStamp.parseDateTime("0001-01-01 00:00:00.0")) // c5
+        .go();
+  }
+
+  @Test
+  public void dateTruncOnTimeStampMillennium() throws Exception {
+    // TODO: It would be good to have some tests on dates in BC period, but looks like currently
Calcite parser is
+    // not accepting date literals in BC.
+    final String query = "SELECT " +
+        "date_trunc('MILLENNIUM', timestamp '2011-2-3 10:11:12.100') as `m1`, " +
+        "date_trunc('MILLENNIUM', timestamp '2000-11-3 10:11:12.100') as `m2`, " +
+        "date_trunc('MILLENNIUM', timestamp '1983-05-18 10:11:12.100') as `m3`, " +
+        "date_trunc('MILLENNIUM', timestamp '990-11-3 10:11:12.100') as `m4`, " +
+        "date_trunc('MILLENNIUM', timestamp '0001-11-3 10:11:12.100') as `m5` " +
+        "FROM sys.version";
+
+    testBuilder()
+        .sqlQuery(query)
+        .unOrdered()
+        .baselineColumns("m1", "m2", "m3", "m4", "m5")
+        .baselineValues(
+            formatTimeStamp.parseDateTime("2001-01-01 00:00:00.0"), // m1
+            formatTimeStamp.parseDateTime("1001-01-01 00:00:00.0"), // m2
+            formatTimeStamp.parseDateTime("1001-01-01 00:00:00.0"), // m3
+            formatTimeStamp.parseDateTime("0001-01-01 00:00:00.0"), // m4
+            formatTimeStamp.parseDateTime("0001-01-01 00:00:00.0")) // m5
+        .go();
+  }
+
+  @Test
+  public void dateTruncOnIntervalYear() throws Exception {
+    final String query = "SELECT " +
+        "date_trunc('SECOND', interval '217-7' year(3) to month) as `second`, " +
+        "date_trunc('MINUTE', interval '217-7' year(3) to month) as `minute`, " +
+        "date_trunc('HOUR', interval '217-7' year(3) to month) as `hour`, " +
+        "date_trunc('DAY', interval '217-7' year(3) to month) as `day`, " +
+        "date_trunc('MONTH', interval '217-7' year(3) to month) as `month`, " +
+        "date_trunc('YEAR', interval '217-7' year(3) to month) as `year`, " +
+        "date_trunc('QUARTER', interval '217-7' year(3) to month) as `quarter`, " +
+        "date_trunc('DECADE', interval '217-7' year(3) to month) as `decade`, " +
+        "date_trunc('CENTURY', interval '217-7' year(3) to month) as `century`, " +
+        "date_trunc('MILLENNIUM', interval '217-7' year(3) to month) as `millennium` " +
+        "FROM sys.version";
+
+    testBuilder()
+        .sqlQuery(query)
+        .unOrdered()
+        .baselineColumns("second", "minute", "hour", "day", "month", "year", "quarter", "decade",
"century", "millennium")
+        .baselineValues(
+            new Period("P217Y7M").normalizedStandard(), // seconds
+            new Period("P217Y7M").normalizedStandard(), // minute
+            new Period("P217Y7M").normalizedStandard(), // hour
+            new Period("P217Y7M").normalizedStandard(), // day
+            new Period("P217Y7M").normalizedStandard(), // month
+            new Period("P217Y").normalizedStandard(), // year
+            new Period("P217Y6M").normalizedStandard(), // quarter
+            new Period("P210Y").normalizedStandard(), // decade
+            new Period("P200Y").normalizedStandard(), // century
+            new Period("PT0S").normalizedStandard()) // millennium
+        .go();
+  }
+
+  @Test
+  public void dateTruncOnIntervalDay() throws Exception {
+    final String query = "SELECT " +
+        "date_trunc('SECOND', interval '200 10:20:30.123' day(3) to second) as `second`,
" +
+        "date_trunc('MINUTE', interval '200 10:20:30.123' day(3) to second) as `minute`,
" +
+        "date_trunc('HOUR', interval '200 10:20:30.123' day(3) to second) as `hour`, " +
+        "date_trunc('DAY', interval '200 10:20:30.123' day(3) to second) as `day`, " +
+        "date_trunc('MONTH', interval '200 10:20:30.123' day(3) to second) as `month`, "
+
+        "date_trunc('YEAR', interval '200 10:20:30.123' day(3) to second) as `year`, " +
+        "date_trunc('QUARTER', interval '200 10:20:30.123' day(3) to second) as `quarter`,
" +
+        "date_trunc('DECADE', interval '200 10:20:30.123' day(3) to second) as `decade`,
" +
+        "date_trunc('CENTURY', interval '200 10:20:30.123' day(3) to second) as `century`,
" +
+        "date_trunc('MILLENNIUM', interval '200 10:20:30.123' day(3) to second) as `millennium`
" +
+        "FROM sys.version";
+
+    testBuilder()
+        .sqlQuery(query)
+        .unOrdered()
+        .baselineColumns("second", "minute", "hour", "day", "month", "year", "quarter", "decade",
"century", "millennium")
+        .baselineValues(
+            new Period().plusDays(200).plusMillis(37230000), // seconds
+            new Period().plusDays(200).plusMillis(37200000), // minute
+            new Period().plusDays(200).plusMillis(36000000), // hour
+            new Period().plusDays(200), // day
+            new Period("PT0S"), // month
+            new Period("PT0S"), // year
+            new Period("PT0S"), // quarter
+            new Period("PT0S"), // decade
+            new Period("PT0S"), // century
+            new Period("PT0S")) // millennium
+        .go();
+  }
+
+  @Test
+  public void testDateTrunc() throws Exception {
+    String query = "select "
+        + "date_trunc('MINUTE', time '2:30:21.5') as TIME1, "
+        + "date_trunc('SECOND', time '2:30:21.5') as TIME2, "
+        + "date_trunc('HOUR', timestamp '1991-05-05 10:11:12.100') as TS1, "
+        + "date_trunc('SECOND', timestamp '1991-05-05 10:11:12.100') as TS2, "
+        + "date_trunc('MONTH', date '2011-2-2') as DATE1, "
+        + "date_trunc('YEAR', date '2011-2-2') as DATE2 "
+        + "from cp.`employee.json` where employee_id < 2";
+
+    DateTime time1 = formatTime.parseDateTime("2:30:00.0");
+    DateTime time2 = formatTime.parseDateTime("2:30:21.0");
+    DateTime ts1 = formatTimeStamp.parseDateTime("1991-05-05 10:00:00.0");
+    DateTime ts2 = formatTimeStamp.parseDateTime("1991-05-05 10:11:12.0");
+    DateTime date1 = formatDate.parseDateTime("2011-02-01");
+    DateTime date2 = formatDate.parseDateTime("2011-01-01");
+
+    testBuilder()
+        .sqlQuery(query)
+        .unOrdered()
+        .baselineColumns("TIME1", "TIME2", "TS1", "TS2", "DATE1", "DATE2")
+        .baselineValues(time1, time2, ts1, ts2, date1, date2)
+        .go();
+  }
+}


Mime
View raw message