DRILL-8340: Add Additional Date Manipulation Functions (#2689)
diff --git a/contrib/udfs/src/main/java/org/apache/drill/exec/udfs/NearestDateUtils.java b/contrib/udfs/src/main/java/org/apache/drill/exec/udfs/DateConversionUtils.java
similarity index 97%
rename from contrib/udfs/src/main/java/org/apache/drill/exec/udfs/NearestDateUtils.java
rename to contrib/udfs/src/main/java/org/apache/drill/exec/udfs/DateConversionUtils.java
index 449c62e..d0652d2 100644
--- a/contrib/udfs/src/main/java/org/apache/drill/exec/udfs/NearestDateUtils.java
+++ b/contrib/udfs/src/main/java/org/apache/drill/exec/udfs/DateConversionUtils.java
@@ -28,7 +28,7 @@
import java.time.temporal.ChronoUnit;
import java.util.Arrays;
-public class NearestDateUtils {
+public class DateConversionUtils {
/**
* Specifies the time grouping to be used with the nearest date function
*/
@@ -48,7 +48,7 @@
SECOND
}
- private static final Logger logger = LoggerFactory.getLogger(NearestDateUtils.class);
+ private static final Logger logger = LoggerFactory.getLogger(DateConversionUtils.class);
/**
* This function takes a Java LocalDateTime object, and an interval string and returns
diff --git a/contrib/udfs/src/main/java/org/apache/drill/exec/udfs/NearestDateFunctions.java b/contrib/udfs/src/main/java/org/apache/drill/exec/udfs/DateFunctions.java
similarity index 65%
rename from contrib/udfs/src/main/java/org/apache/drill/exec/udfs/NearestDateFunctions.java
rename to contrib/udfs/src/main/java/org/apache/drill/exec/udfs/DateFunctions.java
index f61a216..979d603 100644
--- a/contrib/udfs/src/main/java/org/apache/drill/exec/udfs/NearestDateFunctions.java
+++ b/contrib/udfs/src/main/java/org/apache/drill/exec/udfs/DateFunctions.java
@@ -22,10 +22,13 @@
import org.apache.drill.exec.expr.annotations.FunctionTemplate;
import org.apache.drill.exec.expr.annotations.Output;
import org.apache.drill.exec.expr.annotations.Param;
+import org.apache.drill.exec.expr.holders.IntHolder;
+import org.apache.drill.exec.expr.holders.NullableTimeStampHolder;
import org.apache.drill.exec.expr.holders.TimeStampHolder;
import org.apache.drill.exec.expr.holders.VarCharHolder;
-public class NearestDateFunctions {
+
+public class DateFunctions {
/**
* This function takes two arguments, an input date object, and an interval and returns
@@ -53,7 +56,7 @@
* 15SECOND
* SECOND
*/
- @FunctionTemplate(name = "nearestDate",
+ @FunctionTemplate(names = {"nearestDate","nearest_date"},
scope = FunctionTemplate.FunctionScope.SIMPLE,
nulls = FunctionTemplate.NullHandling.NULL_IF_NULL)
public static class NearestDateFunction implements DrillSimpleFunc {
@@ -77,7 +80,7 @@
String input = org.apache.drill.exec.expr.fn.impl.StringFunctionHelpers.toStringFromUTF8(interval.start, interval.end, interval.buffer);
java.time.LocalDateTime ld = java.time.LocalDateTime.ofInstant(java.time.Instant.ofEpochMilli(inputDate.value), java.time.ZoneId.of("UTC"));
- java.time.LocalDateTime td = org.apache.drill.exec.udfs.NearestDateUtils.getDate(ld, input);
+ java.time.LocalDateTime td = DateConversionUtils.getDate(ld, input);
out.value = td.atZone(java.time.ZoneId.of("UTC")).toInstant().toEpochMilli();
}
}
@@ -108,7 +111,7 @@
* 15SECOND
* SECOND
*/
- @FunctionTemplate(name = "nearestDate",
+ @FunctionTemplate(names = {"nearestDate","nearest_date"},
scope = FunctionTemplate.FunctionScope.SIMPLE,
nulls = FunctionTemplate.NullHandling.NULL_IF_NULL)
public static class NearestDateFunctionWithString implements DrillSimpleFunc {
@@ -140,8 +143,66 @@
java.time.format.DateTimeFormatter formatter = java.time.format.DateTimeFormatter.ofPattern(format);
java.time.LocalDateTime dateTime = java.time.LocalDateTime.parse(inputDate, formatter);
- java.time.LocalDateTime td = org.apache.drill.exec.udfs.NearestDateUtils.getDate(dateTime, intervalString);
+ java.time.LocalDateTime td = DateConversionUtils.getDate(dateTime, intervalString);
out.value = td.atZone(java.time.ZoneId.of("UTC")).toInstant().toEpochMilli();
}
}
+
+ @FunctionTemplate(names = {"yearweek","year_week"},
+ scope = FunctionTemplate.FunctionScope.SIMPLE,
+ nulls = FunctionTemplate.NullHandling.NULL_IF_NULL)
+ public static class YearWeekFunction implements DrillSimpleFunc {
+ @Param
+ VarCharHolder inputHolder;
+
+ @Output
+ IntHolder out;
+
+ @Override
+ public void setup() {
+ // noop
+ }
+
+ @Override
+ public void eval() {
+ String input = org.apache.drill.exec.expr.fn.impl.StringFunctionHelpers.toStringFromUTF8(inputHolder.start, inputHolder.end, inputHolder.buffer);
+ java.time.LocalDateTime dt = org.apache.drill.exec.udfs.DateUtilFunctions.getTimestampFromString(input);
+ int week = dt.get(java.time.temporal.IsoFields.WEEK_OF_WEEK_BASED_YEAR);
+ int year = dt.getYear();
+ out.value = (year * 100) + week;
+ }
+ }
+
+ @FunctionTemplate(names = {"to_timestamp"},
+ scope = FunctionTemplate.FunctionScope.SIMPLE,
+ nulls = FunctionTemplate.NullHandling.NULL_IF_NULL)
+ public static class TimestampFunction implements DrillSimpleFunc {
+ /**
+ * This version of the TO_TIMESTAMP function converts strings into timestamps
+ * without the need for a format string. The function will attempt to determine
+ * the date format automatically. If it cannot, the function will return null.
+ */
+ @Param
+ VarCharHolder inputHolder;
+
+ @Output
+ NullableTimeStampHolder out;
+
+ @Override
+ public void setup() {
+ // noop
+ }
+
+ @Override
+ public void eval() {
+ String input = org.apache.drill.exec.expr.fn.impl.StringFunctionHelpers.toStringFromUTF8(inputHolder.start, inputHolder.end, inputHolder.buffer);
+ java.time.LocalDateTime dt = org.apache.drill.exec.udfs.DateUtilFunctions.getTimestampFromString(input);
+ if (dt != null) {
+ out.value = dt.toEpochSecond(java.time.ZoneOffset.UTC) * 1000;
+ out.isSet = 1;
+ } else {
+ out.isSet = 0;
+ }
+ }
+ }
}
diff --git a/contrib/udfs/src/main/java/org/apache/drill/exec/udfs/DateUtilFunctions.java b/contrib/udfs/src/main/java/org/apache/drill/exec/udfs/DateUtilFunctions.java
new file mode 100644
index 0000000..f6380fa
--- /dev/null
+++ b/contrib/udfs/src/main/java/org/apache/drill/exec/udfs/DateUtilFunctions.java
@@ -0,0 +1,164 @@
+/*
+ * 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
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * 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.udfs;
+
+import org.apache.commons.lang3.StringUtils;
+import org.slf4j.Logger;
+import org.slf4j.LoggerFactory;
+
+import java.time.DateTimeException;
+import java.time.LocalDate;
+import java.time.LocalDateTime;
+import java.time.LocalTime;
+import java.util.regex.Matcher;
+import java.util.regex.Pattern;
+
+public class DateUtilFunctions {
+ private static final Logger logger = LoggerFactory.getLogger(DateUtilFunctions.class);
+ // Date Matcher Regexes
+ // yyyy-mm-dd
+ private static final Pattern DB_DATE = Pattern.compile("^(\\d{4})-(\\d{2})-(\\d{2})$");
+
+ // Matches various dates which use slashes
+ private static final Pattern DATE_SLASH = Pattern.compile("^(\\d{1,2})/(\\d{1,2})/(\\d{4})$");
+
+ // Year first with slashes
+ private static final Pattern LEADING_SLASH_DATE = Pattern.compile("^(\\d{4})/(\\d{1,2})/" +
+ "(\\d{1,2})$");
+
+ private static final Pattern TIMESTAMP_PATTERN = Pattern.compile("^(\\d{4})-(\\d{2})-(\\d{2})" +
+ "(?:T|\\s)(\\d{1,2}):(\\d{1,2}):(\\d{1,2})\\.?(\\d*)");
+
+ /** Parses common date strings and returns a {@link LocalDate} of that string.
+ * If the method is unable to parse the string, an error will be logged.
+ * Supports the following formats:
+ *
+ * <ul>
+ * <li>yyyy-MM-dd</li>
+ * <li>MM/dd/yyyy</li>
+ * <li>M/d/yyyy</li>
+ * <li>yyyy/MM/dd</li>
+ * </ul>
+ *
+ * @param inputString An input string containing a date.
+ * @return A {@link LocalDate} of the input string.
+ */
+ public static LocalDate getDateFromString(String inputString) {
+ return getDateFromString(inputString, false);
+ }
+
+ /** Parses common date strings and returns a {@link LocalDate} of that string.
+ * If the method is unable to parse the string, an error will be logged.
+ * Supports the following formats:
+ *
+ * <ul>
+ * <li>yyyy-MM-dd</li>
+ * <li>MM/dd/yyyy</li>
+ * <li>dd/MM/yyyy</li>
+ * <li>M/d/yyyy</li>
+ * <li>yyyy/MM/dd</li>
+ * </ul>
+ *
+ * If the matcher is unable to convert the string, the function returns null.
+ * @param inputString An input string containing a date.
+ * @param leadingDay True if the format has the day first.
+ * @return A {@link LocalDate} of the input string.
+ */
+ public static LocalDate getDateFromString(String inputString, boolean leadingDay) {
+ int year;
+ int month;
+ int day;
+ if (StringUtils.isEmpty(inputString)) {
+ return null;
+ }
+
+ // Clean up input string:
+ inputString = inputString.trim();
+ Matcher dateMatcher;
+ if (DB_DATE.matcher(inputString).matches()) {
+ dateMatcher = DB_DATE.matcher(inputString);
+ dateMatcher.find();
+ year = Integer.parseInt(dateMatcher.group(1));
+ month = Integer.parseInt(dateMatcher.group(2));
+ day = Integer.parseInt(dateMatcher.group(3));
+ } else if (DATE_SLASH.matcher(inputString).matches()) {
+ dateMatcher = DATE_SLASH.matcher(inputString);
+ dateMatcher.find();
+ year = Integer.parseInt(dateMatcher.group(3));
+ if (leadingDay) {
+ month = Integer.parseInt(dateMatcher.group(2));
+ day = Integer.parseInt(dateMatcher.group(1));
+ } else {
+ month = Integer.parseInt(dateMatcher.group(1));
+ day = Integer.parseInt(dateMatcher.group(2));
+ }
+ } else if (LEADING_SLASH_DATE.matcher(inputString).matches()) {
+ dateMatcher = LEADING_SLASH_DATE.matcher(inputString);
+ dateMatcher.find();
+ year = Integer.parseInt(dateMatcher.group(1));
+ month = Integer.parseInt(dateMatcher.group(2));
+ day = Integer.parseInt(dateMatcher.group(3));
+ } else {
+ logger.warn("Unable to parse date {}.", inputString);
+ return null;
+ }
+ try {
+ return LocalDate.of(year,month,day);
+ } catch (DateTimeException e) {
+ logger.warn("Unable to parse date {}.", inputString);
+ return null;
+ }
+ }
+
+ public static LocalDateTime getTimestampFromString(String inputString) {
+ if (StringUtils.isEmpty(inputString)) {
+ return null;
+ }
+ // Clean up input string:
+ inputString = inputString.trim();
+
+ if (inputString.length() <= 10) {
+ LocalDate localDate = getDateFromString(inputString);
+ if (localDate == null) {
+ logger.warn("Unable to parse date {}.", inputString);
+ return null;
+ }
+ LocalTime localTime = LocalTime.of(0,0,0);
+ return LocalDateTime.of(localDate, localTime);
+ }
+
+ Matcher timestampMatcher = TIMESTAMP_PATTERN.matcher(inputString);
+ if (timestampMatcher.find()) {
+ int year = Integer.parseInt(timestampMatcher.group(1));
+ int month = Integer.parseInt(timestampMatcher.group(2));
+ int day = Integer.parseInt(timestampMatcher.group(3));
+ int hour = Integer.parseInt(timestampMatcher.group(4));
+ int minute = Integer.parseInt(timestampMatcher.group(5));
+ int second = Integer.parseInt(timestampMatcher.group(6));
+ int nanos = 0;
+ if (StringUtils.isNotEmpty(timestampMatcher.group(7))) {
+ nanos = Integer.parseInt(timestampMatcher.group(7)) * 1000000;
+ }
+ return LocalDateTime.of(year,month,day,hour,minute,second,nanos);
+ } else {
+ logger.warn("Unable to parse date {}.", inputString);
+ return null;
+ }
+ }
+}
diff --git a/contrib/udfs/src/test/java/org/apache/drill/exec/udfs/TestNearestDateFunctions.java b/contrib/udfs/src/test/java/org/apache/drill/exec/udfs/TestDateFunctions.java
similarity index 90%
rename from contrib/udfs/src/test/java/org/apache/drill/exec/udfs/TestNearestDateFunctions.java
rename to contrib/udfs/src/test/java/org/apache/drill/exec/udfs/TestDateFunctions.java
index 9d4104d..e6e73e0 100644
--- a/contrib/udfs/src/test/java/org/apache/drill/exec/udfs/TestNearestDateFunctions.java
+++ b/contrib/udfs/src/test/java/org/apache/drill/exec/udfs/TestDateFunctions.java
@@ -37,7 +37,7 @@
import static org.junit.Assert.fail;
@Category({UnlikelyTest.class, SqlFunctionTest.class})
-public class TestNearestDateFunctions extends ClusterTest {
+public class TestDateFunctions extends ClusterTest {
@BeforeClass
public static void setup() throws Exception {
@@ -149,7 +149,7 @@
run(query);
fail();
} catch (DrillRuntimeException e) {
- assertTrue(e.getMessage().contains("[BAD_DATE] is not a valid time statement. Expecting: " + Arrays.asList(NearestDateUtils.TimeInterval.values())));
+ assertTrue(e.getMessage().contains("[BAD_DATE] is not a valid time statement. Expecting: " + Arrays.asList(DateConversionUtils.TimeInterval.values())));
}
}
@@ -194,4 +194,33 @@
q4, q4, q4)
.go();
}
+
+ @Test
+ public void testYearWeek() throws Exception {
+ String query = "SELECT yearweek('2012-04-19') as yw, yearweek(CAST('2012-01-19' AS DATE)) AS " +
+ "yw1, year_week(CAST('2020-12-12' AS TIMESTAMP)) AS yw2" +
+ " FROM (VALUES(1))";
+
+ testBuilder()
+ .sqlQuery(query)
+ .ordered()
+ .baselineColumns("yw", "yw1", "yw2")
+ .baselineValues(201216, 201203, 202050)
+ .go();
+ }
+
+ @Test
+ public void testTimestamp() throws Exception {
+ LocalDateTime ts1 = LocalDateTime.of(2020,2,4,0,0,0);
+ LocalDateTime ts2 = LocalDateTime.of(2012,6,12,12,12,45);
+ String query = "SELECT to_timestamp('2020-02-04') AS ts1," +
+ "to_timestamp('2012-06-12T12:12:45.123Z') AS ts2, to_timestamp('AAAAHHHHH') " +
+ "AS ts3 FROM (VALUES(1))";
+ testBuilder()
+ .sqlQuery(query)
+ .unOrdered()
+ .baselineColumns("ts1", "ts2", "ts3")
+ .baselineValues(ts1, ts2, null)
+ .go();
+ }
}
diff --git a/contrib/udfs/src/test/java/org/apache/drill/exec/udfs/TestDateUtils.java b/contrib/udfs/src/test/java/org/apache/drill/exec/udfs/TestDateUtils.java
new file mode 100644
index 0000000..94ca6a5
--- /dev/null
+++ b/contrib/udfs/src/test/java/org/apache/drill/exec/udfs/TestDateUtils.java
@@ -0,0 +1,73 @@
+/*
+ * 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
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * 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.udfs;
+
+import org.junit.Test;
+
+import java.time.LocalDate;
+import java.time.LocalDateTime;
+
+import static org.junit.Assert.assertEquals;
+import static org.junit.Assert.assertNull;
+
+public class TestDateUtils {
+
+ @Test
+ public void testDateFromString() {
+ LocalDate testDate = LocalDate.of(2022, 3,14);
+ assertEquals(testDate, DateUtilFunctions.getDateFromString("2022-03-14"));
+ assertEquals(testDate, DateUtilFunctions.getDateFromString("3/14/2022"));
+ assertEquals(testDate, DateUtilFunctions.getDateFromString("14/03/2022", true));
+ assertEquals(testDate, DateUtilFunctions.getDateFromString("2022/3/14"));
+
+ // Test bad dates
+ assertNull(DateUtilFunctions.getDateFromString(null));
+ assertNull(DateUtilFunctions.getDateFromString("1975-13-56"));
+ assertNull(DateUtilFunctions.getDateFromString("1975-1s"));
+ }
+
+ @Test
+ public void testTimestampFromString() {
+ LocalDateTime testNoSecondFragments = LocalDateTime.of(2022,4,19,17,3,46);
+ LocalDateTime test1Digit = LocalDateTime.of(2022,4,19,17,3,46, 1000000);
+ LocalDateTime test2Digit = LocalDateTime.of(2022,4,19,17,3,46, 13000000);
+ LocalDateTime test3Digit = LocalDateTime.of(2022,4,19,17,3,46, 342000000);
+
+ assertEquals(testNoSecondFragments,
+ DateUtilFunctions.getTimestampFromString("2022-04-19 17:03:46"));
+ assertEquals(testNoSecondFragments,
+ DateUtilFunctions.getTimestampFromString("2022-04-19T17:03:46"));
+ assertEquals(testNoSecondFragments,
+ DateUtilFunctions.getTimestampFromString("2022-04-19T17:03:46.000Z"));
+ assertEquals(test1Digit,
+ DateUtilFunctions.getTimestampFromString("2022-04-19T17:03:46.1Z"));
+ assertEquals(test1Digit,
+ DateUtilFunctions.getTimestampFromString("2022-04-19T17:03:46.001Z"));
+ assertEquals(test2Digit,
+ DateUtilFunctions.getTimestampFromString("2022-04-19T17:03:46.13Z"));
+ assertEquals(test2Digit,
+ DateUtilFunctions.getTimestampFromString("2022-04-19T17:03:46.013Z"));
+ assertEquals(test3Digit,
+ DateUtilFunctions.getTimestampFromString("2022-04-19 17:03:46.342Z"));
+
+ // Test bad dates
+ assertNull(DateUtilFunctions.getTimestampFromString(null));
+ assertNull(DateUtilFunctions.getTimestampFromString(""));
+ }
+}
diff --git a/exec/java-exec/src/main/codegen/data/ExtractTypes.tdd b/exec/java-exec/src/main/codegen/data/ExtractTypes.tdd
index 2d48699..20c446b 100644
--- a/exec/java-exec/src/main/codegen/data/ExtractTypes.tdd
+++ b/exec/java-exec/src/main/codegen/data/ExtractTypes.tdd
@@ -17,6 +17,7 @@
#
{
- toTypes: [Second, Minute, Hour, Day, Week, Month, Year],
+ toTypes: [Second, Minute, Hour, Day, Week, Dow, DayOfWeek, Month, Quarter, Doy, DayOfYear, Year,
+ Epoch],
fromTypes: [Date, Time, TimeStamp, Interval, IntervalDay, IntervalYear]
}
diff --git a/exec/java-exec/src/main/codegen/templates/DateIntervalFunctionTemplates/Extract.java b/exec/java-exec/src/main/codegen/templates/DateIntervalFunctionTemplates/Extract.java
index b09956a..3b496ff 100644
--- a/exec/java-exec/src/main/codegen/templates/DateIntervalFunctionTemplates/Extract.java
+++ b/exec/java-exec/src/main/codegen/templates/DateIntervalFunctionTemplates/Extract.java
@@ -38,7 +38,9 @@
<#list extract.fromTypes as fromUnit>
<#list extract.toTypes as toUnit>
<#if fromUnit == "Date" || fromUnit == "Time" || fromUnit == "TimeStamp">
-<#if !(fromUnit == "Time" && (toUnit == "Year" || toUnit == "Month" || toUnit == "Week" || toUnit == "Day"))>
+<#if !(fromUnit == "Time" && (toUnit == "Year" || toUnit == "Quarter" || toUnit == "Month"
+ || toUnit == "Week" || toUnit == "Day" || toUnit == "Epoch"
+ || toUnit == "Doy" || toUnit == "DayOfYear" || toUnit == "Dow" || toUnit == "DayOfWeek"))>
@FunctionTemplate(name = "extract${toUnit}", scope = FunctionTemplate.FunctionScope.SIMPLE,
nulls = FunctionTemplate.NullHandling.NULL_IF_NULL)
public static class ${toUnit}From${fromUnit} implements DrillSimpleFunc {
@@ -67,12 +69,20 @@
out.value = dateTime.getHourOfDay();
<#elseif toUnit = "Day">
out.value = dateTime.getDayOfMonth();
+ <#elseif toUnit = "Dow">
+ out.value = dateTime.getDayOfWeek();
+ <#elseif toUnit = "Doy">
+ out.value = dateTime.getDayOfYear();
<#elseif toUnit = "Week">
out.value = dateTime.getWeekOfWeekyear();
<#elseif toUnit = "Month">
out.value = dateTime.getMonthOfYear();
+ <#elseif toUnit = "Quarter">
+ out.value = ((int) dateTime.getMonthOfYear() / 4) + 1;
<#elseif toUnit = "Year">
out.value = dateTime.getYear();
+ <#elseif toUnit = "Epoch">
+ out.value = dateTime.getMillis();
</#if>
}
}
@@ -95,6 +105,8 @@
<#if fromUnit == "Interval">
<#if toUnit == "Year">
out.value = (in.months / org.apache.drill.exec.vector.DateUtilities.yearsToMonths);
+ <#elseif toUnit == "Quarter">
+ out.value = (in.months / org.apache.drill.exec.vector.DateUtilities.yearsToQuarter);
<#elseif toUnit == "Month">
out.value = (in.months % org.apache.drill.exec.vector.DateUtilities.yearsToMonths);
<#elseif toUnit == "Week">
@@ -115,6 +127,8 @@
out.value = 0;
<#elseif toUnit == "Week">
out.value = 0;
+ <#elseif toUnit == "Quarter">
+ out.value = 0;
<#elseif toUnit == "Day">
out.value = in.days;
<#elseif toUnit == "Hour">
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 022a9b8..5fdb1ff 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
@@ -570,14 +570,25 @@
// Get the unit of time to be extracted
String timeUnitStr = ((ValueExpressions.QuotedString) args.get(0)).value;
-
- TimeUnit timeUnit = TimeUnit.valueOf(timeUnitStr);
+ TimeUnit timeUnit;
+ // Clean up day of XXX
+ if (timeUnitStr.contentEquals("DAYOFWEEK")) {
+ timeUnit = TimeUnit.DOW;
+ } else if (timeUnitStr.contentEquals("DAYOFYEAR")) {
+ timeUnit = TimeUnit.DOY;
+ } else {
+ timeUnit = TimeUnit.valueOf(timeUnitStr);
+ }
switch (timeUnit) {
case YEAR:
+ case QUARTER:
case MONTH:
case WEEK:
case DAY:
+ case DOW:
+ case DOY:
+ case EPOCH:
case HOUR:
case MINUTE:
case SECOND:
@@ -585,7 +596,9 @@
functionName += functionPostfix;
return FunctionCallFactory.createExpression(functionName, args.subList(1, 2));
default:
- throw new UnsupportedOperationException("extract function supports the following time units: YEAR, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND");
+ throw new UnsupportedOperationException("extract function supports the following " +
+ "time units: YEAR, QUARTER, MONTH, WEEK, DAY, DAYOFWEEK, DAYOFYEAR, EPOCH, HOUR, " +
+ "MINUTE, SECOND");
}
}
case "timestampdiff": {
diff --git a/exec/java-exec/src/main/java/org/apache/drill/exec/planner/sql/TypeInferenceUtils.java b/exec/java-exec/src/main/java/org/apache/drill/exec/planner/sql/TypeInferenceUtils.java
index 44e46bd..5b209db 100644
--- a/exec/java-exec/src/main/java/org/apache/drill/exec/planner/sql/TypeInferenceUtils.java
+++ b/exec/java-exec/src/main/java/org/apache/drill/exec/planner/sql/TypeInferenceUtils.java
@@ -946,11 +946,25 @@
* For Extract and date_part functions, infer the return types based on timeUnit
*/
public static SqlTypeName getSqlTypeNameForTimeUnit(String timeUnitStr) {
- TimeUnit timeUnit = TimeUnit.valueOf(timeUnitStr);
+ TimeUnit timeUnit;
+
+ // Clean up day of XXX
+ if (timeUnitStr.contentEquals("DAYOFWEEK")) {
+ timeUnit = TimeUnit.DOW;
+ } else if (timeUnitStr.contentEquals("DAYOFYEAR")) {
+ timeUnit = TimeUnit.DOY;
+ } else {
+ timeUnit = TimeUnit.valueOf(timeUnitStr);
+ }
+
switch (timeUnit) {
case YEAR:
+ case QUARTER:
case MONTH:
case WEEK:
+ case EPOCH:
+ case DOY:
+ case DOW:
case DAY:
case HOUR:
case MINUTE:
@@ -960,7 +974,8 @@
default:
throw UserException
.functionError()
- .message("extract function supports the following time units: YEAR, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND")
+ .message("extract function supports the following time units: YEAR, QUARTER, MONTH, " +
+ "WEEK, DAY, DAYOFWEEK, DAYOFYEAR, EPOCH, HOUR, MINUTE, SECOND")
.build(logger);
}
}
diff --git a/exec/java-exec/src/test/java/org/apache/drill/exec/fn/impl/TestNewDateFunctions.java b/exec/java-exec/src/test/java/org/apache/drill/exec/fn/impl/TestNewDateFunctions.java
index 5e5fc01..626b6d1 100644
--- a/exec/java-exec/src/test/java/org/apache/drill/exec/fn/impl/TestNewDateFunctions.java
+++ b/exec/java-exec/src/test/java/org/apache/drill/exec/fn/impl/TestNewDateFunctions.java
@@ -142,6 +142,17 @@
}
@Test
+ public void testExtractQuarter() throws Exception {
+ testBuilder()
+ .sqlQuery("SELECT EXTRACT(QUARTER FROM hire_date) AS col FROM cp.`employee.json` WHERE " +
+ "last_name='Nowmer'")
+ .unOrdered()
+ .baselineColumns("col")
+ .baselineValues(4L)
+ .go();
+ }
+
+ @Test
public void testLocalTimestamp() throws Exception {
testBuilder()
.sqlQuery("select extract(day from localtimestamp) = extract(day from current_date) as col from cp.`employee.json` limit 1")
diff --git a/exec/vector/src/main/java/org/apache/drill/exec/vector/DateUtilities.java b/exec/vector/src/main/java/org/apache/drill/exec/vector/DateUtilities.java
index 9090192..4cfc8da 100644
--- a/exec/vector/src/main/java/org/apache/drill/exec/vector/DateUtilities.java
+++ b/exec/vector/src/main/java/org/apache/drill/exec/vector/DateUtilities.java
@@ -36,6 +36,7 @@
public class DateUtilities {
public static final int yearsToMonths = 12;
+ public static final int yearsToQuarter = 4;
public static final int daysToWeeks = 7;
public static final int hoursToMillis = 60 * 60 * 1000;
public static final int minutesToMillis = 60 * 1000;
