You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@drill.apache.org by Serhii-Harnyk <gi...@git.apache.org> on 2016/09/06 12:44:02 UTC
[GitHub] drill pull request #581: DRILL-4864: Add ANSI format for date/time functions
GitHub user Serhii-Harnyk opened a pull request:
https://github.com/apache/drill/pull/581
DRILL-4864: Add ANSI format for date/time functions
You can merge this pull request into a Git repository by running:
$ git pull https://github.com/Serhii-Harnyk/drill DRILL-4864
Alternatively you can review and apply these changes as the patch at:
https://github.com/apache/drill/pull/581.patch
To close this pull request, make a commit to your master/trunk branch
with (at least) the following in the commit message:
This closes #581
----
commit e580e38290de4e3a4f7510811e3c5a956c5d7594
Author: Serhii-Harnyk <se...@gmail.com>
Date: 2016-09-01T14:48:02Z
DRILL-4864: Add ANSI format for date/time functions
----
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] drill pull request #581: DRILL-4864: Add ANSI format for date/time functions
Posted by Serhii-Harnyk <gi...@git.apache.org>.
Github user Serhii-Harnyk commented on a diff in the pull request:
https://github.com/apache/drill/pull/581#discussion_r99312234
--- Diff: exec/java-exec/src/test/java/org/apache/drill/exec/fn/impl/testing/TestDateConversions.java ---
@@ -0,0 +1,200 @@
+/*
+* 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.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;
+
+public class TestDateConversions extends BaseTestQuery {
--- End diff --
Done
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] drill pull request #581: DRILL-4864: Add ANSI format for date/time functions
Posted by gparai <gi...@git.apache.org>.
Github user gparai commented on a diff in the pull request:
https://github.com/apache/drill/pull/581#discussion_r81440134
--- Diff: exec/java-exec/src/main/java/org/apache/drill/exec/expr/fn/impl/AnsiToJoda.java ---
@@ -0,0 +1,58 @@
+/*
+* 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.expr.fn.impl;
+
+import io.netty.buffer.DrillBuf;
+import org.apache.drill.exec.expr.DrillSimpleFunc;
+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.VarCharHolder;
+
+import javax.inject.Inject;
+
+/**
+ * Replaces all ansi patterns to joda equivalents.
+ */
+@FunctionTemplate(name = "ansi_to_joda",
+ scope = FunctionTemplate.FunctionScope.SIMPLE,
+ nulls= FunctionTemplate.NullHandling.NULL_IF_NULL)
+public class AnsiToJoda implements DrillSimpleFunc {
+
+ @Param
+ VarCharHolder in;
+
+ @Output
+ VarCharHolder out;
+
+ @Inject
+ DrillBuf buffer;
+
+ @Override
+ public void setup() {
+ }
+
+ @Override
+ public void eval() {
+ String pattern = org.apache.drill.exec.expr.fn.impl.StringFunctionHelpers.toStringFromUTF8(in.start, in.end, in.buffer);
--- End diff --
Would it be good to validate the ANSI pattern prior to converting it to JODA?
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] drill pull request #581: DRILL-4864: Add ANSI format for date/time functions
Posted by paul-rogers <gi...@git.apache.org>.
Github user paul-rogers commented on a diff in the pull request:
https://github.com/apache/drill/pull/581#discussion_r95887636
--- Diff: logical/src/main/java/org/apache/drill/common/expression/fn/JodaDateValidator.java ---
@@ -0,0 +1,216 @@
+/*
+* 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.Sets;
+import org.apache.commons.lang3.StringUtils;
+import org.apache.drill.common.map.CaseInsensitiveMap;
+
+import java.util.Comparator;
+import java.util.Set;
+
+public class JodaDateValidator {
+
+ private static final Set<String> postgresValuesForDeleting = Sets.newTreeSet(new LengthDescComparator());
+ private static final CaseInsensitiveMap<String> postgresToJodaMap = CaseInsensitiveMap.newTreeMap(new LengthDescComparator());
+
+ // tokens for deleting
+ public static final String SUFFIX_SP = "sp";
+ public static final String PREFIX_FM = "fm";
+ public static final String PREFIX_FX = "fx";
+ public static final String PREFIX_TM = "tm";
+
+ // postgres patterns
+ public static final String POSTGRES_FULL_NAME_OF_DAY = "day";
+ public static final String POSTGRES_DAY_OF_YEAR = "ddd";
+ public static final String POSTGRES_DAY_OF_MONTH = "dd";
+ public static final String POSTGRES_DAY_OF_WEEK = "d";
+ public static final String POSTGRES_NAME_OF_MONTH = "month";
+ public static final String POSTGRES_ABR_NAME_OF_MONTH = "mon";
+ public static final String POSTGRES_FULL_ERA_NAME = "ee";
+ public static final String POSTGRES_NAME_OF_DAY = "dy";
+ public static final String POSTGRES_TIME_ZONE_NAME = "tz";
+ public static final String POSTGRES_HOUR_12_NAME = "hh";
+ public static final String POSTGRES_HOUR_12_OTHER_NAME = "hh12";
+ public static final String POSTGRES_HOUR_24_NAME = "hh24";
+ public static final String POSTGRES_MINUTE_OF_HOUR_NAME = "mi";
+ public static final String POSTGRES_SECOND_OF_MINUTE_NAME = "ss";
+ public static final String POSTGRES_MILLISECOND_OF_MINUTE_NAME = "ms";
+ public static final String POSTGRES_WEEK_OF_YEAR = "ww";
+ public static final String POSTGRES_MONTH = "mm";
+ public static final String POSTGRES_HALFDAY_AM = "am";
+ public static final String POSTGRES_HALFDAY_PM = "pm";
+
+ // 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_FULL_ERA_NAME = "G";
+ public static final String JODA_NAME_OF_DAY = "E";
+ public static final String JODA_TIME_ZONE_NAME = "TZ";
+ public static final String JODA_HOUR_12_NAME = "h";
+ public static final String JODA_HOUR_12_OTHER_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 = "s";
+ 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";
+
+ 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_TIME_ZONE_NAME, JODA_TIME_ZONE_NAME);
+ postgresToJodaMap.put(POSTGRES_HOUR_12_NAME, JODA_HOUR_12_NAME);
+ postgresToJodaMap.put(POSTGRES_HOUR_12_OTHER_NAME, JODA_HOUR_12_OTHER_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);
+ }
+
+ static {
+ postgresValuesForDeleting.add(SUFFIX_SP);
+ postgresValuesForDeleting.add(PREFIX_FM);
+ postgresValuesForDeleting.add(PREFIX_FX);
+ postgresValuesForDeleting.add(PREFIX_TM);
+ }
+
+ /**
+ * 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) {
--- End diff --
Do we support escape characters? This is also necessary if the format allows escape characters. While Postres is a big vague on escapes, this is how it works for Oracle: http://stackoverflow.com/questions/588430/what-is-the-best-way-to-escape-non-format-characters-in-oracles-to-char
Escapes throw off simple pattern matching. This is a date format: "SS". This is not: ""SS"".
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] drill pull request #581: DRILL-4864: Add ANSI format for date/time functions
Posted by paul-rogers <gi...@git.apache.org>.
Github user paul-rogers commented on a diff in the pull request:
https://github.com/apache/drill/pull/581#discussion_r95871016
--- Diff: exec/java-exec/src/main/codegen/templates/DateIntervalFunctionTemplates/SqlToDateTypeFunctions.java ---
@@ -0,0 +1,77 @@
+/**
+ * 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
+ byte[] buf = new byte[right.end - right.start];
+ right.buffer.getBytes(right.start, buf, 0, right.end - right.start);
+ String formatString = new String(buf, com.google.common.base.Charsets.UTF_8);
+ String pattern = org.apache.drill.common.expression.fn.JodaDateValidator.toJodaFormat(formatString);
+ format = org.joda.time.format.DateTimeFormat.forPattern(pattern);
+ }
+
+ public void eval() {
+ // Get the input
+ byte[] buf1 = new byte[left.end - left.start];
+ left.buffer.getBytes(left.start, buf1, 0, left.end - left.start);
+ String input = new String(buf1, com.google.common.base.Charsets.UTF_8);
+
+ <#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>
--- End diff --
Let's think about error handling. The input is a VarChar which can be anything: a blank string, the name of a movie star, a phone number. The user claims it is a date. Even if it is, it might be badly formed or in the wrong format.
What error handling do we do? `doEval` declares no exceptions. According to the Joda Javadoc, `parse()` will throw an (unchecked) `IllegalArgumentException` if the input is bad. This will bubble up the execution stack and cause the fragment executor to fail. There will be no context ("row xyz of input abc"), and precious little information about the SQL statement and field that caused the problem.
Should we catch the exception and fill in as much context as we have available?
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] drill pull request #581: DRILL-4864: Add ANSI format for date/time functions
Posted by Serhii-Harnyk <gi...@git.apache.org>.
Github user Serhii-Harnyk commented on a diff in the pull request:
https://github.com/apache/drill/pull/581#discussion_r99313550
--- Diff: logical/src/main/java/org/apache/drill/common/expression/fn/JodaDateValidator.java ---
@@ -0,0 +1,216 @@
+/*
+* 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.Sets;
+import org.apache.commons.lang3.StringUtils;
+import org.apache.drill.common.map.CaseInsensitiveMap;
+
+import java.util.Comparator;
+import java.util.Set;
+
+public class JodaDateValidator {
+
+ private static final Set<String> postgresValuesForDeleting = Sets.newTreeSet(new LengthDescComparator());
+ private static final CaseInsensitiveMap<String> postgresToJodaMap = CaseInsensitiveMap.newTreeMap(new LengthDescComparator());
+
+ // tokens for deleting
+ public static final String SUFFIX_SP = "sp";
+ public static final String PREFIX_FM = "fm";
+ public static final String PREFIX_FX = "fx";
+ public static final String PREFIX_TM = "tm";
+
+ // postgres patterns
+ public static final String POSTGRES_FULL_NAME_OF_DAY = "day";
+ public static final String POSTGRES_DAY_OF_YEAR = "ddd";
+ public static final String POSTGRES_DAY_OF_MONTH = "dd";
+ public static final String POSTGRES_DAY_OF_WEEK = "d";
+ public static final String POSTGRES_NAME_OF_MONTH = "month";
+ public static final String POSTGRES_ABR_NAME_OF_MONTH = "mon";
+ public static final String POSTGRES_FULL_ERA_NAME = "ee";
+ public static final String POSTGRES_NAME_OF_DAY = "dy";
+ public static final String POSTGRES_TIME_ZONE_NAME = "tz";
+ public static final String POSTGRES_HOUR_12_NAME = "hh";
+ public static final String POSTGRES_HOUR_12_OTHER_NAME = "hh12";
+ public static final String POSTGRES_HOUR_24_NAME = "hh24";
+ public static final String POSTGRES_MINUTE_OF_HOUR_NAME = "mi";
+ public static final String POSTGRES_SECOND_OF_MINUTE_NAME = "ss";
+ public static final String POSTGRES_MILLISECOND_OF_MINUTE_NAME = "ms";
+ public static final String POSTGRES_WEEK_OF_YEAR = "ww";
+ public static final String POSTGRES_MONTH = "mm";
+ public static final String POSTGRES_HALFDAY_AM = "am";
+ public static final String POSTGRES_HALFDAY_PM = "pm";
+
+ // 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_FULL_ERA_NAME = "G";
+ public static final String JODA_NAME_OF_DAY = "E";
+ public static final String JODA_TIME_ZONE_NAME = "TZ";
+ public static final String JODA_HOUR_12_NAME = "h";
+ public static final String JODA_HOUR_12_OTHER_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 = "s";
+ 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";
+
+ 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_TIME_ZONE_NAME, JODA_TIME_ZONE_NAME);
+ postgresToJodaMap.put(POSTGRES_HOUR_12_NAME, JODA_HOUR_12_NAME);
+ postgresToJodaMap.put(POSTGRES_HOUR_12_OTHER_NAME, JODA_HOUR_12_OTHER_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);
+ }
+
+ static {
+ postgresValuesForDeleting.add(SUFFIX_SP);
+ postgresValuesForDeleting.add(PREFIX_FM);
+ postgresValuesForDeleting.add(PREFIX_FX);
+ postgresValuesForDeleting.add(PREFIX_TM);
+ }
+
+ /**
+ * 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) {
--- End diff --
Yes, you are right. We limited by the set of the date patterns that supports JodaTime.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] drill issue #581: DRILL-4864: Add ANSI format for date/time functions
Posted by Serhii-Harnyk <gi...@git.apache.org>.
Github user Serhii-Harnyk commented on the issue:
https://github.com/apache/drill/pull/581
@paul-rogers could you please review new changes?
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] drill pull request #581: DRILL-4864: Add ANSI format for date/time functions
Posted by paul-rogers <gi...@git.apache.org>.
Github user paul-rogers commented on a diff in the pull request:
https://github.com/apache/drill/pull/581#discussion_r95883938
--- Diff: logical/src/test/java/org/apache/drill/common/expression/fn/JodaDateValidatorTest.java ---
@@ -0,0 +1,176 @@
+/*
+* 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 org.joda.time.DateTime;
+import org.joda.time.DateTimeZone;
+import org.joda.time.format.DateTimeFormatter;
+import org.junit.Assert;
+import org.junit.Test;
+
+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 {
--- End diff --
Was rather expecting many more tests. Might be easier to structure them as an array of (input, expected) values so that the cost of adding more tests is low. See discussion above about the kinds of tests expected: each and every format and variation, including ambiguous situations
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] drill pull request #581: DRILL-4864: Add ANSI format for date/time functions
Posted by asfgit <gi...@git.apache.org>.
Github user asfgit closed the pull request at:
https://github.com/apache/drill/pull/581
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] drill issue #581: DRILL-4864: Add ANSI format for date/time functions
Posted by amansinha100 <gi...@git.apache.org>.
Github user amansinha100 commented on the issue:
https://github.com/apache/drill/pull/581
+1
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] drill pull request #581: DRILL-4864: Add ANSI format for date/time functions
Posted by gparai <gi...@git.apache.org>.
Github user gparai commented on a diff in the pull request:
https://github.com/apache/drill/pull/581#discussion_r81438289
--- Diff: exec/java-exec/src/main/java/org/apache/drill/exec/planner/logical/DrillOptiq.java ---
@@ -408,6 +411,12 @@ private LogicalExpression getDrillFunctionFromOptiqCall(RexCall call) {
return first;
}
+ } else if (functionName.equals("to_date") || functionName.equals("to_time") || functionName.equals("to_timestamp")) {
+ // convert ansi date format string to joda according to session option
+ OptionManager om = this.context.getPlannerSettings().getOptions();
+ if(ToDateFormats.valueOf(om.getOption(ExecConstants.TO_DATE_FORMAT).string_val.toUpperCase()).equals(ToDateFormats.ANSI)) {
--- End diff --
if (
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] drill pull request #581: DRILL-4864: Add ANSI format for date/time functions
Posted by paul-rogers <gi...@git.apache.org>.
Github user paul-rogers commented on a diff in the pull request:
https://github.com/apache/drill/pull/581#discussion_r95887850
--- Diff: logical/src/main/java/org/apache/drill/common/expression/fn/JodaDateValidator.java ---
@@ -0,0 +1,216 @@
+/*
+* 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.Sets;
+import org.apache.commons.lang3.StringUtils;
+import org.apache.drill.common.map.CaseInsensitiveMap;
+
+import java.util.Comparator;
+import java.util.Set;
+
+public class JodaDateValidator {
+
+ private static final Set<String> postgresValuesForDeleting = Sets.newTreeSet(new LengthDescComparator());
+ private static final CaseInsensitiveMap<String> postgresToJodaMap = CaseInsensitiveMap.newTreeMap(new LengthDescComparator());
+
+ // tokens for deleting
+ public static final String SUFFIX_SP = "sp";
+ public static final String PREFIX_FM = "fm";
+ public static final String PREFIX_FX = "fx";
+ public static final String PREFIX_TM = "tm";
+
+ // postgres patterns
+ public static final String POSTGRES_FULL_NAME_OF_DAY = "day";
+ public static final String POSTGRES_DAY_OF_YEAR = "ddd";
+ public static final String POSTGRES_DAY_OF_MONTH = "dd";
+ public static final String POSTGRES_DAY_OF_WEEK = "d";
+ public static final String POSTGRES_NAME_OF_MONTH = "month";
+ public static final String POSTGRES_ABR_NAME_OF_MONTH = "mon";
+ public static final String POSTGRES_FULL_ERA_NAME = "ee";
+ public static final String POSTGRES_NAME_OF_DAY = "dy";
+ public static final String POSTGRES_TIME_ZONE_NAME = "tz";
+ public static final String POSTGRES_HOUR_12_NAME = "hh";
+ public static final String POSTGRES_HOUR_12_OTHER_NAME = "hh12";
+ public static final String POSTGRES_HOUR_24_NAME = "hh24";
+ public static final String POSTGRES_MINUTE_OF_HOUR_NAME = "mi";
+ public static final String POSTGRES_SECOND_OF_MINUTE_NAME = "ss";
+ public static final String POSTGRES_MILLISECOND_OF_MINUTE_NAME = "ms";
+ public static final String POSTGRES_WEEK_OF_YEAR = "ww";
+ public static final String POSTGRES_MONTH = "mm";
+ public static final String POSTGRES_HALFDAY_AM = "am";
+ public static final String POSTGRES_HALFDAY_PM = "pm";
+
+ // 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_FULL_ERA_NAME = "G";
+ public static final String JODA_NAME_OF_DAY = "E";
+ public static final String JODA_TIME_ZONE_NAME = "TZ";
+ public static final String JODA_HOUR_12_NAME = "h";
+ public static final String JODA_HOUR_12_OTHER_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 = "s";
+ 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";
+
+ 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_TIME_ZONE_NAME, JODA_TIME_ZONE_NAME);
+ postgresToJodaMap.put(POSTGRES_HOUR_12_NAME, JODA_HOUR_12_NAME);
+ postgresToJodaMap.put(POSTGRES_HOUR_12_OTHER_NAME, JODA_HOUR_12_OTHER_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);
+ }
+
+ static {
+ postgresValuesForDeleting.add(SUFFIX_SP);
+ postgresValuesForDeleting.add(PREFIX_FM);
+ postgresValuesForDeleting.add(PREFIX_FX);
+ postgresValuesForDeleting.add(PREFIX_TM);
+ }
+
+ /**
+ * 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) {
--- End diff --
Since this is, essentially, a parser, it must handle invalid input. But the function does not throw an exception, so it is not clear if we detect and handle invalid inputs. (Or, do we simply output an invalid format and let the Joda functions throw an error?)
The tests below are good, but they only scratch the surface of the many "ANSI" formats and combinations. Because we test only a few well-defined formats, we have no information about how this translation function will behave for the crazy formats Drill will encounter in the wild.
How can we be certain that this function correct translates all possible inputs, and rejects all invalid inputs?
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] drill pull request #581: DRILL-4864: Add ANSI format for date/time functions
Posted by paul-rogers <gi...@git.apache.org>.
Github user paul-rogers commented on a diff in the pull request:
https://github.com/apache/drill/pull/581#discussion_r95864621
--- Diff: exec/java-exec/src/main/codegen/templates/DateIntervalFunctionTemplates/SqlToDateTypeFunctions.java ---
@@ -0,0 +1,77 @@
+/**
+ * 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
+ byte[] buf = new byte[right.end - right.start];
+ right.buffer.getBytes(right.start, buf, 0, right.end - right.start);
+ String formatString = new String(buf, com.google.common.base.Charsets.UTF_8);
+ String pattern = org.apache.drill.common.expression.fn.JodaDateValidator.toJodaFormat(formatString);
--- End diff --
If not already done, I would suggest testing the code in Java 8. Replace the Joda classes with the equivalent Java 8 classes. Make sure that there is no user-visible change in behavior or semantics. This is important because Java 7 is long obsolete and we really must, sometime soon, upgrade to Java 8. At that point, we won't want to continue to use the obsolete Joda classes.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] drill pull request #581: DRILL-4864: Add ANSI format for date/time functions
Posted by Serhii-Harnyk <gi...@git.apache.org>.
Github user Serhii-Harnyk commented on a diff in the pull request:
https://github.com/apache/drill/pull/581#discussion_r83871870
--- Diff: logical/src/main/java/org/apache/drill/common/expression/fn/JodaDateValidator.java ---
@@ -0,0 +1,213 @@
+/*
+* 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.Sets;
+import org.apache.commons.lang3.StringUtils;
+import org.apache.drill.common.map.CaseInsensitiveMap;
+
+import java.util.Comparator;
+import java.util.Set;
+
+public class JodaDateValidator {
+
+ private static final Set<String> ansiValuesForDeleting = Sets.newTreeSet(new LengthDescComparator());
+ private static final CaseInsensitiveMap<String> ansiToJodaMap = CaseInsensitiveMap.newTreeMap(new LengthDescComparator());
+
+ //tokens for deleting
+ public static final String SUFFIX_SP = "sp";
+ public static final String PREFIX_FM = "fm";
+ public static final String PREFIX_FX = "fx";
+ public static final String PREFIX_TM = "tm";
+
+ //ansi patterns
+ public static final String ANSI_FULL_NAME_OF_DAY = "day";
+ public static final String ANSI_DAY_OF_YEAR = "ddd";
+ public static final String ANSI_DAY_OF_MONTH = "dd";
+ public static final String ANSI_DAY_OF_WEEK = "d";
+ public static final String ANSI_NAME_OF_MONTH = "month";
+ public static final String ANSI_ABR_NAME_OF_MONTH = "mon";
+ public static final String ANSI_FULL_ERA_NAME = "ee";
+ public static final String ANSI_NAME_OF_DAY = "dy";
+ public static final String ANSI_TIME_ZONE_NAME = "tz";
+ public static final String ANSI_HOUR_12_NAME = "hh";
+ public static final String ANSI_HOUR_12_OTHER_NAME = "hh12";
+ public static final String ANSI_HOUR_24_NAME = "hh24";
+ public static final String ANSI_MINUTE_OF_HOUR_NAME = "mi";
+ public static final String ANSI_SECOND_OF_MINUTE_NAME = "ss";
+ public static final String ANSI_MILLISECOND_OF_MINUTE_NAME = "ms";
+ public static final String ANSI_WEEK_OF_YEAR = "ww";
+ public static final String ANSI_MONTH = "mm";
+ public static final String ANSI_HALFDAY_AM = "am";
+ public static final String ANSI_HALFDAY_PM = "pm";
+
+ //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_FULL_ERA_NAME = "G";
+ public static final String JODA_NAME_OF_DAY = "E";
+ public static final String JODA_TIME_ZONE_NAME = "TZ";
+ public static final String JODA_HOUR_12_NAME = "h";
+ public static final String JODA_HOUR_12_OTHER_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 = "s";
+ public static final String JODA_MILLISECOND_OF_MINUTE_NAME = "S";
+ public static final String JODA_WEEK_OF_YEAR = "w";
+ public static final String JODA_MONTH = "MM";
+ public static final String JODA_HALFDAY = "aa";
+
+ static {
+ ansiToJodaMap.put(ANSI_FULL_NAME_OF_DAY, JODA_FULL_NAME_OF_DAY);
+ ansiToJodaMap.put(ANSI_DAY_OF_YEAR, JODA_DAY_OF_YEAR);
+ ansiToJodaMap.put(ANSI_DAY_OF_MONTH, JODA_DAY_OF_MONTH);
+ ansiToJodaMap.put(ANSI_DAY_OF_WEEK, JODA_DAY_OF_WEEK);
+ ansiToJodaMap.put(ANSI_NAME_OF_MONTH, JODA_NAME_OF_MONTH);
+ ansiToJodaMap.put(ANSI_ABR_NAME_OF_MONTH, JODA_ABR_NAME_OF_MONTH);
+ ansiToJodaMap.put(ANSI_FULL_ERA_NAME, JODA_FULL_ERA_NAME);
+ ansiToJodaMap.put(ANSI_NAME_OF_DAY, JODA_NAME_OF_DAY);
+ ansiToJodaMap.put(ANSI_TIME_ZONE_NAME, JODA_TIME_ZONE_NAME);
+ ansiToJodaMap.put(ANSI_HOUR_12_NAME, JODA_HOUR_12_NAME);
+ ansiToJodaMap.put(ANSI_HOUR_12_OTHER_NAME, JODA_HOUR_12_OTHER_NAME);
+ ansiToJodaMap.put(ANSI_HOUR_24_NAME, JODA_HOUR_24_NAME);
+ ansiToJodaMap.put(ANSI_MINUTE_OF_HOUR_NAME, JODA_MINUTE_OF_HOUR_NAME);
+ ansiToJodaMap.put(ANSI_SECOND_OF_MINUTE_NAME, JODA_SECOND_OF_MINUTE_NAME);
+ ansiToJodaMap.put(ANSI_MILLISECOND_OF_MINUTE_NAME, JODA_MILLISECOND_OF_MINUTE_NAME);
+ ansiToJodaMap.put(ANSI_WEEK_OF_YEAR, JODA_WEEK_OF_YEAR);
+ ansiToJodaMap.put(ANSI_MONTH, JODA_MONTH);
+ ansiToJodaMap.put(ANSI_HALFDAY_AM, JODA_HALFDAY);
+ ansiToJodaMap.put(ANSI_HALFDAY_PM, JODA_HALFDAY);
+ }
+
+ static {
+ ansiValuesForDeleting.add(SUFFIX_SP);
+ ansiValuesForDeleting.add(PREFIX_FM);
+ ansiValuesForDeleting.add(PREFIX_FX);
+ ansiValuesForDeleting.add(PREFIX_TM);
+ }
+
+ /**
+ * Replaces all ansi patterns from {@param pattern},
+ * available in ansiToJodaMap keys to jodaTime equivalents.
+ *
+ * @param pattern date pattern in ansi format
+ * @return date pattern with replaced patterns in joda format
+ */
+ public static String toJodaFormat(String pattern) {
+ String preparedString = deleteFromAnsi(pattern);
+ StringBuilder builder = new StringBuilder(preparedString);
+
+ int start = 0; //every time search of ansi token in pattern will start from this index.
+ int minPos; //min position of the longest ansi token
+ do {
+ //finds first value with max length
+ minPos = builder.length();
+ String firstMatch = null;
+ for (String ansiPattern : ansiToJodaMap.keySet()) { //keys sorted in length decreasing
--- End diff --
Done
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] drill pull request #581: DRILL-4864: Add ANSI format for date/time functions
Posted by Serhii-Harnyk <gi...@git.apache.org>.
Github user Serhii-Harnyk commented on a diff in the pull request:
https://github.com/apache/drill/pull/581#discussion_r83870798
--- Diff: exec/java-exec/src/main/java/org/apache/drill/exec/expr/fn/impl/AnsiToJoda.java ---
@@ -0,0 +1,58 @@
+/*
+* 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.expr.fn.impl;
+
+import io.netty.buffer.DrillBuf;
+import org.apache.drill.exec.expr.DrillSimpleFunc;
+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.VarCharHolder;
+
+import javax.inject.Inject;
+
+/**
+ * Replaces all ansi patterns to joda equivalents.
+ */
+@FunctionTemplate(name = "ansi_to_joda",
+ scope = FunctionTemplate.FunctionScope.SIMPLE,
+ nulls= FunctionTemplate.NullHandling.NULL_IF_NULL)
--- End diff --
Fixed
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] drill pull request #581: DRILL-4864: Add ANSI format for date/time functions
Posted by gparai <gi...@git.apache.org>.
Github user gparai commented on a diff in the pull request:
https://github.com/apache/drill/pull/581#discussion_r81438806
--- Diff: exec/java-exec/src/main/java/org/apache/drill/exec/planner/logical/DrillOptiq.java ---
@@ -408,6 +411,12 @@ private LogicalExpression getDrillFunctionFromOptiqCall(RexCall call) {
return first;
}
+ } else if (functionName.equals("to_date") || functionName.equals("to_time") || functionName.equals("to_timestamp")) {
+ // convert ansi date format string to joda according to session option
+ OptionManager om = this.context.getPlannerSettings().getOptions();
+ if(ToDateFormats.valueOf(om.getOption(ExecConstants.TO_DATE_FORMAT).string_val.toUpperCase()).equals(ToDateFormats.ANSI)) {
+ args.set(1, FunctionCallFactory.createExpression("ansi_to_joda", Arrays.asList(args.get(1))));
--- End diff --
What would happen if
drill.exec.fn.to_date_format = 'ansi'
query: select to_date(1234545, ansi_to_joda('dd-MM-YYYY')) from emp;
Would we get select to_date(1234545, ansi_to_joda(ansi_to_joda('dd-MM-YYYY'))) from emp;?
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] drill issue #581: DRILL-4864: Add ANSI format for date/time functions
Posted by adeneche <gi...@git.apache.org>.
Github user adeneche commented on the issue:
https://github.com/apache/drill/pull/581
+1 LGTM,
sorry for taking too long
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] drill issue #581: DRILL-4864: Add ANSI format for date/time functions
Posted by adeneche <gi...@git.apache.org>.
Github user adeneche commented on the issue:
https://github.com/apache/drill/pull/581
There seem to be an issue with milliseconds. Consider the following:
```TO_DATE('2013-01-01 12:13:14.001', 'YYYY-MM-DD HH:MI:SS:MS')```
This works fine in Postgresql but fails in Drill with the following error:
```Invalid format: "2013-01-01 12:13:14.001" is malformed at ".001"```
The issue seems to be that MS is being converted to S but it should be SSS instead
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] drill pull request #581: DRILL-4864: Add ANSI format for date/time functions
Posted by paul-rogers <gi...@git.apache.org>.
Github user paul-rogers commented on a diff in the pull request:
https://github.com/apache/drill/pull/581#discussion_r95865841
--- Diff: exec/java-exec/src/main/codegen/templates/DateIntervalFunctionTemplates/SqlToDateTypeFunctions.java ---
@@ -0,0 +1,77 @@
+/**
+ * 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
+ byte[] buf = new byte[right.end - right.start];
+ right.buffer.getBytes(right.start, buf, 0, right.end - right.start);
+ String formatString = new String(buf, com.google.common.base.Charsets.UTF_8);
+ String pattern = org.apache.drill.common.expression.fn.JodaDateValidator.toJodaFormat(formatString);
+ format = org.joda.time.format.DateTimeFormat.forPattern(pattern);
+ }
+
+ public void eval() {
+ // Get the input
+ byte[] buf1 = new byte[left.end - left.start];
+ left.buffer.getBytes(left.start, buf1, 0, left.end - left.start);
+ String input = new String(buf1, com.google.common.base.Charsets.UTF_8);
--- End diff --
Notice that this is a copy of the above code. I'm sure we have many more.
Beyond the scope of this project, but notice we leave a trail of garbage behind us: every `doEval` allocates a new byte array.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] drill pull request #581: DRILL-4864: Add ANSI format for date/time functions
Posted by Serhii-Harnyk <gi...@git.apache.org>.
Github user Serhii-Harnyk commented on a diff in the pull request:
https://github.com/apache/drill/pull/581#discussion_r83871818
--- Diff: logical/src/main/java/org/apache/drill/common/expression/fn/JodaDateValidator.java ---
@@ -0,0 +1,213 @@
+/*
+* 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.Sets;
+import org.apache.commons.lang3.StringUtils;
+import org.apache.drill.common.map.CaseInsensitiveMap;
+
+import java.util.Comparator;
+import java.util.Set;
+
+public class JodaDateValidator {
+
+ private static final Set<String> ansiValuesForDeleting = Sets.newTreeSet(new LengthDescComparator());
+ private static final CaseInsensitiveMap<String> ansiToJodaMap = CaseInsensitiveMap.newTreeMap(new LengthDescComparator());
+
+ //tokens for deleting
+ public static final String SUFFIX_SP = "sp";
+ public static final String PREFIX_FM = "fm";
+ public static final String PREFIX_FX = "fx";
+ public static final String PREFIX_TM = "tm";
+
+ //ansi patterns
+ public static final String ANSI_FULL_NAME_OF_DAY = "day";
+ public static final String ANSI_DAY_OF_YEAR = "ddd";
+ public static final String ANSI_DAY_OF_MONTH = "dd";
+ public static final String ANSI_DAY_OF_WEEK = "d";
+ public static final String ANSI_NAME_OF_MONTH = "month";
+ public static final String ANSI_ABR_NAME_OF_MONTH = "mon";
+ public static final String ANSI_FULL_ERA_NAME = "ee";
+ public static final String ANSI_NAME_OF_DAY = "dy";
+ public static final String ANSI_TIME_ZONE_NAME = "tz";
+ public static final String ANSI_HOUR_12_NAME = "hh";
+ public static final String ANSI_HOUR_12_OTHER_NAME = "hh12";
+ public static final String ANSI_HOUR_24_NAME = "hh24";
+ public static final String ANSI_MINUTE_OF_HOUR_NAME = "mi";
+ public static final String ANSI_SECOND_OF_MINUTE_NAME = "ss";
+ public static final String ANSI_MILLISECOND_OF_MINUTE_NAME = "ms";
+ public static final String ANSI_WEEK_OF_YEAR = "ww";
+ public static final String ANSI_MONTH = "mm";
+ public static final String ANSI_HALFDAY_AM = "am";
+ public static final String ANSI_HALFDAY_PM = "pm";
+
+ //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_FULL_ERA_NAME = "G";
+ public static final String JODA_NAME_OF_DAY = "E";
+ public static final String JODA_TIME_ZONE_NAME = "TZ";
+ public static final String JODA_HOUR_12_NAME = "h";
+ public static final String JODA_HOUR_12_OTHER_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 = "s";
+ public static final String JODA_MILLISECOND_OF_MINUTE_NAME = "S";
+ public static final String JODA_WEEK_OF_YEAR = "w";
+ public static final String JODA_MONTH = "MM";
+ public static final String JODA_HALFDAY = "aa";
+
+ static {
+ ansiToJodaMap.put(ANSI_FULL_NAME_OF_DAY, JODA_FULL_NAME_OF_DAY);
+ ansiToJodaMap.put(ANSI_DAY_OF_YEAR, JODA_DAY_OF_YEAR);
+ ansiToJodaMap.put(ANSI_DAY_OF_MONTH, JODA_DAY_OF_MONTH);
+ ansiToJodaMap.put(ANSI_DAY_OF_WEEK, JODA_DAY_OF_WEEK);
+ ansiToJodaMap.put(ANSI_NAME_OF_MONTH, JODA_NAME_OF_MONTH);
+ ansiToJodaMap.put(ANSI_ABR_NAME_OF_MONTH, JODA_ABR_NAME_OF_MONTH);
+ ansiToJodaMap.put(ANSI_FULL_ERA_NAME, JODA_FULL_ERA_NAME);
+ ansiToJodaMap.put(ANSI_NAME_OF_DAY, JODA_NAME_OF_DAY);
+ ansiToJodaMap.put(ANSI_TIME_ZONE_NAME, JODA_TIME_ZONE_NAME);
+ ansiToJodaMap.put(ANSI_HOUR_12_NAME, JODA_HOUR_12_NAME);
+ ansiToJodaMap.put(ANSI_HOUR_12_OTHER_NAME, JODA_HOUR_12_OTHER_NAME);
+ ansiToJodaMap.put(ANSI_HOUR_24_NAME, JODA_HOUR_24_NAME);
+ ansiToJodaMap.put(ANSI_MINUTE_OF_HOUR_NAME, JODA_MINUTE_OF_HOUR_NAME);
+ ansiToJodaMap.put(ANSI_SECOND_OF_MINUTE_NAME, JODA_SECOND_OF_MINUTE_NAME);
+ ansiToJodaMap.put(ANSI_MILLISECOND_OF_MINUTE_NAME, JODA_MILLISECOND_OF_MINUTE_NAME);
+ ansiToJodaMap.put(ANSI_WEEK_OF_YEAR, JODA_WEEK_OF_YEAR);
+ ansiToJodaMap.put(ANSI_MONTH, JODA_MONTH);
+ ansiToJodaMap.put(ANSI_HALFDAY_AM, JODA_HALFDAY);
+ ansiToJodaMap.put(ANSI_HALFDAY_PM, JODA_HALFDAY);
+ }
+
+ static {
+ ansiValuesForDeleting.add(SUFFIX_SP);
+ ansiValuesForDeleting.add(PREFIX_FM);
+ ansiValuesForDeleting.add(PREFIX_FX);
+ ansiValuesForDeleting.add(PREFIX_TM);
+ }
+
+ /**
+ * Replaces all ansi patterns from {@param pattern},
+ * available in ansiToJodaMap keys to jodaTime equivalents.
+ *
+ * @param pattern date pattern in ansi format
+ * @return date pattern with replaced patterns in joda format
+ */
+ public static String toJodaFormat(String pattern) {
+ String preparedString = deleteFromAnsi(pattern);
--- End diff --
Done
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] drill pull request #581: DRILL-4864: Add ANSI format for date/time functions
Posted by paul-rogers <gi...@git.apache.org>.
Github user paul-rogers commented on a diff in the pull request:
https://github.com/apache/drill/pull/581#discussion_r95887742
--- Diff: logical/src/main/java/org/apache/drill/common/expression/fn/JodaDateValidator.java ---
@@ -0,0 +1,216 @@
+/*
+* 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.Sets;
+import org.apache.commons.lang3.StringUtils;
+import org.apache.drill.common.map.CaseInsensitiveMap;
+
+import java.util.Comparator;
+import java.util.Set;
+
+public class JodaDateValidator {
+
+ private static final Set<String> postgresValuesForDeleting = Sets.newTreeSet(new LengthDescComparator());
+ private static final CaseInsensitiveMap<String> postgresToJodaMap = CaseInsensitiveMap.newTreeMap(new LengthDescComparator());
+
+ // tokens for deleting
+ public static final String SUFFIX_SP = "sp";
+ public static final String PREFIX_FM = "fm";
+ public static final String PREFIX_FX = "fx";
+ public static final String PREFIX_TM = "tm";
+
+ // postgres patterns
+ public static final String POSTGRES_FULL_NAME_OF_DAY = "day";
+ public static final String POSTGRES_DAY_OF_YEAR = "ddd";
+ public static final String POSTGRES_DAY_OF_MONTH = "dd";
+ public static final String POSTGRES_DAY_OF_WEEK = "d";
+ public static final String POSTGRES_NAME_OF_MONTH = "month";
+ public static final String POSTGRES_ABR_NAME_OF_MONTH = "mon";
+ public static final String POSTGRES_FULL_ERA_NAME = "ee";
+ public static final String POSTGRES_NAME_OF_DAY = "dy";
+ public static final String POSTGRES_TIME_ZONE_NAME = "tz";
+ public static final String POSTGRES_HOUR_12_NAME = "hh";
+ public static final String POSTGRES_HOUR_12_OTHER_NAME = "hh12";
+ public static final String POSTGRES_HOUR_24_NAME = "hh24";
+ public static final String POSTGRES_MINUTE_OF_HOUR_NAME = "mi";
+ public static final String POSTGRES_SECOND_OF_MINUTE_NAME = "ss";
+ public static final String POSTGRES_MILLISECOND_OF_MINUTE_NAME = "ms";
+ public static final String POSTGRES_WEEK_OF_YEAR = "ww";
+ public static final String POSTGRES_MONTH = "mm";
+ public static final String POSTGRES_HALFDAY_AM = "am";
+ public static final String POSTGRES_HALFDAY_PM = "pm";
+
+ // 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_FULL_ERA_NAME = "G";
+ public static final String JODA_NAME_OF_DAY = "E";
+ public static final String JODA_TIME_ZONE_NAME = "TZ";
+ public static final String JODA_HOUR_12_NAME = "h";
+ public static final String JODA_HOUR_12_OTHER_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 = "s";
+ 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";
+
+ 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_TIME_ZONE_NAME, JODA_TIME_ZONE_NAME);
+ postgresToJodaMap.put(POSTGRES_HOUR_12_NAME, JODA_HOUR_12_NAME);
+ postgresToJodaMap.put(POSTGRES_HOUR_12_OTHER_NAME, JODA_HOUR_12_OTHER_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);
+ }
+
+ static {
+ postgresValuesForDeleting.add(SUFFIX_SP);
+ postgresValuesForDeleting.add(PREFIX_FM);
+ postgresValuesForDeleting.add(PREFIX_FX);
+ postgresValuesForDeleting.add(PREFIX_TM);
+ }
+
+ /**
+ * 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) {
--- End diff --
The more typical way that this kind of translation is done is to parse the input into tokens, then translate each token into the output format. In that way, we look for "runs of Y", possibly preceded by "I" as a token, then decide which Joda pattern to use depending on the resulting pattern.
This is also necessary if the format allows escape characters. Escapes throw off simple pattern matching. This is a date format: "SS". This is not: ""SS"". A context-free substitution may not catch this case, but a left-to-right parser will.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] drill pull request #581: DRILL-4864: Add ANSI format for date/time functions
Posted by paul-rogers <gi...@git.apache.org>.
Github user paul-rogers commented on a diff in the pull request:
https://github.com/apache/drill/pull/581#discussion_r95880960
--- Diff: exec/java-exec/src/test/java/org/apache/drill/exec/fn/impl/testing/TestDateConversions.java ---
@@ -0,0 +1,200 @@
+/*
+* 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.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;
+
+public class TestDateConversions extends BaseTestQuery {
--- End diff --
Good tests. Perhaps add:
1. Negative tests: what happens if the date does not fit the format?
2. Negative format tests: what happens if the format itself is not valid?
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] drill pull request #581: DRILL-4864: Add ANSI format for date/time functions
Posted by Serhii-Harnyk <gi...@git.apache.org>.
Github user Serhii-Harnyk commented on a diff in the pull request:
https://github.com/apache/drill/pull/581#discussion_r99311783
--- Diff: exec/java-exec/src/main/codegen/templates/DateIntervalFunctionTemplates/SqlToDateTypeFunctions.java ---
@@ -0,0 +1,77 @@
+/**
+ * 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
+ byte[] buf = new byte[right.end - right.start];
+ right.buffer.getBytes(right.start, buf, 0, right.end - right.start);
+ String formatString = new String(buf, com.google.common.base.Charsets.UTF_8);
+ String pattern = org.apache.drill.common.expression.fn.JodaDateValidator.toJodaFormat(formatString);
--- End diff --
Before the using Java 8 classes, we need change some patterns in JodaDateValidator.class, because there are some differences, but in general there shouldn't be user-visible change in behavior or semantics.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] drill issue #581: DRILL-4864: Add ANSI format for date/time functions
Posted by Serhii-Harnyk <gi...@git.apache.org>.
Github user Serhii-Harnyk commented on the issue:
https://github.com/apache/drill/pull/581
@adeneche - miliseconds issue is fixed in last commit
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] drill pull request #581: DRILL-4864: Add ANSI format for date/time functions
Posted by paul-rogers <gi...@git.apache.org>.
Github user paul-rogers commented on a diff in the pull request:
https://github.com/apache/drill/pull/581#discussion_r95865424
--- Diff: exec/java-exec/src/main/codegen/templates/DateIntervalFunctionTemplates/SqlToDateTypeFunctions.java ---
@@ -0,0 +1,77 @@
+/**
+ * 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
+ byte[] buf = new byte[right.end - right.start];
+ right.buffer.getBytes(right.start, buf, 0, right.end - right.start);
+ String formatString = new String(buf, com.google.common.base.Charsets.UTF_8);
--- End diff --
While this code is perfectly fine, I would recommend we move it elsewhere as we use it over and over again, and there is only one right way to extract a string from a VarChar vector. Perhaps this should appear in the VarCharVector.Accessor class?
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] drill pull request #581: DRILL-4864: Add ANSI format for date/time functions
Posted by Serhii-Harnyk <gi...@git.apache.org>.
Github user Serhii-Harnyk commented on a diff in the pull request:
https://github.com/apache/drill/pull/581#discussion_r99317199
--- Diff: logical/src/test/java/org/apache/drill/common/expression/fn/JodaDateValidatorTest.java ---
@@ -0,0 +1,176 @@
+/*
+* 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 org.joda.time.DateTime;
+import org.joda.time.DateTimeZone;
+import org.joda.time.format.DateTimeFormatter;
+import org.junit.Assert;
+import org.junit.Test;
+
+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 {
--- End diff --
Done
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] drill pull request #581: DRILL-4864: Add ANSI format for date/time functions
Posted by gparai <gi...@git.apache.org>.
Github user gparai commented on a diff in the pull request:
https://github.com/apache/drill/pull/581#discussion_r81440360
--- Diff: logical/src/main/java/org/apache/drill/common/expression/fn/JodaDateValidator.java ---
@@ -0,0 +1,213 @@
+/*
+* 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.Sets;
+import org.apache.commons.lang3.StringUtils;
+import org.apache.drill.common.map.CaseInsensitiveMap;
+
+import java.util.Comparator;
+import java.util.Set;
+
+public class JodaDateValidator {
+
+ private static final Set<String> ansiValuesForDeleting = Sets.newTreeSet(new LengthDescComparator());
+ private static final CaseInsensitiveMap<String> ansiToJodaMap = CaseInsensitiveMap.newTreeMap(new LengthDescComparator());
+
+ //tokens for deleting
+ public static final String SUFFIX_SP = "sp";
+ public static final String PREFIX_FM = "fm";
+ public static final String PREFIX_FX = "fx";
+ public static final String PREFIX_TM = "tm";
+
+ //ansi patterns
+ public static final String ANSI_FULL_NAME_OF_DAY = "day";
+ public static final String ANSI_DAY_OF_YEAR = "ddd";
+ public static final String ANSI_DAY_OF_MONTH = "dd";
+ public static final String ANSI_DAY_OF_WEEK = "d";
+ public static final String ANSI_NAME_OF_MONTH = "month";
+ public static final String ANSI_ABR_NAME_OF_MONTH = "mon";
+ public static final String ANSI_FULL_ERA_NAME = "ee";
+ public static final String ANSI_NAME_OF_DAY = "dy";
+ public static final String ANSI_TIME_ZONE_NAME = "tz";
+ public static final String ANSI_HOUR_12_NAME = "hh";
+ public static final String ANSI_HOUR_12_OTHER_NAME = "hh12";
+ public static final String ANSI_HOUR_24_NAME = "hh24";
+ public static final String ANSI_MINUTE_OF_HOUR_NAME = "mi";
+ public static final String ANSI_SECOND_OF_MINUTE_NAME = "ss";
+ public static final String ANSI_MILLISECOND_OF_MINUTE_NAME = "ms";
+ public static final String ANSI_WEEK_OF_YEAR = "ww";
+ public static final String ANSI_MONTH = "mm";
+ public static final String ANSI_HALFDAY_AM = "am";
+ public static final String ANSI_HALFDAY_PM = "pm";
+
+ //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_FULL_ERA_NAME = "G";
+ public static final String JODA_NAME_OF_DAY = "E";
+ public static final String JODA_TIME_ZONE_NAME = "TZ";
+ public static final String JODA_HOUR_12_NAME = "h";
+ public static final String JODA_HOUR_12_OTHER_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 = "s";
+ public static final String JODA_MILLISECOND_OF_MINUTE_NAME = "S";
+ public static final String JODA_WEEK_OF_YEAR = "w";
+ public static final String JODA_MONTH = "MM";
+ public static final String JODA_HALFDAY = "aa";
+
+ static {
+ ansiToJodaMap.put(ANSI_FULL_NAME_OF_DAY, JODA_FULL_NAME_OF_DAY);
+ ansiToJodaMap.put(ANSI_DAY_OF_YEAR, JODA_DAY_OF_YEAR);
+ ansiToJodaMap.put(ANSI_DAY_OF_MONTH, JODA_DAY_OF_MONTH);
+ ansiToJodaMap.put(ANSI_DAY_OF_WEEK, JODA_DAY_OF_WEEK);
+ ansiToJodaMap.put(ANSI_NAME_OF_MONTH, JODA_NAME_OF_MONTH);
+ ansiToJodaMap.put(ANSI_ABR_NAME_OF_MONTH, JODA_ABR_NAME_OF_MONTH);
+ ansiToJodaMap.put(ANSI_FULL_ERA_NAME, JODA_FULL_ERA_NAME);
+ ansiToJodaMap.put(ANSI_NAME_OF_DAY, JODA_NAME_OF_DAY);
+ ansiToJodaMap.put(ANSI_TIME_ZONE_NAME, JODA_TIME_ZONE_NAME);
+ ansiToJodaMap.put(ANSI_HOUR_12_NAME, JODA_HOUR_12_NAME);
+ ansiToJodaMap.put(ANSI_HOUR_12_OTHER_NAME, JODA_HOUR_12_OTHER_NAME);
+ ansiToJodaMap.put(ANSI_HOUR_24_NAME, JODA_HOUR_24_NAME);
+ ansiToJodaMap.put(ANSI_MINUTE_OF_HOUR_NAME, JODA_MINUTE_OF_HOUR_NAME);
+ ansiToJodaMap.put(ANSI_SECOND_OF_MINUTE_NAME, JODA_SECOND_OF_MINUTE_NAME);
+ ansiToJodaMap.put(ANSI_MILLISECOND_OF_MINUTE_NAME, JODA_MILLISECOND_OF_MINUTE_NAME);
+ ansiToJodaMap.put(ANSI_WEEK_OF_YEAR, JODA_WEEK_OF_YEAR);
+ ansiToJodaMap.put(ANSI_MONTH, JODA_MONTH);
+ ansiToJodaMap.put(ANSI_HALFDAY_AM, JODA_HALFDAY);
+ ansiToJodaMap.put(ANSI_HALFDAY_PM, JODA_HALFDAY);
+ }
+
+ static {
+ ansiValuesForDeleting.add(SUFFIX_SP);
+ ansiValuesForDeleting.add(PREFIX_FM);
+ ansiValuesForDeleting.add(PREFIX_FX);
+ ansiValuesForDeleting.add(PREFIX_TM);
+ }
+
+ /**
+ * Replaces all ansi patterns from {@param pattern},
+ * available in ansiToJodaMap keys to jodaTime equivalents.
+ *
+ * @param pattern date pattern in ansi format
+ * @return date pattern with replaced patterns in joda format
+ */
+ public static String toJodaFormat(String pattern) {
+ String preparedString = deleteFromAnsi(pattern);
+ StringBuilder builder = new StringBuilder(preparedString);
+
+ int start = 0; //every time search of ansi token in pattern will start from this index.
+ int minPos; //min position of the longest ansi token
+ do {
+ //finds first value with max length
+ minPos = builder.length();
+ String firstMatch = null;
+ for (String ansiPattern : ansiToJodaMap.keySet()) { //keys sorted in length decreasing
--- End diff --
Please add a comment explaining why the keys need to be in length decreasing maybe with an example. e.g. month and mon?
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] drill pull request #581: DRILL-4864: Add ANSI format for date/time functions
Posted by Serhii-Harnyk <gi...@git.apache.org>.
Github user Serhii-Harnyk commented on a diff in the pull request:
https://github.com/apache/drill/pull/581#discussion_r99312091
--- Diff: exec/java-exec/src/main/codegen/templates/DateIntervalFunctionTemplates/SqlToDateTypeFunctions.java ---
@@ -0,0 +1,77 @@
+/**
+ * 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
+ byte[] buf = new byte[right.end - right.start];
+ right.buffer.getBytes(right.start, buf, 0, right.end - right.start);
+ String formatString = new String(buf, com.google.common.base.Charsets.UTF_8);
+ String pattern = org.apache.drill.common.expression.fn.JodaDateValidator.toJodaFormat(formatString);
+ format = org.joda.time.format.DateTimeFormat.forPattern(pattern);
+ }
+
+ public void eval() {
+ // Get the input
+ byte[] buf1 = new byte[left.end - left.start];
+ left.buffer.getBytes(left.start, buf1, 0, left.end - left.start);
+ String input = new String(buf1, com.google.common.base.Charsets.UTF_8);
+
+ <#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>
--- End diff --
Yes, we should. Fixed, thanks.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] drill pull request #581: DRILL-4864: Add ANSI format for date/time functions
Posted by Serhii-Harnyk <gi...@git.apache.org>.
Github user Serhii-Harnyk commented on a diff in the pull request:
https://github.com/apache/drill/pull/581#discussion_r83871606
--- Diff: exec/java-exec/src/main/java/org/apache/drill/exec/planner/logical/DrillOptiq.java ---
@@ -408,6 +411,12 @@ private LogicalExpression getDrillFunctionFromOptiqCall(RexCall call) {
return first;
}
+ } else if (functionName.equals("to_date") || functionName.equals("to_time") || functionName.equals("to_timestamp")) {
+ // convert ansi date format string to joda according to session option
+ OptionManager om = this.context.getPlannerSettings().getOptions();
+ if(ToDateFormats.valueOf(om.getOption(ExecConstants.TO_DATE_FORMAT).string_val.toUpperCase()).equals(ToDateFormats.ANSI)) {
--- End diff --
Fixed
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] drill pull request #581: DRILL-4864: Add ANSI format for date/time functions
Posted by paul-rogers <gi...@git.apache.org>.
Github user paul-rogers commented on a diff in the pull request:
https://github.com/apache/drill/pull/581#discussion_r95887588
--- Diff: logical/src/main/java/org/apache/drill/common/expression/fn/JodaDateValidator.java ---
@@ -0,0 +1,216 @@
+/*
+* 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.Sets;
+import org.apache.commons.lang3.StringUtils;
+import org.apache.drill.common.map.CaseInsensitiveMap;
+
+import java.util.Comparator;
+import java.util.Set;
+
+public class JodaDateValidator {
+
+ private static final Set<String> postgresValuesForDeleting = Sets.newTreeSet(new LengthDescComparator());
+ private static final CaseInsensitiveMap<String> postgresToJodaMap = CaseInsensitiveMap.newTreeMap(new LengthDescComparator());
+
+ // tokens for deleting
+ public static final String SUFFIX_SP = "sp";
+ public static final String PREFIX_FM = "fm";
+ public static final String PREFIX_FX = "fx";
+ public static final String PREFIX_TM = "tm";
+
+ // postgres patterns
+ public static final String POSTGRES_FULL_NAME_OF_DAY = "day";
+ public static final String POSTGRES_DAY_OF_YEAR = "ddd";
+ public static final String POSTGRES_DAY_OF_MONTH = "dd";
+ public static final String POSTGRES_DAY_OF_WEEK = "d";
+ public static final String POSTGRES_NAME_OF_MONTH = "month";
+ public static final String POSTGRES_ABR_NAME_OF_MONTH = "mon";
+ public static final String POSTGRES_FULL_ERA_NAME = "ee";
+ public static final String POSTGRES_NAME_OF_DAY = "dy";
+ public static final String POSTGRES_TIME_ZONE_NAME = "tz";
+ public static final String POSTGRES_HOUR_12_NAME = "hh";
+ public static final String POSTGRES_HOUR_12_OTHER_NAME = "hh12";
+ public static final String POSTGRES_HOUR_24_NAME = "hh24";
+ public static final String POSTGRES_MINUTE_OF_HOUR_NAME = "mi";
+ public static final String POSTGRES_SECOND_OF_MINUTE_NAME = "ss";
+ public static final String POSTGRES_MILLISECOND_OF_MINUTE_NAME = "ms";
+ public static final String POSTGRES_WEEK_OF_YEAR = "ww";
+ public static final String POSTGRES_MONTH = "mm";
+ public static final String POSTGRES_HALFDAY_AM = "am";
+ public static final String POSTGRES_HALFDAY_PM = "pm";
+
+ // 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_FULL_ERA_NAME = "G";
+ public static final String JODA_NAME_OF_DAY = "E";
+ public static final String JODA_TIME_ZONE_NAME = "TZ";
+ public static final String JODA_HOUR_12_NAME = "h";
+ public static final String JODA_HOUR_12_OTHER_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 = "s";
+ 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";
+
+ 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_TIME_ZONE_NAME, JODA_TIME_ZONE_NAME);
+ postgresToJodaMap.put(POSTGRES_HOUR_12_NAME, JODA_HOUR_12_NAME);
+ postgresToJodaMap.put(POSTGRES_HOUR_12_OTHER_NAME, JODA_HOUR_12_OTHER_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);
+ }
+
+ static {
+ postgresValuesForDeleting.add(SUFFIX_SP);
+ postgresValuesForDeleting.add(PREFIX_FM);
+ postgresValuesForDeleting.add(PREFIX_FX);
+ postgresValuesForDeleting.add(PREFIX_TM);
+ }
+
+ /**
+ * 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) {
--- End diff --
The premise of this function is that no one pattern is a subset of another pattern. And, no one pattern is the same as the same as part of two adjacent patterns. That is, we know that, say, SSSS is a single pattern and not two copies of SS next to one another. Have we done the analysis to ensure that no such aliases are possible?
Or, for example, consider these patterns: DAY, Y, YY, YYY, IYY and IYYY. If we replace "Y" first, we end up matching "DAY" or "IYY" when we should not. If we do "IY" first, we miss "IYYY". If the input is "DAYY", and we do "YY" first, we miss that this is "DAY - Y", not "DA - YY". In short, context seems essential. Or, the substitutions must be carefully ordered. I think that, for the above, "IYYY", "IYY", "YYY", "YY", "DAY", "Y" would be the proper order. But, since we use a map, do we have an implied replacement order?
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] drill issue #581: DRILL-4864: Add ANSI format for date/time functions
Posted by Serhii-Harnyk <gi...@git.apache.org>.
Github user Serhii-Harnyk commented on the issue:
https://github.com/apache/drill/pull/581
@paul-rogers, I have implemented changes that you have proposed. Could you take a look?
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] drill pull request #581: DRILL-4864: Add ANSI format for date/time functions
Posted by paul-rogers <gi...@git.apache.org>.
Github user paul-rogers commented on a diff in the pull request:
https://github.com/apache/drill/pull/581#discussion_r95883122
--- Diff: logical/src/main/java/org/apache/drill/common/expression/fn/JodaDateValidator.java ---
@@ -0,0 +1,216 @@
+/*
+* 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.Sets;
+import org.apache.commons.lang3.StringUtils;
+import org.apache.drill.common.map.CaseInsensitiveMap;
+
+import java.util.Comparator;
+import java.util.Set;
+
+public class JodaDateValidator {
+
+ private static final Set<String> postgresValuesForDeleting = Sets.newTreeSet(new LengthDescComparator());
+ private static final CaseInsensitiveMap<String> postgresToJodaMap = CaseInsensitiveMap.newTreeMap(new LengthDescComparator());
+
+ // tokens for deleting
+ public static final String SUFFIX_SP = "sp";
+ public static final String PREFIX_FM = "fm";
+ public static final String PREFIX_FX = "fx";
+ public static final String PREFIX_TM = "tm";
+
+ // postgres patterns
+ public static final String POSTGRES_FULL_NAME_OF_DAY = "day";
+ public static final String POSTGRES_DAY_OF_YEAR = "ddd";
+ public static final String POSTGRES_DAY_OF_MONTH = "dd";
+ public static final String POSTGRES_DAY_OF_WEEK = "d";
+ public static final String POSTGRES_NAME_OF_MONTH = "month";
+ public static final String POSTGRES_ABR_NAME_OF_MONTH = "mon";
+ public static final String POSTGRES_FULL_ERA_NAME = "ee";
+ public static final String POSTGRES_NAME_OF_DAY = "dy";
+ public static final String POSTGRES_TIME_ZONE_NAME = "tz";
+ public static final String POSTGRES_HOUR_12_NAME = "hh";
+ public static final String POSTGRES_HOUR_12_OTHER_NAME = "hh12";
+ public static final String POSTGRES_HOUR_24_NAME = "hh24";
+ public static final String POSTGRES_MINUTE_OF_HOUR_NAME = "mi";
+ public static final String POSTGRES_SECOND_OF_MINUTE_NAME = "ss";
+ public static final String POSTGRES_MILLISECOND_OF_MINUTE_NAME = "ms";
+ public static final String POSTGRES_WEEK_OF_YEAR = "ww";
+ public static final String POSTGRES_MONTH = "mm";
+ public static final String POSTGRES_HALFDAY_AM = "am";
+ public static final String POSTGRES_HALFDAY_PM = "pm";
+
+ // 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_FULL_ERA_NAME = "G";
+ public static final String JODA_NAME_OF_DAY = "E";
+ public static final String JODA_TIME_ZONE_NAME = "TZ";
+ public static final String JODA_HOUR_12_NAME = "h";
+ public static final String JODA_HOUR_12_OTHER_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 = "s";
+ 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";
+
+ 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_TIME_ZONE_NAME, JODA_TIME_ZONE_NAME);
+ postgresToJodaMap.put(POSTGRES_HOUR_12_NAME, JODA_HOUR_12_NAME);
+ postgresToJodaMap.put(POSTGRES_HOUR_12_OTHER_NAME, JODA_HOUR_12_OTHER_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);
+ }
+
+ static {
+ postgresValuesForDeleting.add(SUFFIX_SP);
+ postgresValuesForDeleting.add(PREFIX_FM);
+ postgresValuesForDeleting.add(PREFIX_FX);
+ postgresValuesForDeleting.add(PREFIX_TM);
+ }
+
+ /**
+ * 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) {
--- End diff --
The list of formats does not seem to include the full set listed at https://www.postgresql.org/docs/9.0/static/functions-formatting.html - or am I missing something?
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] drill pull request #581: DRILL-4864: Add ANSI format for date/time functions
Posted by Serhii-Harnyk <gi...@git.apache.org>.
Github user Serhii-Harnyk commented on a diff in the pull request:
https://github.com/apache/drill/pull/581#discussion_r83871584
--- Diff: exec/java-exec/src/main/java/org/apache/drill/exec/planner/logical/DrillOptiq.java ---
@@ -408,6 +411,12 @@ private LogicalExpression getDrillFunctionFromOptiqCall(RexCall call) {
return first;
}
+ } else if (functionName.equals("to_date") || functionName.equals("to_time") || functionName.equals("to_timestamp")) {
--- End diff --
No. Variable functionName has been already lowercased. It's for all functions.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] drill pull request #581: DRILL-4864: Add ANSI format for date/time functions
Posted by Serhii-Harnyk <gi...@git.apache.org>.
Github user Serhii-Harnyk commented on a diff in the pull request:
https://github.com/apache/drill/pull/581#discussion_r99315665
--- Diff: logical/src/main/java/org/apache/drill/common/expression/fn/JodaDateValidator.java ---
@@ -0,0 +1,216 @@
+/*
+* 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.Sets;
+import org.apache.commons.lang3.StringUtils;
+import org.apache.drill.common.map.CaseInsensitiveMap;
+
+import java.util.Comparator;
+import java.util.Set;
+
+public class JodaDateValidator {
+
+ private static final Set<String> postgresValuesForDeleting = Sets.newTreeSet(new LengthDescComparator());
+ private static final CaseInsensitiveMap<String> postgresToJodaMap = CaseInsensitiveMap.newTreeMap(new LengthDescComparator());
+
+ // tokens for deleting
+ public static final String SUFFIX_SP = "sp";
+ public static final String PREFIX_FM = "fm";
+ public static final String PREFIX_FX = "fx";
+ public static final String PREFIX_TM = "tm";
+
+ // postgres patterns
+ public static final String POSTGRES_FULL_NAME_OF_DAY = "day";
+ public static final String POSTGRES_DAY_OF_YEAR = "ddd";
+ public static final String POSTGRES_DAY_OF_MONTH = "dd";
+ public static final String POSTGRES_DAY_OF_WEEK = "d";
+ public static final String POSTGRES_NAME_OF_MONTH = "month";
+ public static final String POSTGRES_ABR_NAME_OF_MONTH = "mon";
+ public static final String POSTGRES_FULL_ERA_NAME = "ee";
+ public static final String POSTGRES_NAME_OF_DAY = "dy";
+ public static final String POSTGRES_TIME_ZONE_NAME = "tz";
+ public static final String POSTGRES_HOUR_12_NAME = "hh";
+ public static final String POSTGRES_HOUR_12_OTHER_NAME = "hh12";
+ public static final String POSTGRES_HOUR_24_NAME = "hh24";
+ public static final String POSTGRES_MINUTE_OF_HOUR_NAME = "mi";
+ public static final String POSTGRES_SECOND_OF_MINUTE_NAME = "ss";
+ public static final String POSTGRES_MILLISECOND_OF_MINUTE_NAME = "ms";
+ public static final String POSTGRES_WEEK_OF_YEAR = "ww";
+ public static final String POSTGRES_MONTH = "mm";
+ public static final String POSTGRES_HALFDAY_AM = "am";
+ public static final String POSTGRES_HALFDAY_PM = "pm";
+
+ // 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_FULL_ERA_NAME = "G";
+ public static final String JODA_NAME_OF_DAY = "E";
+ public static final String JODA_TIME_ZONE_NAME = "TZ";
+ public static final String JODA_HOUR_12_NAME = "h";
+ public static final String JODA_HOUR_12_OTHER_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 = "s";
+ 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";
+
+ 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_TIME_ZONE_NAME, JODA_TIME_ZONE_NAME);
+ postgresToJodaMap.put(POSTGRES_HOUR_12_NAME, JODA_HOUR_12_NAME);
+ postgresToJodaMap.put(POSTGRES_HOUR_12_OTHER_NAME, JODA_HOUR_12_OTHER_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);
+ }
+
+ static {
+ postgresValuesForDeleting.add(SUFFIX_SP);
+ postgresValuesForDeleting.add(PREFIX_FM);
+ postgresValuesForDeleting.add(PREFIX_FX);
+ postgresValuesForDeleting.add(PREFIX_TM);
+ }
+
+ /**
+ * 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) {
--- End diff --
Added support of escape characters and unit tests to check correct behavior.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] drill pull request #581: DRILL-4864: Add ANSI format for date/time functions
Posted by Serhii-Harnyk <gi...@git.apache.org>.
Github user Serhii-Harnyk commented on a diff in the pull request:
https://github.com/apache/drill/pull/581#discussion_r99315319
--- Diff: logical/src/main/java/org/apache/drill/common/expression/fn/JodaDateValidator.java ---
@@ -0,0 +1,216 @@
+/*
+* 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.Sets;
+import org.apache.commons.lang3.StringUtils;
+import org.apache.drill.common.map.CaseInsensitiveMap;
+
+import java.util.Comparator;
+import java.util.Set;
+
+public class JodaDateValidator {
+
+ private static final Set<String> postgresValuesForDeleting = Sets.newTreeSet(new LengthDescComparator());
+ private static final CaseInsensitiveMap<String> postgresToJodaMap = CaseInsensitiveMap.newTreeMap(new LengthDescComparator());
+
+ // tokens for deleting
+ public static final String SUFFIX_SP = "sp";
+ public static final String PREFIX_FM = "fm";
+ public static final String PREFIX_FX = "fx";
+ public static final String PREFIX_TM = "tm";
+
+ // postgres patterns
+ public static final String POSTGRES_FULL_NAME_OF_DAY = "day";
+ public static final String POSTGRES_DAY_OF_YEAR = "ddd";
+ public static final String POSTGRES_DAY_OF_MONTH = "dd";
+ public static final String POSTGRES_DAY_OF_WEEK = "d";
+ public static final String POSTGRES_NAME_OF_MONTH = "month";
+ public static final String POSTGRES_ABR_NAME_OF_MONTH = "mon";
+ public static final String POSTGRES_FULL_ERA_NAME = "ee";
+ public static final String POSTGRES_NAME_OF_DAY = "dy";
+ public static final String POSTGRES_TIME_ZONE_NAME = "tz";
+ public static final String POSTGRES_HOUR_12_NAME = "hh";
+ public static final String POSTGRES_HOUR_12_OTHER_NAME = "hh12";
+ public static final String POSTGRES_HOUR_24_NAME = "hh24";
+ public static final String POSTGRES_MINUTE_OF_HOUR_NAME = "mi";
+ public static final String POSTGRES_SECOND_OF_MINUTE_NAME = "ss";
+ public static final String POSTGRES_MILLISECOND_OF_MINUTE_NAME = "ms";
+ public static final String POSTGRES_WEEK_OF_YEAR = "ww";
+ public static final String POSTGRES_MONTH = "mm";
+ public static final String POSTGRES_HALFDAY_AM = "am";
+ public static final String POSTGRES_HALFDAY_PM = "pm";
+
+ // 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_FULL_ERA_NAME = "G";
+ public static final String JODA_NAME_OF_DAY = "E";
+ public static final String JODA_TIME_ZONE_NAME = "TZ";
+ public static final String JODA_HOUR_12_NAME = "h";
+ public static final String JODA_HOUR_12_OTHER_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 = "s";
+ 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";
+
+ 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_TIME_ZONE_NAME, JODA_TIME_ZONE_NAME);
+ postgresToJodaMap.put(POSTGRES_HOUR_12_NAME, JODA_HOUR_12_NAME);
+ postgresToJodaMap.put(POSTGRES_HOUR_12_OTHER_NAME, JODA_HOUR_12_OTHER_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);
+ }
+
+ static {
+ postgresValuesForDeleting.add(SUFFIX_SP);
+ postgresValuesForDeleting.add(PREFIX_FM);
+ postgresValuesForDeleting.add(PREFIX_FX);
+ postgresValuesForDeleting.add(PREFIX_TM);
+ }
+
+ /**
+ * 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) {
--- End diff --
Such aliases are not possible in current implementation.
More details about replacement ordering you may find in mini-spec, linked in Jira.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] drill pull request #581: DRILL-4864: Add ANSI format for date/time functions
Posted by Serhii-Harnyk <gi...@git.apache.org>.
Github user Serhii-Harnyk commented on a diff in the pull request:
https://github.com/apache/drill/pull/581#discussion_r83871296
--- Diff: exec/java-exec/src/main/java/org/apache/drill/exec/expr/fn/impl/AnsiToJoda.java ---
@@ -0,0 +1,58 @@
+/*
+* 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.expr.fn.impl;
+
+import io.netty.buffer.DrillBuf;
+import org.apache.drill.exec.expr.DrillSimpleFunc;
+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.VarCharHolder;
+
+import javax.inject.Inject;
+
+/**
+ * Replaces all ansi patterns to joda equivalents.
+ */
+@FunctionTemplate(name = "ansi_to_joda",
+ scope = FunctionTemplate.FunctionScope.SIMPLE,
+ nulls= FunctionTemplate.NullHandling.NULL_IF_NULL)
+public class AnsiToJoda implements DrillSimpleFunc {
+
+ @Param
+ VarCharHolder in;
+
+ @Output
+ VarCharHolder out;
+
+ @Inject
+ DrillBuf buffer;
+
+ @Override
+ public void setup() {
+ }
+
+ @Override
+ public void eval() {
+ String pattern = org.apache.drill.exec.expr.fn.impl.StringFunctionHelpers.toStringFromUTF8(in.start, in.end, in.buffer);
--- End diff --
Datetime patterns validation isn't a simple task. For example JODA Time has no validation.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] drill pull request #581: DRILL-4864: Add ANSI format for date/time functions
Posted by gparai <gi...@git.apache.org>.
Github user gparai commented on a diff in the pull request:
https://github.com/apache/drill/pull/581#discussion_r81438363
--- Diff: exec/java-exec/src/main/java/org/apache/drill/exec/expr/fn/impl/AnsiToJoda.java ---
@@ -0,0 +1,58 @@
+/*
+* 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.expr.fn.impl;
+
+import io.netty.buffer.DrillBuf;
+import org.apache.drill.exec.expr.DrillSimpleFunc;
+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.VarCharHolder;
+
+import javax.inject.Inject;
+
+/**
+ * Replaces all ansi patterns to joda equivalents.
+ */
+@FunctionTemplate(name = "ansi_to_joda",
+ scope = FunctionTemplate.FunctionScope.SIMPLE,
+ nulls= FunctionTemplate.NullHandling.NULL_IF_NULL)
--- End diff --
nulls =
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] drill pull request #581: DRILL-4864: Add ANSI format for date/time functions
Posted by Serhii-Harnyk <gi...@git.apache.org>.
Github user Serhii-Harnyk commented on a diff in the pull request:
https://github.com/apache/drill/pull/581#discussion_r83871748
--- Diff: exec/java-exec/src/main/java/org/apache/drill/exec/planner/logical/DrillOptiq.java ---
@@ -408,6 +411,12 @@ private LogicalExpression getDrillFunctionFromOptiqCall(RexCall call) {
return first;
}
+ } else if (functionName.equals("to_date") || functionName.equals("to_time") || functionName.equals("to_timestamp")) {
+ // convert ansi date format string to joda according to session option
+ OptionManager om = this.context.getPlannerSettings().getOptions();
+ if(ToDateFormats.valueOf(om.getOption(ExecConstants.TO_DATE_FORMAT).string_val.toUpperCase()).equals(ToDateFormats.ANSI)) {
+ args.set(1, FunctionCallFactory.createExpression("ansi_to_joda", Arrays.asList(args.get(1))));
--- End diff --
Yes, it would be two nested ansi_to_joda conversions.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] drill pull request #581: DRILL-4864: Add ANSI format for date/time functions
Posted by Serhii-Harnyk <gi...@git.apache.org>.
Github user Serhii-Harnyk commented on a diff in the pull request:
https://github.com/apache/drill/pull/581#discussion_r99316546
--- Diff: logical/src/main/java/org/apache/drill/common/expression/fn/JodaDateValidator.java ---
@@ -0,0 +1,216 @@
+/*
+* 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.Sets;
+import org.apache.commons.lang3.StringUtils;
+import org.apache.drill.common.map.CaseInsensitiveMap;
+
+import java.util.Comparator;
+import java.util.Set;
+
+public class JodaDateValidator {
+
+ private static final Set<String> postgresValuesForDeleting = Sets.newTreeSet(new LengthDescComparator());
+ private static final CaseInsensitiveMap<String> postgresToJodaMap = CaseInsensitiveMap.newTreeMap(new LengthDescComparator());
+
+ // tokens for deleting
+ public static final String SUFFIX_SP = "sp";
+ public static final String PREFIX_FM = "fm";
+ public static final String PREFIX_FX = "fx";
+ public static final String PREFIX_TM = "tm";
+
+ // postgres patterns
+ public static final String POSTGRES_FULL_NAME_OF_DAY = "day";
+ public static final String POSTGRES_DAY_OF_YEAR = "ddd";
+ public static final String POSTGRES_DAY_OF_MONTH = "dd";
+ public static final String POSTGRES_DAY_OF_WEEK = "d";
+ public static final String POSTGRES_NAME_OF_MONTH = "month";
+ public static final String POSTGRES_ABR_NAME_OF_MONTH = "mon";
+ public static final String POSTGRES_FULL_ERA_NAME = "ee";
+ public static final String POSTGRES_NAME_OF_DAY = "dy";
+ public static final String POSTGRES_TIME_ZONE_NAME = "tz";
+ public static final String POSTGRES_HOUR_12_NAME = "hh";
+ public static final String POSTGRES_HOUR_12_OTHER_NAME = "hh12";
+ public static final String POSTGRES_HOUR_24_NAME = "hh24";
+ public static final String POSTGRES_MINUTE_OF_HOUR_NAME = "mi";
+ public static final String POSTGRES_SECOND_OF_MINUTE_NAME = "ss";
+ public static final String POSTGRES_MILLISECOND_OF_MINUTE_NAME = "ms";
+ public static final String POSTGRES_WEEK_OF_YEAR = "ww";
+ public static final String POSTGRES_MONTH = "mm";
+ public static final String POSTGRES_HALFDAY_AM = "am";
+ public static final String POSTGRES_HALFDAY_PM = "pm";
+
+ // 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_FULL_ERA_NAME = "G";
+ public static final String JODA_NAME_OF_DAY = "E";
+ public static final String JODA_TIME_ZONE_NAME = "TZ";
+ public static final String JODA_HOUR_12_NAME = "h";
+ public static final String JODA_HOUR_12_OTHER_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 = "s";
+ 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";
+
+ 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_TIME_ZONE_NAME, JODA_TIME_ZONE_NAME);
+ postgresToJodaMap.put(POSTGRES_HOUR_12_NAME, JODA_HOUR_12_NAME);
+ postgresToJodaMap.put(POSTGRES_HOUR_12_OTHER_NAME, JODA_HOUR_12_OTHER_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);
+ }
+
+ static {
+ postgresValuesForDeleting.add(SUFFIX_SP);
+ postgresValuesForDeleting.add(PREFIX_FM);
+ postgresValuesForDeleting.add(PREFIX_FX);
+ postgresValuesForDeleting.add(PREFIX_TM);
+ }
+
+ /**
+ * 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) {
--- End diff --
The validation of resulting pattern produced only in Joda functions.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] drill issue #581: DRILL-4864: Add ANSI format for date/time functions
Posted by paul-rogers <gi...@git.apache.org>.
Github user paul-rogers commented on the issue:
https://github.com/apache/drill/pull/581
Please see comments in the JIRA entry. IMHO, the approach taken can be improved. We should not attempt to change the format used by the existing to_date function. Instead, we should 1) define the new dialect (is there really an ANSI standard?) and 2) create a new function: sql_to_date that uses the new format.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] drill issue #581: DRILL-4864: Add ANSI format for date/time functions
Posted by Serhii-Harnyk <gi...@git.apache.org>.
Github user Serhii-Harnyk commented on the issue:
https://github.com/apache/drill/pull/581
@adeneche
What about fixed milliseconds?
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] drill pull request #581: DRILL-4864: Add ANSI format for date/time functions
Posted by Serhii-Harnyk <gi...@git.apache.org>.
Github user Serhii-Harnyk commented on a diff in the pull request:
https://github.com/apache/drill/pull/581#discussion_r99311060
--- Diff: exec/java-exec/src/main/codegen/templates/DateIntervalFunctionTemplates/SqlToDateTypeFunctions.java ---
@@ -0,0 +1,77 @@
+/**
+ * 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
+ byte[] buf = new byte[right.end - right.start];
+ right.buffer.getBytes(right.start, buf, 0, right.end - right.start);
+ String formatString = new String(buf, com.google.common.base.Charsets.UTF_8);
--- End diff --
I found the method that does the same. Fixed.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] drill pull request #581: DRILL-4864: Add ANSI format for date/time functions
Posted by gparai <gi...@git.apache.org>.
Github user gparai commented on a diff in the pull request:
https://github.com/apache/drill/pull/581#discussion_r81438265
--- Diff: exec/java-exec/src/main/java/org/apache/drill/exec/planner/logical/DrillOptiq.java ---
@@ -408,6 +411,12 @@ private LogicalExpression getDrillFunctionFromOptiqCall(RexCall call) {
return first;
}
+ } else if (functionName.equals("to_date") || functionName.equals("to_time") || functionName.equals("to_timestamp")) {
--- End diff --
equalsIgnoreCase needed?
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] drill pull request #581: DRILL-4864: Add ANSI format for date/time functions
Posted by gparai <gi...@git.apache.org>.
Github user gparai commented on a diff in the pull request:
https://github.com/apache/drill/pull/581#discussion_r81439112
--- Diff: logical/src/main/java/org/apache/drill/common/expression/fn/JodaDateValidator.java ---
@@ -0,0 +1,213 @@
+/*
+* 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.Sets;
+import org.apache.commons.lang3.StringUtils;
+import org.apache.drill.common.map.CaseInsensitiveMap;
+
+import java.util.Comparator;
+import java.util.Set;
+
+public class JodaDateValidator {
+
+ private static final Set<String> ansiValuesForDeleting = Sets.newTreeSet(new LengthDescComparator());
+ private static final CaseInsensitiveMap<String> ansiToJodaMap = CaseInsensitiveMap.newTreeMap(new LengthDescComparator());
+
+ //tokens for deleting
+ public static final String SUFFIX_SP = "sp";
+ public static final String PREFIX_FM = "fm";
+ public static final String PREFIX_FX = "fx";
+ public static final String PREFIX_TM = "tm";
+
+ //ansi patterns
+ public static final String ANSI_FULL_NAME_OF_DAY = "day";
+ public static final String ANSI_DAY_OF_YEAR = "ddd";
+ public static final String ANSI_DAY_OF_MONTH = "dd";
+ public static final String ANSI_DAY_OF_WEEK = "d";
+ public static final String ANSI_NAME_OF_MONTH = "month";
+ public static final String ANSI_ABR_NAME_OF_MONTH = "mon";
+ public static final String ANSI_FULL_ERA_NAME = "ee";
+ public static final String ANSI_NAME_OF_DAY = "dy";
+ public static final String ANSI_TIME_ZONE_NAME = "tz";
+ public static final String ANSI_HOUR_12_NAME = "hh";
+ public static final String ANSI_HOUR_12_OTHER_NAME = "hh12";
+ public static final String ANSI_HOUR_24_NAME = "hh24";
+ public static final String ANSI_MINUTE_OF_HOUR_NAME = "mi";
+ public static final String ANSI_SECOND_OF_MINUTE_NAME = "ss";
+ public static final String ANSI_MILLISECOND_OF_MINUTE_NAME = "ms";
+ public static final String ANSI_WEEK_OF_YEAR = "ww";
+ public static final String ANSI_MONTH = "mm";
+ public static final String ANSI_HALFDAY_AM = "am";
+ public static final String ANSI_HALFDAY_PM = "pm";
+
+ //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_FULL_ERA_NAME = "G";
+ public static final String JODA_NAME_OF_DAY = "E";
+ public static final String JODA_TIME_ZONE_NAME = "TZ";
+ public static final String JODA_HOUR_12_NAME = "h";
+ public static final String JODA_HOUR_12_OTHER_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 = "s";
+ public static final String JODA_MILLISECOND_OF_MINUTE_NAME = "S";
+ public static final String JODA_WEEK_OF_YEAR = "w";
+ public static final String JODA_MONTH = "MM";
+ public static final String JODA_HALFDAY = "aa";
+
+ static {
+ ansiToJodaMap.put(ANSI_FULL_NAME_OF_DAY, JODA_FULL_NAME_OF_DAY);
+ ansiToJodaMap.put(ANSI_DAY_OF_YEAR, JODA_DAY_OF_YEAR);
+ ansiToJodaMap.put(ANSI_DAY_OF_MONTH, JODA_DAY_OF_MONTH);
+ ansiToJodaMap.put(ANSI_DAY_OF_WEEK, JODA_DAY_OF_WEEK);
+ ansiToJodaMap.put(ANSI_NAME_OF_MONTH, JODA_NAME_OF_MONTH);
+ ansiToJodaMap.put(ANSI_ABR_NAME_OF_MONTH, JODA_ABR_NAME_OF_MONTH);
+ ansiToJodaMap.put(ANSI_FULL_ERA_NAME, JODA_FULL_ERA_NAME);
+ ansiToJodaMap.put(ANSI_NAME_OF_DAY, JODA_NAME_OF_DAY);
+ ansiToJodaMap.put(ANSI_TIME_ZONE_NAME, JODA_TIME_ZONE_NAME);
+ ansiToJodaMap.put(ANSI_HOUR_12_NAME, JODA_HOUR_12_NAME);
+ ansiToJodaMap.put(ANSI_HOUR_12_OTHER_NAME, JODA_HOUR_12_OTHER_NAME);
+ ansiToJodaMap.put(ANSI_HOUR_24_NAME, JODA_HOUR_24_NAME);
+ ansiToJodaMap.put(ANSI_MINUTE_OF_HOUR_NAME, JODA_MINUTE_OF_HOUR_NAME);
+ ansiToJodaMap.put(ANSI_SECOND_OF_MINUTE_NAME, JODA_SECOND_OF_MINUTE_NAME);
+ ansiToJodaMap.put(ANSI_MILLISECOND_OF_MINUTE_NAME, JODA_MILLISECOND_OF_MINUTE_NAME);
+ ansiToJodaMap.put(ANSI_WEEK_OF_YEAR, JODA_WEEK_OF_YEAR);
+ ansiToJodaMap.put(ANSI_MONTH, JODA_MONTH);
+ ansiToJodaMap.put(ANSI_HALFDAY_AM, JODA_HALFDAY);
+ ansiToJodaMap.put(ANSI_HALFDAY_PM, JODA_HALFDAY);
+ }
+
+ static {
+ ansiValuesForDeleting.add(SUFFIX_SP);
+ ansiValuesForDeleting.add(PREFIX_FM);
+ ansiValuesForDeleting.add(PREFIX_FX);
+ ansiValuesForDeleting.add(PREFIX_TM);
+ }
+
+ /**
+ * Replaces all ansi patterns from {@param pattern},
+ * available in ansiToJodaMap keys to jodaTime equivalents.
+ *
+ * @param pattern date pattern in ansi format
+ * @return date pattern with replaced patterns in joda format
+ */
+ public static String toJodaFormat(String pattern) {
+ String preparedString = deleteFromAnsi(pattern);
--- End diff --
Please add a comment explaining `String preparedString = deleteFromAnsi(pattern)`
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---