drill-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From amansi...@apache.org
Subject [2/2] drill git commit: DRILL-4864: Add ANSI format for date/time functions
Date Sat, 11 Feb 2017 02:18:08 GMT
DRILL-4864: Add ANSI format for date/time functions

DRILL-4864: Add ANSI format for date/time functions(review changes)

close apache/drill#581


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

Branch: refs/heads/master
Commit: c9a6ac4fc8859693b7b0a71885afb700f81e345c
Parents: f80d77e
Author: Serhii-Harnyk <serhii.harnyk@gmail.com>
Authored: Thu Sep 1 17:48:02 2016 +0300
Committer: Aman Sinha <asinha@maprtech.com>
Committed: Fri Feb 10 14:24:43 2017 -0800

----------------------------------------------------------------------
 .../SqlToDateTypeFunctions.java                 |  84 ++++++
 .../fn/impl/testing/TestDateConversions.java    | 223 ++++++++++++++++
 .../common/expression/fn/JodaDateValidator.java | 255 +++++++++++++++++++
 .../expression/fn/JodaDateValidatorTest.java    | 203 +++++++++++++++
 4 files changed, 765 insertions(+)
----------------------------------------------------------------------


http://git-wip-us.apache.org/repos/asf/drill/blob/c9a6ac4f/exec/java-exec/src/main/codegen/templates/DateIntervalFunctionTemplates/SqlToDateTypeFunctions.java
----------------------------------------------------------------------
diff --git a/exec/java-exec/src/main/codegen/templates/DateIntervalFunctionTemplates/SqlToDateTypeFunctions.java
b/exec/java-exec/src/main/codegen/templates/DateIntervalFunctionTemplates/SqlToDateTypeFunctions.java
new file mode 100644
index 0000000..0385826
--- /dev/null
+++ b/exec/java-exec/src/main/codegen/templates/DateIntervalFunctionTemplates/SqlToDateTypeFunctions.java
@@ -0,0 +1,84 @@
+/**
+ * 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.
+ */
+
+import org.apache.drill.exec.expr.annotations.Workspace;
+
+<@pp.dropOutputFile/>
+
+<#list dateIntervalFunc.dates as type>
+
+<@pp.changeOutputFile name = "/org/apache/drill/exec/expr/fn/impl/SqlTo${type}.java"/>
+
+<#include "/@includes/license.ftl"/>
+
+package org.apache.drill.exec.expr.fn.impl;
+
+import org.apache.drill.exec.expr.DrillSimpleFunc;
+import org.apache.drill.exec.expr.annotations.FunctionTemplate;
+import org.apache.drill.exec.expr.annotations.FunctionTemplate.NullHandling;
+import org.apache.drill.exec.expr.annotations.Output;
+import org.apache.drill.exec.expr.annotations.Workspace;
+import org.apache.drill.exec.expr.annotations.Param;
+import org.apache.drill.exec.expr.holders.*;
+
+/*
+ * This class is generated using freemarker and the ${.template_name} template.
+ */
+
+@FunctionTemplate(name = "sql_to_${type?lower_case}",
+                  scope = FunctionTemplate.FunctionScope.SIMPLE,
+                  nulls = NullHandling.NULL_IF_NULL)
+public class SqlTo${type} implements DrillSimpleFunc {
+
+  @Param  VarCharHolder left;
+  @Param  VarCharHolder right;
+  @Workspace org.joda.time.format.DateTimeFormatter format;
+  @Output ${type}Holder out;
+
+  public void setup() {
+    // Get the desired output format
+    String formatString = org.apache.drill.exec.expr.fn.impl.StringFunctionHelpers.getStringFromVarCharHolder(right);
+    String pattern = org.apache.drill.common.expression.fn.JodaDateValidator.toJodaFormat(formatString);
+    try {
+      format = org.joda.time.format.DateTimeFormat.forPattern(pattern);
+    } catch (IllegalArgumentException e) {
+      throw org.apache.drill.common.exceptions.UserException.functionError(e)
+        .message("Error parsing formatter %s in %s function", formatString, "sql_to_${type?lower_case}")
+        .build();
+    }
+  }
+
+  public void eval() {
+    // Get the input
+    String input = org.apache.drill.exec.expr.fn.impl.StringFunctionHelpers.getStringFromVarCharHolder(left);
+    try {
+      <#if type == "Date">
+      out.value = org.joda.time.DateMidnight.parse(input, format).withZoneRetainFields(org.joda.time.DateTimeZone.UTC).getMillis();
+      <#elseif type == "TimeStamp">
+      out.value = org.joda.time.DateTime.parse(input, format).withZoneRetainFields(org.joda.time.DateTimeZone.UTC).getMillis();
+      <#elseif type == "Time">
+      out.value = (int) format.parseDateTime(input).withZoneRetainFields(org.joda.time.DateTimeZone.UTC).getMillis();
+      </#if>
+    } catch (IllegalArgumentException e) {
+      throw org.apache.drill.common.exceptions.UserException.functionError(e)
+        .message("Error parsing date-time %s in %s function", input, "sql_to_${type?lower_case}")
+        .build();
+    }
+  }
+}
+</#list>
\ No newline at end of file

http://git-wip-us.apache.org/repos/asf/drill/blob/c9a6ac4f/exec/java-exec/src/test/java/org/apache/drill/exec/fn/impl/testing/TestDateConversions.java
----------------------------------------------------------------------
diff --git a/exec/java-exec/src/test/java/org/apache/drill/exec/fn/impl/testing/TestDateConversions.java
b/exec/java-exec/src/test/java/org/apache/drill/exec/fn/impl/testing/TestDateConversions.java
new file mode 100644
index 0000000..bf35d10
--- /dev/null
+++ b/exec/java-exec/src/test/java/org/apache/drill/exec/fn/impl/testing/TestDateConversions.java
@@ -0,0 +1,223 @@
+/*
+* 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.fn.impl.testing;
+
+import org.apache.drill.BaseTestQuery;
+import org.apache.drill.common.exceptions.UserException;
+import org.junit.AfterClass;
+import org.junit.BeforeClass;
+import org.junit.Test;
+
+import java.io.BufferedWriter;
+import java.io.File;
+import java.io.FileWriter;
+import java.io.IOException;
+
+import static org.hamcrest.CoreMatchers.startsWith;
+import static org.junit.Assert.assertThat;
+public class TestDateConversions extends BaseTestQuery {
+
+  private static String TEMP_DIR;
+
+  @BeforeClass
+  public static void generateTestFiles() throws IOException {
+    File path = new File(BaseTestQuery.getTempDir("json/input"));
+    path.mkdirs();
+    TEMP_DIR = path.toPath().toString();
+
+    try (BufferedWriter writer = new BufferedWriter(new FileWriter(new File(path, "joda_postgres_date.json"))))
{
+      writer.write("{\"date1\" : \"1970-01-02\",\n \"date2\" : \"01021970\",\n \"date3\"
: \"32/1970\"\n}\n"
+        + "{\"date1\" : \"2010-05-03\",\n \"date2\" : \"01021970\",\n \"date3\" : \"64/2010\"\n}");
+    }
+
+    try (BufferedWriter writer = new BufferedWriter(new FileWriter(new File(path, "joda_postgres_time.json"))))
{
+      writer.write("{\"time1\" : \"23:11:59\",\n \"time2\" : \"11:11:59pm\",\n \"time3\"
: \"591111pm\"\n}\n"
+        + "{\"time1\" : \"17:33:41\",\n \"time2\" : \"5:33:41am\",\n \"time3\" : \"413305pm\"\n}");
+    }
+
+    try (BufferedWriter writer = new BufferedWriter(new FileWriter(new File(path, "joda_postgres_date_time.json"))))
{
+      writer.write("{ \"time1\" : \"1970-01-0223:11:59\",\n \"time2\" : \"0102197011:11:59pm\",\n"
+        + "  \"time3\" : \"32/1970591111pm\"\n}\n"
+        + "{\"time1\" : \"2010-05-0317:33:41\",\n \"time2\" : \"0102197005:33:41am\",\n"
+        + "  \"time3\" : \"64/2010413305pm\"\n}");
+    }
+  }
+
+  @AfterClass
+  public static void deleteTestFiles() throws IOException {
+    java.nio.file.Files.delete(new File(TEMP_DIR, "joda_postgres_date.json").toPath());
+    java.nio.file.Files.delete(new File(TEMP_DIR, "joda_postgres_time.json").toPath());
+    java.nio.file.Files.delete(new File(TEMP_DIR, "joda_postgres_date_time.json").toPath());
+  }
+
+  @Test
+  public void testJodaDate() throws Exception {
+    String query = String.format("SELECT to_date(date1, 'yyyy-dd-MM') = "
+      + "to_date(date2, 'ddMMyyyy') as col1, " + "to_date(date1, 'yyyy-dd-MM') = "
+      + "to_date(date3, 'D/yyyy') as col2 "
+      + "from dfs_test.`%s/joda_postgres_date.json`", TEMP_DIR);
+
+    testBuilder()
+      .sqlQuery(query)
+      .unOrdered()
+      .baselineColumns("col1", "col2")
+      .baselineValues(true, true)
+      .baselineValues(false, true)
+      .go();
+  }
+
+  @Test
+  public void testPostgresDate() throws Exception {
+    String query = String.format("SELECT sql_to_date(date1, 'yyyy-DD-MM') = "
+      + "sql_to_date(date2, 'DDMMyyyy') as col1, "
+      + "sql_to_date(date1, 'yyyy-DD-MM') = "
+      + "sql_to_date(date3, 'DDD/yyyy') as col2 "
+      + "from dfs_test.`%s/joda_postgres_date.json`", TEMP_DIR);
+
+    testBuilder()
+      .sqlQuery(query)
+      .unOrdered()
+      .baselineColumns("col1", "col2")
+      .baselineValues(true, true)
+      .baselineValues(false, true)
+      .go();
+  }
+
+  @Test
+  public void testJodaTime() throws Exception {
+    String query = String.format("SELECT to_time(time1, 'H:m:ss') = "
+      + "to_time(time2, 'h:m:ssa') as col1, "
+      + "to_time(time1, 'H:m:ss') = "
+      + "to_time(time3, 'ssmha') as col2 "
+      + "from dfs_test.`%s/joda_postgres_time.json`", TEMP_DIR);
+
+    testBuilder()
+      .sqlQuery(query)
+      .unOrdered()
+      .baselineColumns("col1", "col2")
+      .baselineValues(true, true)
+      .baselineValues(false, true)
+      .go();
+  }
+
+  @Test
+  public void testPostgresTime() throws Exception {
+    String query = String.format("SELECT sql_to_time(time1, 'HH24:MI:SS') = "
+      + "sql_to_time(time2, 'HH12:MI:SSam') as col1, "
+      + "sql_to_time(time1, 'HH24:MI:SS') = "
+      + "sql_to_time(time3, 'SSMIHH12am') as col2 "
+      + "from dfs_test.`%s/joda_postgres_time.json`", TEMP_DIR);
+
+    testBuilder()
+      .sqlQuery(query)
+      .unOrdered()
+      .baselineColumns("col1", "col2")
+      .baselineValues(true, true)
+      .baselineValues(false, true)
+      .go();
+  }
+
+  @Test
+  public void testPostgresDateTime() throws Exception {
+    String query = String.format("SELECT sql_to_timestamp(time1, 'yyyy-DD-MMHH24:MI:SS')
= "
+      + "sql_to_timestamp(time2, 'DDMMyyyyHH12:MI:SSam') as col1, "
+      + "sql_to_timestamp(time1, 'yyyy-DD-MMHH24:MI:SS') = "
+      + "sql_to_timestamp(time3, 'DDD/yyyySSMIHH12am') as col2 "
+      + "from dfs_test.`%s/joda_postgres_date_time.json`", TEMP_DIR);
+
+    testBuilder()
+      .sqlQuery(query)
+      .unOrdered()
+      .baselineColumns("col1", "col2")
+      .baselineValues(true, true)
+      .baselineValues(false, true)
+      .go();
+
+  }
+
+  @Test
+  public void testJodaDateTime() throws Exception {
+    String query = String.format("SELECT to_timestamp(time1, 'yyyy-dd-MMH:m:ss') = "
+      + "to_timestamp(time2, 'ddMMyyyyh:m:ssa') as col1, "
+      + "to_timestamp(time1, 'yyyy-dd-MMH:m:ss') = "
+      + "to_timestamp(time3, 'DDD/yyyyssmha') as col2 "
+      + "from dfs_test.`%s/joda_postgres_date_time.json`", TEMP_DIR);
+
+    testBuilder()
+      .sqlQuery(query)
+      .unOrdered()
+      .baselineColumns("col1", "col2")
+      .baselineValues(true, true)
+      .baselineValues(false, true)
+      .go();
+  }
+
+  @Test
+  public void testJodaDateTimeNested() throws Exception {
+    String query = String.format("SELECT date_add(to_date(time1, concat('yyyy-dd-MM','H:m:ss')),
22)= "
+      + "date_add(to_date(time2, concat('ddMMyyyy', 'h:m:ssa')), 22) as col1, "
+      + "date_add(to_date(time1, concat('yyyy-dd-MM', 'H:m:ss')), 22) = "
+      + "date_add(to_date(time3, concat('DDD/yyyy', 'ssmha')), 22) as col2 "
+      + "from dfs_test.`%s/joda_postgres_date_time.json`", TEMP_DIR);
+
+    testBuilder()
+      .sqlQuery(query)
+      .unOrdered()
+      .baselineColumns("col1", "col2")
+      .baselineValues(true, true)
+      .baselineValues(false, true)
+      .go();
+
+  }
+
+  @Test
+  public void testPostgresDateTimeNested() throws Exception {
+    String query = String.format("SELECT date_add(sql_to_date(time1, concat('yyyy-DD-MM',
'HH24:MI:SS')), 22) = "
+      + "date_add(sql_to_date(time2, concat('DDMMyyyy', 'HH12:MI:SSam')), 22) as col1, "
+      + "date_add(sql_to_date(time1, concat('yyyy-DD-MM', 'HH24:MI:SS')), 10) = "
+      + "date_add(sql_to_date(time3, concat('DDD/yyyySSMI', 'HH12am')), 10) as col2 "
+      + "from dfs_test.`%s/joda_postgres_date_time.json`", TEMP_DIR);
+
+    testBuilder()
+      .sqlQuery(query)
+      .unOrdered()
+      .baselineColumns("col1", "col2")
+      .baselineValues(true, true)
+      .baselineValues(false, true)
+      .go();
+  }
+
+  @Test(expected = UserException.class)
+  public void testPostgresPatternFormatError() throws Exception {
+    try {
+      test("SELECT sql_to_date('1970-01-02', 'yyyy-QQ-MM') from (values(1))");
+    } catch (UserException e) {
+      assertThat("No expected current \"FUNCTION ERROR\"", e.getMessage(), startsWith("FUNCTION
ERROR"));
+      throw e;
+    }
+  }
+
+  @Test(expected = UserException.class)
+  public void testPostgresDateFormatError() throws Exception {
+    try {
+      test("SELECT sql_to_date('1970/01/02', 'yyyy-DD-MM') from (values(1))");
+    } catch (UserException e) {
+      assertThat("No expected current \"FUNCTION ERROR\"", e.getMessage(), startsWith("FUNCTION
ERROR"));
+      throw e;
+    }
+  }
+}

http://git-wip-us.apache.org/repos/asf/drill/blob/c9a6ac4f/logical/src/main/java/org/apache/drill/common/expression/fn/JodaDateValidator.java
----------------------------------------------------------------------
diff --git a/logical/src/main/java/org/apache/drill/common/expression/fn/JodaDateValidator.java
b/logical/src/main/java/org/apache/drill/common/expression/fn/JodaDateValidator.java
new file mode 100644
index 0000000..341af0f
--- /dev/null
+++ b/logical/src/main/java/org/apache/drill/common/expression/fn/JodaDateValidator.java
@@ -0,0 +1,255 @@
+/*
+* 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.common.expression.fn;
+
+import com.google.common.collect.Maps;
+import org.apache.commons.lang3.StringUtils;
+
+import java.util.Comparator;
+import java.util.Map;
+
+import static org.apache.drill.common.expression.fn.JodaDateValidator.PostgresDateTimeConstant.POSTGRES_ABR_NAME_OF_MONTH;
+import static org.apache.drill.common.expression.fn.JodaDateValidator.PostgresDateTimeConstant.POSTGRES_DAY_OF_MONTH;
+import static org.apache.drill.common.expression.fn.JodaDateValidator.PostgresDateTimeConstant.POSTGRES_DAY_OF_WEEK;
+import static org.apache.drill.common.expression.fn.JodaDateValidator.PostgresDateTimeConstant.POSTGRES_DAY_OF_YEAR;
+import static org.apache.drill.common.expression.fn.JodaDateValidator.PostgresDateTimeConstant.POSTGRES_FULL_ERA_NAME;
+import static org.apache.drill.common.expression.fn.JodaDateValidator.PostgresDateTimeConstant.POSTGRES_FULL_NAME_OF_DAY;
+import static org.apache.drill.common.expression.fn.JodaDateValidator.PostgresDateTimeConstant.POSTGRES_HALFDAY_AM;
+import static org.apache.drill.common.expression.fn.JodaDateValidator.PostgresDateTimeConstant.POSTGRES_HALFDAY_PM;
+import static org.apache.drill.common.expression.fn.JodaDateValidator.PostgresDateTimeConstant.POSTGRES_HOUR_12_NAME;
+import static org.apache.drill.common.expression.fn.JodaDateValidator.PostgresDateTimeConstant.POSTGRES_HOUR_12_OTHER_NAME;
+import static org.apache.drill.common.expression.fn.JodaDateValidator.PostgresDateTimeConstant.POSTGRES_HOUR_24_NAME;
+import static org.apache.drill.common.expression.fn.JodaDateValidator.PostgresDateTimeConstant.POSTGRES_ISO_1YEAR;
+import static org.apache.drill.common.expression.fn.JodaDateValidator.PostgresDateTimeConstant.POSTGRES_ISO_2YEAR;
+import static org.apache.drill.common.expression.fn.JodaDateValidator.PostgresDateTimeConstant.POSTGRES_ISO_3YEAR;
+import static org.apache.drill.common.expression.fn.JodaDateValidator.PostgresDateTimeConstant.POSTGRES_ISO_4YEAR;
+import static org.apache.drill.common.expression.fn.JodaDateValidator.PostgresDateTimeConstant.POSTGRES_ISO_WEEK_OF_YEAR;
+import static org.apache.drill.common.expression.fn.JodaDateValidator.PostgresDateTimeConstant.POSTGRES_MILLISECOND_OF_MINUTE_NAME;
+import static org.apache.drill.common.expression.fn.JodaDateValidator.PostgresDateTimeConstant.POSTGRES_MINUTE_OF_HOUR_NAME;
+import static org.apache.drill.common.expression.fn.JodaDateValidator.PostgresDateTimeConstant.POSTGRES_MONTH;
+import static org.apache.drill.common.expression.fn.JodaDateValidator.PostgresDateTimeConstant.POSTGRES_NAME_OF_DAY;
+import static org.apache.drill.common.expression.fn.JodaDateValidator.PostgresDateTimeConstant.POSTGRES_NAME_OF_MONTH;
+import static org.apache.drill.common.expression.fn.JodaDateValidator.PostgresDateTimeConstant.POSTGRES_SECOND_OF_MINUTE_NAME;
+import static org.apache.drill.common.expression.fn.JodaDateValidator.PostgresDateTimeConstant.POSTGRES_WEEK_OF_YEAR;
+import static org.apache.drill.common.expression.fn.JodaDateValidator.PostgresDateTimeConstant.POSTGRES_YEAR;
+import static org.apache.drill.common.expression.fn.JodaDateValidator.PostgresDateTimeConstant.PREFIX_FM;
+import static org.apache.drill.common.expression.fn.JodaDateValidator.PostgresDateTimeConstant.PREFIX_FX;
+import static org.apache.drill.common.expression.fn.JodaDateValidator.PostgresDateTimeConstant.PREFIX_TM;
+
+public class JodaDateValidator {
+
+  public enum PostgresDateTimeConstant {
+
+    // patterns for replacing
+    POSTGRES_FULL_NAME_OF_DAY(true, "day"),
+    POSTGRES_DAY_OF_YEAR(false, "ddd"),
+    POSTGRES_DAY_OF_MONTH(false, "dd"),
+    POSTGRES_DAY_OF_WEEK(false, "d"),
+    POSTGRES_NAME_OF_MONTH(true, "month"),
+    POSTGRES_ABR_NAME_OF_MONTH(true, "mon"),
+    POSTGRES_YEAR(false, "y"),
+    POSTGRES_ISO_4YEAR(false, "iyyy"),
+    POSTGRES_ISO_3YEAR(false, "iyy"),
+    POSTGRES_ISO_2YEAR(false, "iy"),
+    POSTGRES_ISO_1YEAR(false, "i"),
+    POSTGRES_FULL_ERA_NAME(false, "ee"),
+    POSTGRES_NAME_OF_DAY(true, "dy"),
+    POSTGRES_HOUR_12_NAME(false, "hh"),
+    POSTGRES_HOUR_12_OTHER_NAME(false, "hh12"),
+    POSTGRES_HOUR_24_NAME(false, "hh24"),
+    POSTGRES_MINUTE_OF_HOUR_NAME(false, "mi"),
+    POSTGRES_SECOND_OF_MINUTE_NAME(false, "ss"),
+    POSTGRES_MILLISECOND_OF_MINUTE_NAME(false, "ms"),
+    POSTGRES_WEEK_OF_YEAR(false, "ww"),
+    POSTGRES_ISO_WEEK_OF_YEAR(false, "iw"),
+    POSTGRES_MONTH(false, "mm"),
+    POSTGRES_HALFDAY_AM(false, "am"),
+    POSTGRES_HALFDAY_PM(false, "pm"),
+
+    // pattern modifiers for deleting
+    PREFIX_FM(false, "fm"),
+    PREFIX_FX(false, "fx"),
+    PREFIX_TM(false, "tm");
+
+    private final boolean hasCamelCasing;
+    private final String name;
+
+    PostgresDateTimeConstant(boolean hasCamelCasing, String name) {
+      this.hasCamelCasing = hasCamelCasing;
+      this.name = name;
+    }
+
+    public boolean hasCamelCasing() {
+      return hasCamelCasing;
+    }
+
+    public String getName() {
+      return name;
+    }
+  }
+
+  private static final Map<PostgresDateTimeConstant, String> postgresToJodaMap = Maps.newTreeMap(new
LengthDescComparator());
+
+  public static final String POSTGRES_ESCAPE_CHARACTER = "\"";
+
+  // jodaTime patterns
+  public static final String JODA_FULL_NAME_OF_DAY = "EEEE";
+  public static final String JODA_DAY_OF_YEAR = "D";
+  public static final String JODA_DAY_OF_MONTH = "d";
+  public static final String JODA_DAY_OF_WEEK = "e";
+  public static final String JODA_NAME_OF_MONTH = "MMMM";
+  public static final String JODA_ABR_NAME_OF_MONTH = "MMM";
+  public static final String JODA_YEAR = "y";
+  public static final String JODA_ISO_4YEAR = "xxxx";
+  public static final String JODA_ISO_3YEAR = "xxx";
+  public static final String JODA_ISO_2YEAR = "xx";
+  public static final String JODA_ISO_1YEAR = "x";
+  public static final String JODA_FULL_ERA_NAME = "G";
+  public static final String JODA_NAME_OF_DAY = "E";
+  public static final String JODA_HOUR_12_NAME = "h";
+  public static final String JODA_HOUR_24_NAME = "H";
+  public static final String JODA_MINUTE_OF_HOUR_NAME = "m";
+  public static final String JODA_SECOND_OF_MINUTE_NAME = "ss";
+  public static final String JODA_MILLISECOND_OF_MINUTE_NAME = "SSS";
+  public static final String JODA_WEEK_OF_YEAR = "w";
+  public static final String JODA_MONTH = "MM";
+  public static final String JODA_HALFDAY = "aa";
+  public static final String JODA_ESCAPE_CHARACTER = "'";
+  public static final String EMPTY_STRING = "";
+
+  static {
+    postgresToJodaMap.put(POSTGRES_FULL_NAME_OF_DAY, JODA_FULL_NAME_OF_DAY);
+    postgresToJodaMap.put(POSTGRES_DAY_OF_YEAR, JODA_DAY_OF_YEAR);
+    postgresToJodaMap.put(POSTGRES_DAY_OF_MONTH, JODA_DAY_OF_MONTH);
+    postgresToJodaMap.put(POSTGRES_DAY_OF_WEEK, JODA_DAY_OF_WEEK);
+    postgresToJodaMap.put(POSTGRES_NAME_OF_MONTH, JODA_NAME_OF_MONTH);
+    postgresToJodaMap.put(POSTGRES_ABR_NAME_OF_MONTH, JODA_ABR_NAME_OF_MONTH);
+    postgresToJodaMap.put(POSTGRES_FULL_ERA_NAME, JODA_FULL_ERA_NAME);
+    postgresToJodaMap.put(POSTGRES_NAME_OF_DAY, JODA_NAME_OF_DAY);
+    postgresToJodaMap.put(POSTGRES_HOUR_12_NAME, JODA_HOUR_12_NAME);
+    postgresToJodaMap.put(POSTGRES_HOUR_12_OTHER_NAME, JODA_HOUR_12_NAME);
+    postgresToJodaMap.put(POSTGRES_HOUR_24_NAME, JODA_HOUR_24_NAME);
+    postgresToJodaMap.put(POSTGRES_MINUTE_OF_HOUR_NAME, JODA_MINUTE_OF_HOUR_NAME);
+    postgresToJodaMap.put(POSTGRES_SECOND_OF_MINUTE_NAME, JODA_SECOND_OF_MINUTE_NAME);
+    postgresToJodaMap.put(POSTGRES_MILLISECOND_OF_MINUTE_NAME, JODA_MILLISECOND_OF_MINUTE_NAME);
+    postgresToJodaMap.put(POSTGRES_WEEK_OF_YEAR, JODA_WEEK_OF_YEAR);
+    postgresToJodaMap.put(POSTGRES_MONTH, JODA_MONTH);
+    postgresToJodaMap.put(POSTGRES_HALFDAY_AM, JODA_HALFDAY);
+    postgresToJodaMap.put(POSTGRES_HALFDAY_PM, JODA_HALFDAY);
+    postgresToJodaMap.put(POSTGRES_ISO_WEEK_OF_YEAR, JODA_WEEK_OF_YEAR);
+    postgresToJodaMap.put(POSTGRES_YEAR, JODA_YEAR);
+    postgresToJodaMap.put(POSTGRES_ISO_1YEAR, JODA_ISO_1YEAR);
+    postgresToJodaMap.put(POSTGRES_ISO_2YEAR, JODA_ISO_2YEAR);
+    postgresToJodaMap.put(POSTGRES_ISO_3YEAR, JODA_ISO_3YEAR);
+    postgresToJodaMap.put(POSTGRES_ISO_4YEAR, JODA_ISO_4YEAR);
+    postgresToJodaMap.put(PREFIX_FM, EMPTY_STRING);
+    postgresToJodaMap.put(PREFIX_FX, EMPTY_STRING);
+    postgresToJodaMap.put(PREFIX_TM, EMPTY_STRING);
+  }
+
+  /**
+   * Replaces all postgres patterns from {@param pattern},
+   * available in postgresToJodaMap keys to jodaTime equivalents.
+   *
+   * @param pattern date pattern in postgres format
+   * @return date pattern with replaced patterns in joda format
+   */
+  public static String toJodaFormat(String pattern) {
+    // replaces escape character for text delimiter
+    StringBuilder builder = new StringBuilder(pattern.replaceAll(POSTGRES_ESCAPE_CHARACTER,
JODA_ESCAPE_CHARACTER));
+
+    int start = 0;    // every time search of postgres token in pattern will start from this
index.
+    int minPos;       // min position of the longest postgres token
+    do {
+      // finds first value with max length
+      minPos = builder.length();
+      PostgresDateTimeConstant firstMatch = null;
+      for (PostgresDateTimeConstant postgresPattern : postgresToJodaMap.keySet()) {
+        // keys sorted in length decreasing
+        // at first search longer tokens to consider situation where some tokens are the
parts of large tokens
+        // example: if pattern contains a token "DDD", token "DD" would be skipped, as a
part of "DDD".
+        int pos;
+        // some tokens can't be in upper camel casing, so we ignore them here.
+        // example: DD, DDD, MM, etc.
+        if (postgresPattern.hasCamelCasing()) {
+          // finds postgres tokens in upper camel casing
+          // example: Month, Mon, Day, Dy, etc.
+          pos = builder.indexOf(StringUtils.capitalize(postgresPattern.getName()), start);
+          if (pos >= 0 && pos < minPos) {
+            firstMatch = postgresPattern;
+            minPos = pos;
+            if (minPos == start) {
+              break;
+            }
+          }
+        }
+        // finds postgres tokens in lower casing
+        pos = builder.indexOf(postgresPattern.getName().toLowerCase(), start);
+        if (pos >= 0 && pos < minPos) {
+          firstMatch = postgresPattern;
+          minPos = pos;
+          if (minPos == start) {
+            break;
+          }
+        }
+        // finds postgres tokens in upper casing
+        pos = builder.indexOf(postgresPattern.getName().toUpperCase(), start);
+        if (pos >= 0 && pos < minPos) {
+          firstMatch = postgresPattern;
+          minPos = pos;
+          if (minPos == start) {
+            break;
+          }
+        }
+      }
+      // replaces postgres token, if found and it does not escape character
+      if (minPos < builder.length() && firstMatch != null) {
+        String jodaToken = postgresToJodaMap.get(firstMatch);
+        // checks that token is not a part of escape sequence
+        if (StringUtils.countMatches(builder.subSequence(0, minPos), JODA_ESCAPE_CHARACTER)
% 2 == 0) {
+          int offset = minPos + firstMatch.getName().length();
+          builder.replace(minPos, offset, jodaToken);
+          start = minPos + jodaToken.length();
+        } else {
+          int endEscapeCharacter = builder.indexOf(JODA_ESCAPE_CHARACTER, minPos);
+          if (endEscapeCharacter >= 0) {
+            start = endEscapeCharacter;
+          } else {
+            break;
+          }
+        }
+      }
+    } while (minPos < builder.length());
+    return builder.toString();
+  }
+
+  /**
+   * Length decreasing comparator.
+   * Compares PostgresDateTimeConstant names by length, if they have the same length, compares
them lexicographically.
+   */
+  private static class LengthDescComparator implements Comparator<PostgresDateTimeConstant>
{
+
+    public int compare(PostgresDateTimeConstant o1, PostgresDateTimeConstant o2) {
+      int result = o2.getName().length() - o1.getName().length();
+      if (result == 0) {
+        return o1.getName().compareTo(o2.getName());
+      }
+      return result;
+    }
+  }
+
+}

http://git-wip-us.apache.org/repos/asf/drill/blob/c9a6ac4f/logical/src/test/java/org/apache/drill/common/expression/fn/JodaDateValidatorTest.java
----------------------------------------------------------------------
diff --git a/logical/src/test/java/org/apache/drill/common/expression/fn/JodaDateValidatorTest.java
b/logical/src/test/java/org/apache/drill/common/expression/fn/JodaDateValidatorTest.java
new file mode 100644
index 0000000..8398bcf
--- /dev/null
+++ b/logical/src/test/java/org/apache/drill/common/expression/fn/JodaDateValidatorTest.java
@@ -0,0 +1,203 @@
+/*
+* 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.common.expression.fn;
+
+import com.google.common.collect.Maps;
+import org.joda.time.DateTime;
+import org.joda.time.DateTimeZone;
+import org.joda.time.format.DateTimeFormatter;
+import org.junit.Assert;
+import org.junit.BeforeClass;
+import org.junit.Test;
+
+import java.util.Map;
+
+import static org.apache.drill.common.expression.fn.JodaDateValidator.toJodaFormat;
+import static org.joda.time.DateTime.parse;
+import static org.joda.time.format.DateTimeFormat.forPattern;
+
+public class JodaDateValidatorTest {
+
+  private static final Map<String, String> TEST_CASES = Maps.newHashMap();
+
+  @BeforeClass
+  public static void fillTestCases() {
+    TEST_CASES.put("ddd-mm-yyyy", "D-MM-yyyy");
+    TEST_CASES.put("DDD-MM-YYYY", "D-MM-yyyy");
+    TEST_CASES.put("ddd/yyyy", "D/yyyy");
+    TEST_CASES.put("DDD/YYYY", "D/yyyy");
+    TEST_CASES.put("yyyy-Mon-dd", "yyyy-MMM-d");
+    TEST_CASES.put("YYYY-mon-DD", "yyyy-MMM-d");
+    TEST_CASES.put("yyyy-mon-dd", "yyyy-MMM-d");
+    TEST_CASES.put("YYYY-MON-DD", "yyyy-MMM-d");
+    TEST_CASES.put("YYYY-MON-DD-D", "yyyy-MMM-d-e");
+    TEST_CASES.put("YYYY-MONTH-DD", "yyyy-MMMM-d");
+    TEST_CASES.put("dayyyy", "EEEEyyy");
+    TEST_CASES.put("dayy", "EEEEy");
+    TEST_CASES.put("dyy", "Ey");
+    TEST_CASES.put("ddd\"D\"mm\"D\"yyyy", "D'D'MM'D'yyyy");
+    TEST_CASES.put("ddd\"ddd-mm-yyyy\"mm-yyyy", "D'ddd-mm-yyyy'MM-yyyy");
+    TEST_CASES.put("ddd\"ddd-mm-yyyy\"mm\"ddd-mm-yyyy\"yyyy", "D'ddd-mm-yyyy'MM'ddd-mm-yyyy'yyyy");
+    TEST_CASES.put("DD-mm-yyyy", "d-MM-yyyy");
+    TEST_CASES.put("DddDDD", "edD");
+    TEST_CASES.put("dddddd", "DD");
+    TEST_CASES.put("mmmmyyyyddd", "MMMMyyyyD");
+    TEST_CASES.put("wweeiyyy", "wGxxxx");
+    TEST_CASES.put("iweeiyy", "wGxxx");
+    TEST_CASES.put("wweei", "wGx");
+    TEST_CASES.put("hhmissmsam", "hmssSSSaa");
+    TEST_CASES.put("HHMISSMSAM", "hmssSSSaa");
+    TEST_CASES.put("HHmiSSmsPM", "hmssSSSaa");
+    TEST_CASES.put("hh12missmsam", "hmssSSSaa");
+    TEST_CASES.put("hh24missmsam", "HmssSSSaa");
+    TEST_CASES.put("hh24mifmssfxmsam", "HmssSSSaa");
+  }
+
+  @Test
+  public void testDateCases() {
+    for (Map.Entry<String, String> testEntry : TEST_CASES.entrySet()) {
+      Assert.assertEquals(testEntry.getValue(), toJodaFormat(testEntry.getKey()));
+    }
+  }
+
+  @Test
+  public void testDateMonthDayYearFormat() {
+    int day = 1;
+    int month = 8;
+    int year = 2011;
+    DateTime date = parseDateFromPostgres(month + "/" + day + "/" + year, "MM/DD/YYYY");
+    Assert.assertTrue(date.getDayOfMonth() == day &&
+                        date.getMonthOfYear() == month &&
+                        date.getYear() == year);
+  }
+
+  @Test
+  public void testDateYearMonthDayFormat() {
+    String day = "05";
+    String month = "Dec";
+    int year = 2000;
+    DateTime date = parseDateFromPostgres(day + " " + month + " " + year, "DD Mon YYYY");
+    Assert.assertTrue(date.getDayOfMonth() == Integer.parseInt(day) &&
+                        date.getMonthOfYear() == 12 &&
+                        date.getYear() == year);
+  }
+
+  @Test
+  public void testDateDayMonthYearFormat() {
+    String day = "01";
+    String month = "08";
+    int year = 2011;
+    DateTime date = parseDateFromPostgres(year + "-" + month + "-" + day, "YYYY-MM-DD");
+    Assert.assertTrue(date.getDayOfMonth() == Integer.parseInt(day) &&
+                        date.getMonthOfYear() == Integer.parseInt(month) &&
+                        date.getYear() == year);
+  }
+
+  @Test
+  public void testDateDayOfYearYearFormat() {
+    String day = "01";
+    int year = 2011;
+    DateTime date = parseDateFromPostgres(day + "/" + year, "ddd/YYYY");
+    Assert.assertTrue(date.getDayOfMonth() == 1 &&
+                        date.getMonthOfYear() == 1 &&
+                        date.getYear() == year);
+  }
+
+  @Test
+  public void testTimeHoursMinutesSecondsFormat() {
+    int hours = 11;
+    int minutes = 50;
+    String seconds = "05";
+    DateTime date = parseDateFromPostgres(hours + ":" + minutes + ":" + seconds + " am",
"hh12:mi:ss am");
+    Assert.assertTrue(date.getHourOfDay() == hours &&
+                        date.getMinuteOfHour() == minutes &&
+                        date.getSecondOfMinute() == Integer.parseInt(seconds));
+  }
+
+  @Test
+  public void testTimeHours24MinutesSecondsFormat() {
+    int hours = 15;
+    int minutes = 50;
+    int seconds = 5;
+    DateTime date = parseDateFromPostgres(hours + ":" + minutes + ":" + seconds, "hh24:mi:ss");
+    Assert.assertTrue(date.getHourOfDay() == hours &&
+                        date.getMinuteOfHour() == minutes &&
+                        date.getSecondOfMinute() == seconds);
+  }
+
+  @Test
+  public void testDateYearMonthNameFormat() {
+    String month = "JUN";
+    int year = 2000;
+    DateTime date = parseDateFromPostgres(year + " " + month, "YYYY MON");
+    Assert.assertTrue(date.getMonthOfYear() == 6 && date.getYear() == year);
+  }
+
+  @Test
+  public void testYearMonthDayFormat() {
+    String day = "01";
+    String month = "08";
+    int year = 2011;
+    DateTime date = parseDateFromPostgres(year + "" + month + day, "YYYYMMDD");
+    Assert.assertTrue(date.getDayOfMonth() == Integer.parseInt(day) &&
+                        date.getMonthOfYear() == Integer.parseInt(month) &&
+                        date.getYear() == year);
+  }
+
+  @Test
+  public void testYearAndMonthDayFormat() {
+    String day = "01";
+    String month = "08";
+    int year = 2011;
+    DateTime date = parseDateFromPostgres(year + "-" + month + day, "YYYY-MMDD");
+    Assert.assertTrue(date.getDayOfMonth() == Integer.parseInt(day) &&
+                        date.getMonthOfYear() == Integer.parseInt(month) &&
+                        date.getYear() == year);
+  }
+
+  @Test
+  public void testYearMonthNameDayFormat() {
+    String day = "30";
+    String month = "Nov";
+    int year = 2000;
+    DateTime date = parseDateFromPostgres(year + "" + month + day, "YYYYMonDD");
+    Assert.assertTrue(date.getDayOfMonth() == Integer.parseInt(day) &&
+                        date.getMonthOfYear() == 11 &&
+                        date.getYear() == year);
+  }
+
+  @Test
+  public void testDateTimeHoursMinutesSecondsFormat() {
+    String day = "24";
+    String month = "June";
+    int year = 2010;
+    int hours = 10;
+    int minutes = 12;
+    DateTime date = parseDateFromPostgres(year + "" + day + month + hours + ":" + minutes
+ "am", "YYYYDDFMMonthHH12:MIam");
+    Assert.assertTrue(date.getDayOfMonth() == Integer.parseInt(day) &&
+                        date.getMonthOfYear() == 6 &&
+                        date.getYear() == year &&
+                        date.getHourOfDay() == hours &&
+                        date.getMinuteOfHour() == minutes);
+  }
+
+  private DateTime parseDateFromPostgres(String date, String pattern) {
+    String jodaFormat = toJodaFormat(pattern);
+    DateTimeFormatter format = forPattern(jodaFormat);
+    return parse(date, format).withZoneRetainFields(DateTimeZone.UTC);
+  }
+}


Mime
View raw message