subject:"\[jira\] \[Commented\] \(DRILL\-4864\) Add ANSI format for date\/time functions"

[jira] [Commented] (DRILL-4864) Add ANSI format for date/time functions

2017-02-13 Thread Serhii Harnyk (JIRA)


[ 
https://issues.apache.org/jira/browse/DRILL-4864?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15863355#comment-15863355
 ] 

Serhii Harnyk commented on DRILL-4864:
--

Fixed in 
[c9a6ac4|https://github.com/apache/drill/commit/c9a6ac4fc8859693b7b0a71885afb700f81e345c]

> Add ANSI format for date/time functions
> ---
>
> Key: DRILL-4864
> URL: https://issues.apache.org/jira/browse/DRILL-4864
> Project: Apache Drill
>  Issue Type: Improvement
>Affects Versions: 1.8.0
>Reporter: Serhii Harnyk
>Assignee: Serhii Harnyk
>  Labels: doc-impacting, ready-to-commit
> Fix For: 1.10
>
>
> The TO_DATE() is exposing the Joda string formatting conventions into the SQL 
> layer. This is not following SQL conventions used by ANSI and many other 
> database engines on the market.
> Add new UDFs: 
> * sql_to_date(String, Format), 
> * sql_to_time(String, Format), 
> * sql_to_timestamp(String, Format)
> that requires Postgres datetime format.
> Table of supported Postgres patterns
> ||Pattern name||Postgres format   
> |Full name of day|day   
> |Day of year|ddd   
> |Day of month|dd
> |Day of week|d   
> |Name of month|month
> |Abr name of month|mon
> |Full era name|ee
> |Name of day|dy   
> |Time zone|tz   
> |Hour 12 |hh   
> |Hour 12 |hh12   
> |Hour 24|hh24
> |Minute of hour|mi  
> |Second of minute|ss   
> |Millisecond of minute|ms
> |Week of year|ww   
> |Month|mm   
> |Halfday am|am
> |Year   |   y   
> |ref.|
> https://www.postgresql.org/docs/8.2/static/functions-formatting.html   |
> Table of acceptable Postgres pattern modifiers, which may be used in Format 
> string
> ||Description||Pattern||
> |fill mode (suppress padding blanks and zeroes)|fm |
> |fixed format global option (see usage notes)|fx |
> |translation mode (print localized day and month names based on 
> lc_messages)|tm |
> |spell mode (not yet implemented)|sp|
> |ref.|
> https://www.postgresql.org/docs/8.2/static/functions-formatting.html|



--
This message was sent by Atlassian JIRA
(v6.3.15#6346)

[jira] [Commented] (DRILL-4864) Add ANSI format for date/time functions

2017-02-10 Thread ASF GitHub Bot (JIRA)


[ 
https://issues.apache.org/jira/browse/DRILL-4864?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15862164#comment-15862164
 ] 

ASF GitHub Bot commented on DRILL-4864:
---

Github user asfgit closed the pull request at:

https://github.com/apache/drill/pull/581


> Add ANSI format for date/time functions
> ---
>
> Key: DRILL-4864
> URL: https://issues.apache.org/jira/browse/DRILL-4864
> Project: Apache Drill
>  Issue Type: Improvement
>Affects Versions: 1.8.0
>Reporter: Serhii Harnyk
>Assignee: Serhii Harnyk
>  Labels: doc-impacting, ready-to-commit
> Fix For: 1.10
>
>
> The TO_DATE() is exposing the Joda string formatting conventions into the SQL 
> layer. This is not following SQL conventions used by ANSI and many other 
> database engines on the market.
> Add new UDFs: 
> * sql_to_date(String, Format), 
> * sql_to_time(String, Format), 
> * sql_to_timestamp(String, Format)
> that requires Postgres datetime format.
> Table of supported Postgres patterns
> ||Pattern name||Postgres format   
> |Full name of day|day   
> |Day of year|ddd   
> |Day of month|dd
> |Day of week|d   
> |Name of month|month
> |Abr name of month|mon
> |Full era name|ee
> |Name of day|dy   
> |Time zone|tz   
> |Hour 12 |hh   
> |Hour 12 |hh12   
> |Hour 24|hh24
> |Minute of hour|mi  
> |Second of minute|ss   
> |Millisecond of minute|ms
> |Week of year|ww   
> |Month|mm   
> |Halfday am|am
> |Year   |   y   
> |ref.|
> https://www.postgresql.org/docs/8.2/static/functions-formatting.html   |
> Table of acceptable Postgres pattern modifiers, which may be used in Format 
> string
> ||Description||Pattern||
> |fill mode (suppress padding blanks and zeroes)|fm |
> |fixed format global option (see usage notes)|fx |
> |translation mode (print localized day and month names based on 
> lc_messages)|tm |
> |spell mode (not yet implemented)|sp|
> |ref.|
> https://www.postgresql.org/docs/8.2/static/functions-formatting.html|



--
This message was sent by Atlassian JIRA
(v6.3.15#6346)

[jira] [Commented] (DRILL-4864) Add ANSI format for date/time functions

2017-02-10 Thread ASF GitHub Bot (JIRA)


[ 
https://issues.apache.org/jira/browse/DRILL-4864?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15862160#comment-15862160
 ] 

ASF GitHub Bot commented on DRILL-4864:
---

Github user amansinha100 commented on the issue:

https://github.com/apache/drill/pull/581
  
+1


> Add ANSI format for date/time functions
> ---
>
> Key: DRILL-4864
> URL: https://issues.apache.org/jira/browse/DRILL-4864
> Project: Apache Drill
>  Issue Type: Improvement
>Affects Versions: 1.8.0
>Reporter: Serhii Harnyk
>Assignee: Serhii Harnyk
>  Labels: doc-impacting, ready-to-commit
> Fix For: 1.10
>
>
> The TO_DATE() is exposing the Joda string formatting conventions into the SQL 
> layer. This is not following SQL conventions used by ANSI and many other 
> database engines on the market.
> Add new UDFs: 
> * sql_to_date(String, Format), 
> * sql_to_time(String, Format), 
> * sql_to_timestamp(String, Format)
> that requires Postgres datetime format.
> Table of supported Postgres patterns
> ||Pattern name||Postgres format   
> |Full name of day|day   
> |Day of year|ddd   
> |Day of month|dd
> |Day of week|d   
> |Name of month|month
> |Abr name of month|mon
> |Full era name|ee
> |Name of day|dy   
> |Time zone|tz   
> |Hour 12 |hh   
> |Hour 12 |hh12   
> |Hour 24|hh24
> |Minute of hour|mi  
> |Second of minute|ss   
> |Millisecond of minute|ms
> |Week of year|ww   
> |Month|mm   
> |Halfday am|am
> |Year   |   y   
> |ref.|
> https://www.postgresql.org/docs/8.2/static/functions-formatting.html   |
> Table of acceptable Postgres pattern modifiers, which may be used in Format 
> string
> ||Description||Pattern||
> |fill mode (suppress padding blanks and zeroes)|fm |
> |fixed format global option (see usage notes)|fx |
> |translation mode (print localized day and month names based on 
> lc_messages)|tm |
> |spell mode (not yet implemented)|sp|
> |ref.|
> https://www.postgresql.org/docs/8.2/static/functions-formatting.html|



--
This message was sent by Atlassian JIRA
(v6.3.15#6346)

[jira] [Commented] (DRILL-4864) Add ANSI format for date/time functions

2017-02-03 Thread ASF GitHub Bot (JIRA)


[ 
https://issues.apache.org/jira/browse/DRILL-4864?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15851361#comment-15851361
 ] 

ASF GitHub Bot commented on DRILL-4864:
---

Github user Serhii-Harnyk commented on the issue:

https://github.com/apache/drill/pull/581
  
@paul-rogers could you please review new changes?


> Add ANSI format for date/time functions
> ---
>
> Key: DRILL-4864
> URL: https://issues.apache.org/jira/browse/DRILL-4864
> Project: Apache Drill
>  Issue Type: Improvement
>Affects Versions: 1.8.0
>Reporter: Serhii Harnyk
>Assignee: Serhii Harnyk
>  Labels: doc-impacting
> Fix For: Future
>
>
> The TO_DATE() is exposing the Joda string formatting conventions into the SQL 
> layer. This is not following SQL conventions used by ANSI and many other 
> database engines on the market.
> Add new UDFs: 
> * sql_to_date(String, Format), 
> * sql_to_time(String, Format), 
> * sql_to_timestamp(String, Format)
> that requires Postgres datetime format.
> Table of supported Postgres patterns
> ||Pattern name||Postgres format   
> |Full name of day|day   
> |Day of year|ddd   
> |Day of month|dd
> |Day of week|d   
> |Name of month|month
> |Abr name of month|mon
> |Full era name|ee
> |Name of day|dy   
> |Time zone|tz   
> |Hour 12 |hh   
> |Hour 12 |hh12   
> |Hour 24|hh24
> |Minute of hour|mi  
> |Second of minute|ss   
> |Millisecond of minute|ms
> |Week of year|ww   
> |Month|mm   
> |Halfday am|am
> |Year   |   y   
> |ref.|
> https://www.postgresql.org/docs/8.2/static/functions-formatting.html   |
> Table of acceptable Postgres pattern modifiers, which may be used in Format 
> string
> ||Description||Pattern||
> |fill mode (suppress padding blanks and zeroes)|fm |
> |fixed format global option (see usage notes)|fx |
> |translation mode (print localized day and month names based on 
> lc_messages)|tm |
> |spell mode (not yet implemented)|sp|
> |ref.|
> https://www.postgresql.org/docs/8.2/static/functions-formatting.html|



--
This message was sent by Atlassian JIRA
(v6.3.15#6346)

[jira] [Commented] (DRILL-4864) Add ANSI format for date/time functions

2017-02-03 Thread ASF GitHub Bot (JIRA)


[ 
https://issues.apache.org/jira/browse/DRILL-4864?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15851349#comment-15851349
 ] 

ASF GitHub Bot commented on DRILL-4864:
---

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


> Add ANSI format for date/time functions
> ---
>
> Key: DRILL-4864
> URL: https://issues.apache.org/jira/browse/DRILL-4864
> Project: Apache Drill
>  Issue Type: Improvement
>Affects Versions: 1.8.0
>Reporter: Serhii Harnyk
>Assignee: Serhii Harnyk
>  Labels: doc-impacting
> Fix For: Future
>
>
> The TO_DATE() is exposing the Joda string formatting conventions into the SQL 
> layer. This is not following SQL conventions used by ANSI and many other 
> database engines on the market.
> Add new UDFs: 
> * sql_to_date(String, Format), 
> * sql_to_time(String, Format), 
> * sql_to_timestamp(String, Format)
> that requires Postgres datetime format.
> Table of supported Postgres patterns
> ||Pattern name||Postgres format   
> |Full name of day|day   
> |Day of year|ddd   
> |Day of month|dd
> |Day of week|d   
> |Name of month|month
> |Abr name of month|mon
> |Full era name|ee
> |Name of day|dy   
> |Time zone|tz   
> |Hour 12 |hh   
> |Hour 12 |hh12   
> |Hour 24|hh24
> |Minute of hour|mi  
> |Second of minute|ss   
> |Millisecond of minute|ms
> |Week of year|ww   
> |Month|mm   
> |Halfday am|am
> |Year   |   y   
> |ref.|
> https://www.postgresql.org/docs/8.2/static/functions-formatting.html   |
> Table of acceptable Postgres pattern modifiers, which may be used in Format 
> string
> ||Description||Pattern||
> |fill mode (suppress padding blanks and zeroes)|fm |
> |fixed format global option (see usage notes)|fx |
> |translation mode (print localized day and month names based on 
> lc_messages)|tm |
> |spell mode (not yet implemented)|sp|
> |ref.|
> https://www.postgresql.org/docs/8.2/static/functions-formatting.html|



--
This message was sent by Atlassian JIRA
(v6.3.15#6346)

[jira] [Commented] (DRILL-4864) Add ANSI format for date/time functions

2017-02-03 Thread ASF GitHub Bot (JIRA)


[ 
https://issues.apache.org/jira/browse/DRILL-4864?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15851345#comment-15851345
 ] 

ASF GitHub Bot commented on DRILL-4864:
---

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 postgresValuesForDeleting = 
Sets.newTreeSet(new LengthDescComparator());
+  private static final CaseInsensitiveMap 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 = "";
+  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 = "";
+  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_MONT

[jira] [Commented] (DRILL-4864) Add ANSI format for date/time functions

2017-02-03 Thread ASF GitHub Bot (JIRA)


[ 
https://issues.apache.org/jira/browse/DRILL-4864?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15851348#comment-15851348
 ] 

ASF GitHub Bot commented on DRILL-4864:
---

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 postgresValuesForDeleting = 
Sets.newTreeSet(new LengthDescComparator());
+  private static final CaseInsensitiveMap 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 = "";
+  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 = "";
+  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_MONT

[jira] [Commented] (DRILL-4864) Add ANSI format for date/time functions

2017-02-03 Thread ASF GitHub Bot (JIRA)


[ 
https://issues.apache.org/jira/browse/DRILL-4864?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15851347#comment-15851347
 ] 

ASF GitHub Bot commented on DRILL-4864:
---

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 postgresValuesForDeleting = 
Sets.newTreeSet(new LengthDescComparator());
+  private static final CaseInsensitiveMap 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 = "";
+  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 = "";
+  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_MONT

[jira] [Commented] (DRILL-4864) Add ANSI format for date/time functions

2017-02-03 Thread ASF GitHub Bot (JIRA)


[ 
https://issues.apache.org/jira/browse/DRILL-4864?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15851346#comment-15851346
 ] 

ASF GitHub Bot commented on DRILL-4864:
---

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 postgresValuesForDeleting = 
Sets.newTreeSet(new LengthDescComparator());
+  private static final CaseInsensitiveMap 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 = "";
+  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 = "";
+  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_MONT

[jira] [Commented] (DRILL-4864) Add ANSI format for date/time functions

2017-02-03 Thread ASF GitHub Bot (JIRA)


[ 
https://issues.apache.org/jira/browse/DRILL-4864?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15851319#comment-15851319
 ] 

ASF GitHub Bot commented on DRILL-4864:
---

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());
+
--- End diff --

Yes, we should. Fixed, thanks.


> Add ANSI format for date/time functions
> ---
>
> Key: DRILL-4864
> URL: https://issues.apache.org/jira/browse/DRILL-4864
> Project: Apache Drill
>  Issue Type: Improvement
>Affects Versions: 1.8.0
>Reporter: Serhii Harnyk
>Assignee: Serhii Harnyk
>  Labels: doc-impacting
> Fix For: Future
>
>
> The TO_DATE() is exposing the Joda string formatting conventions into the SQL 
> layer. This is not following SQL conventions used by ANSI and many other 
> database engines on the market.
> Add new UDFs: 
> * sql_to_date(String, Format), 
> * sql_to_time(String, Format), 
> * sql_to_timestamp(String, Format)
> that requires Postgres datetime format.
> Table of supported Po

[jira] [Commented] (DRILL-4864) Add ANSI format for date/time functions

2017-02-03 Thread ASF GitHub Bot (JIRA)


[ 
https://issues.apache.org/jira/browse/DRILL-4864?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15851320#comment-15851320
 ] 

ASF GitHub Bot commented on DRILL-4864:
---

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


> Add ANSI format for date/time functions
> ---
>
> Key: DRILL-4864
> URL: https://issues.apache.org/jira/browse/DRILL-4864
> Project: Apache Drill
>  Issue Type: Improvement
>Affects Versions: 1.8.0
>Reporter: Serhii Harnyk
>Assignee: Serhii Harnyk
>  Labels: doc-impacting
> Fix For: Future
>
>
> The TO_DATE() is exposing the Joda string formatting conventions into the SQL 
> layer. This is not following SQL conventions used by ANSI and many other 
> database engines on the market.
> Add new UDFs: 
> * sql_to_date(String, Format), 
> * sql_to_time(String, Format), 
> * sql_to_timestamp(String, Format)
> that requires Postgres datetime format.
> Table of supported Postgres patterns
> ||Pattern name||Postgres format   
> |Full name of day|day   
> |Day of year|ddd   
> |Day of month|dd
> |Day of week|d   
> |Name of month|month
> |Abr name of month|mon
> |Full era name|ee
> |Name of day|dy   
> |Time zone|tz   
> |Hour 12 |hh   
> |Hour 12 |hh12   
> |Hour 24|hh24
> |Minute of hour|mi  
> |Second of minute|ss   
> |Millisecond of minute|ms
> |Week of year|ww   
> |Month|mm   
> |Halfday am|am
> |Year   |   y   
> |ref.|
> https://www.postgresql.org/docs/8.2/static/functions-formatting.html   |
> Table of acceptable Postgres pattern modifiers, which may be used in Format 
> string
> ||Description||Pattern||
> |fill mode (suppress padding blanks and zeroes)|fm |
> |fixed format global option (see usage notes)|fx |
> |translation mode (print localized day and month names based on 
> lc_messages)|tm |
> |spell mode (not yet implemented)|sp|
> |ref.|
> https://www.postgresql.org/docs/8.2/static/functions-formatting.html|



--
This message was sent by Atlassian JIRA
(v6.3.15#6346)

[jira] [Commented] (DRILL-4864) Add ANSI format for date/time functions

2017-02-03 Thread ASF GitHub Bot (JIRA)


[ 
https://issues.apache.org/jira/browse/DRILL-4864?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15851316#comment-15851316
 ] 

ASF GitHub Bot commented on DRILL-4864:
---

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.


> Add ANSI format for date/time functions
> ---
>
> Key: DRILL-4864
> URL: https://issues.apache.org/jira/browse/DRILL-4864
> Project: Apache Drill
>  Issue Type: Improvement
>Affects Versions: 1.8.0
>Reporter: Serhii Harnyk
>Assignee: Serhii Harnyk
>  Labels: doc-impacting
> Fix For: Future
>
>
> The TO_DATE() is exposing the Joda string formatting conventions into the SQL 
> layer. This is not following SQL conventions used by ANSI and many other 
> database engines on the market.
> Add new UDFs: 
> * sql_to_date(String, Format), 
> * sql_to_time(String, Format), 
> * sql_to_timestamp(String, Format)
> that requires Postgres datetime format.
> Table of supported Postgres patterns
> ||Pattern name||Postgres format   
> |Full name of day|day   
> |Day of year|ddd   
> |Day of month|dd
> |Day of week|d   
> |Name of month|month
> |Abr name of month|mon
> |Full era name|ee
> |Name of day|dy   
> |Time zone|tz   
> |Hour 12 |hh   
> |Hour 12 |hh12   
> |Hour 24|hh24
> |Minute of hour|mi  
> |Second of minute|ss   
> |Millisecond of minute|ms
> |Week of year|ww   
> |Month|mm   
> |Halfday am|am
> |Year   |   y   
> |ref.|
> https://www

[jira] [Commented] (DRILL-4864) Add ANSI format for date/time functions

2017-02-03 Thread ASF GitHub Bot (JIRA)


[ 
https://issues.apache.org/jira/browse/DRILL-4864?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15851309#comment-15851309
 ] 

ASF GitHub Bot commented on DRILL-4864:
---

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.


> Add ANSI format for date/time functions
> ---
>
> Key: DRILL-4864
> URL: https://issues.apache.org/jira/browse/DRILL-4864
> Project: Apache Drill
>  Issue Type: Improvement
>Affects Versions: 1.8.0
>Reporter: Serhii Harnyk
>Assignee: Serhii Harnyk
>  Labels: doc-impacting
> Fix For: Future
>
>
> The TO_DATE() is exposing the Joda string formatting conventions into the SQL 
> layer. This is not following SQL conventions used by ANSI and many other 
> database engines on the market.
> Add new UDFs: 
> * sql_to_date(String, Format), 
> * sql_to_time(String, Format), 
> * sql_to_timestamp(String, Format)
> that requires Postgres datetime format.
> Table of supported Postgres patterns
> ||Pattern name||Postgres format   
> |Full name of day|day   
> |Day of year|ddd   
> |Day of month|dd
> |Day of week|d   
> |Name of month|month
> |Abr name of month|mon
> |Full era name|ee
> |Name of day|dy   
> |Time zone|tz   
> |Hour 12 |hh   
> |Hour 12 |hh12   
> |Hour 24|hh24
> |Minute of hour|mi  
> |Second of minute|ss   
> |Millisecond of minute|ms
> |Week of year|ww   
> |Month|mm   
> |Halfday am|am
> |Year   |   y   
> |ref.|
> https://www.postgresql.org/docs/8.2/static/functions-formatting.html   |
> Table of acceptable Postgres pattern modifiers, which may be used in Format 
> string
> ||Description||Pattern||
> |fill mode (suppress padding blanks and zeroes)|fm |
> |fixed

[jira] [Commented] (DRILL-4864) Add ANSI format for date/time functions

2016-12-16 Thread ASF GitHub Bot (JIRA)


[ 
https://issues.apache.org/jira/browse/DRILL-4864?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15754233#comment-15754233
 ] 

ASF GitHub Bot commented on DRILL-4864:
---

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?


> Add ANSI format for date/time functions
> ---
>
> Key: DRILL-4864
> URL: https://issues.apache.org/jira/browse/DRILL-4864
> Project: Apache Drill
>  Issue Type: Improvement
>Affects Versions: 1.8.0
>Reporter: Serhii Harnyk
>Assignee: Serhii Harnyk
>  Labels: doc-impacting
> Fix For: Future
>
>
> The TO_DATE() is exposing the Joda string formatting conventions into the SQL 
> layer. This is not following SQL conventions used by ANSI and many other 
> database engines on the market.
> Add new UDFs: 
> * sql_to_date(String, Format), 
> * sql_to_time(String, Format), 
> * sql_to_timestamp(String, Format)
> that requires Postgres datetime format.
> Table of supported Postgres patterns
> ||Pattern name||Postgres format   
> |Full name of day|day   
> |Day of year|ddd   
> |Day of month|dd
> |Day of week|d   
> |Name of month|month
> |Abr name of month|mon
> |Full era name|ee
> |Name of day|dy   
> |Time zone|tz   
> |Hour 12 |hh   
> |Hour 12 |hh12   
> |Hour 24|hh24
> |Minute of hour|mi  
> |Second of minute|ss   
> |Millisecond of minute|ms
> |Week of year|ww   
> |Month|mm   
> |Halfday am|am
> |Year   |   y   
> |ref.|
> https://www.postgresql.org/docs/8.2/static/functions-formatting.html   |
> Table of acceptable Postgres pattern modifiers, which may be used in Format 
> string
> ||Description||Pattern||
> |fill mode (suppress padding blanks and zeroes)|fm |
> |fixed format global option (see usage notes)|fx |
> |translation mode (print localized day and month names based on 
> lc_messages)|tm |
> |spell mode (not yet implemented)|sp|
> |ref.|
> https://www.postgresql.org/docs/8.2/static/functions-formatting.html|



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)

[jira] [Commented] (DRILL-4864) Add ANSI format for date/time functions

2016-11-03 Thread ASF GitHub Bot (JIRA)


[ 
https://issues.apache.org/jira/browse/DRILL-4864?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15633854#comment-15633854
 ] 

ASF GitHub Bot commented on DRILL-4864:
---

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.


> Add ANSI format for date/time functions
> ---
>
> Key: DRILL-4864
> URL: https://issues.apache.org/jira/browse/DRILL-4864
> Project: Apache Drill
>  Issue Type: Improvement
>Affects Versions: 1.8.0
>Reporter: Serhii Harnyk
>Assignee: Serhii Harnyk
>  Labels: doc-impacting
> Fix For: 1.9.0
>
>
> The TO_DATE() is exposing the Joda string formatting conventions into the SQL 
> layer. This is not following SQL conventions used by ANSI and many other 
> database engines on the market.
> Add new UDF "ansi_to_joda(string)", that takes string that represents ANSI 
> datetime format and returns string that represents equal Joda format.
> Add new session option "drill.exec.fn.to_date_format" that can be one of two 
> values - "JODA"(default) and "ANSI".
> If option is set to "JODA" queries with to_date() function would work in 
> usual way.
> If option is set to "ANSI" second argument would be wrapped with 
> ansi_to_joda() function, that allows user to use ANSI datetime format
> Wrapping is used in to_date(), to_time() and to_timestamp() functions.
> Table of joda and ansi patterns which may be replaced
> ||Pattern name||  Ansi format ||  JodaTime format
> | Full name of day|   day |   
> | Day of year |   ddd |   D
> | Day of month|   dd  |   d
> | Day of week |   d   |   e
> | Name of month   |   month   |   
> | Abr name of month   |   mon |   MMM
> | Full era name   |   ee  |   G
> | Name of day |   dy  |   E
> | Time zone   |   tz  |   TZ
> | Hour 12 |   hh  |   h
> | Hour 12 |   hh12|   h
> | Hour 24 |   hh24|   H
> | Minute of hour  |   mi  |   m
> | Second of minute|   ss  |   s
> | Millisecond of minute   |   ms  |   S
> | Week of year|   ww  |   w
> | Month   |   mm  |   MM
> | Halfday am  |   am  |   aa
> | Halfday pm  |   pm  |   aa
> | ref.|   
> https://www.postgresql.org/docs/8.2/static/functions-formatting.html| 
>   
> http://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html
>  |
> Table of ansi pattern modifiers, which may be deleted from string
> ||Description ||  Pattern ||
> | fill mode (suppress padding blanks and zeroes)  |   fm  |
> | fixed format global option (see usage notes)|   fx  |
> | translation mode (print localized day and month names based on 
> lc_messages) |   tm  |
> | spell mode (not yet implemented)|   sp  |
> | ref.|   
> https://www.postgresql.org/docs/8.2/static/functions-formatting.html|



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)

[jira] [Commented] (DRILL-4864) Add ANSI format for date/time functions

2016-11-03 Thread Paul Rogers (JIRA)


[ 
https://issues.apache.org/jira/browse/DRILL-4864?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15633845#comment-15633845
 ] 

Paul Rogers commented on DRILL-4864:


I wonder about the approach taken here. First, it is not clear that there is a 
standard "ANSI" date format... Consider Oracle's format 
(https://www.techonthenet.com/oracle/functions/to_date.php, IBM's DB2 format 
(http://www.ibm.com/support/knowledgecenter/SSEPEK_10.0.0/sqlref/src/tpc/db2z_bif_timestampformat.html)
 or the Postgres format 
(https://www.postgresql.org/docs/8.2/static/functions-formatting.html).

Second, changing the format of an existing function will break existing queries.

Third, working around that problem by having a global option is awkward: this 
means that all users of a Drill cluster must agree on date format. Further, any 
stored queries that use the "Joda" format would have to be rewritten when the 
setting changes. Such a change can thus never be made for existing 
installations. To prevent data corruption (queries that no longer work) new 
installs must be backward compatible and use the old formats. So, only new 
installs that know how to set the option could use the "ANSI" formats.

Fourth, the "ANSI" name seems to refer to a specific format (ISO 8601: 
https://en.wikipedia.org/wiki/ISO_8601). I can't find references to an ANSI 
standard for the format strings. Instead, it seems that there is an Oracle 
standard, an IBM standard, etc.

Is there a better way?

Two possibilities.

First, leave the to_date, etc. functions unchanged. They use the Java (formerly 
Joda) formats. (Note, Joda is deprecated. Java 8 includes the former Joda 
classes in the JDK. Java 7 was the last version that needed Joda, but Java 7 
went end-of-life in April, 2015.)

Introduce a new function that takes a new format. Maybe call it sql_to_date, 
then pick one of the Oracle/DB2/Postgress/etc. dialects to support.

The second option is modify to_date to take a prefix:

"java:M/D/y" or
"sql:MM/DD/YY"

The default would be "java:" for backward compatibility.

The sql_to_date approach is probably cleaner.

In either case, we have to define the new date/time format strings (or claim we 
support some vendor's format.) It appears that the code supports the Postgres 
format. How does that differ from the Oracle format?

> Add ANSI format for date/time functions
> ---
>
> Key: DRILL-4864
> URL: https://issues.apache.org/jira/browse/DRILL-4864
> Project: Apache Drill
>  Issue Type: Improvement
>Affects Versions: 1.8.0
>Reporter: Serhii Harnyk
>Assignee: Serhii Harnyk
>  Labels: doc-impacting
> Fix For: 1.9.0
>
>
> The TO_DATE() is exposing the Joda string formatting conventions into the SQL 
> layer. This is not following SQL conventions used by ANSI and many other 
> database engines on the market.
> Add new UDF "ansi_to_joda(string)", that takes string that represents ANSI 
> datetime format and returns string that represents equal Joda format.
> Add new session option "drill.exec.fn.to_date_format" that can be one of two 
> values - "JODA"(default) and "ANSI".
> If option is set to "JODA" queries with to_date() function would work in 
> usual way.
> If option is set to "ANSI" second argument would be wrapped with 
> ansi_to_joda() function, that allows user to use ANSI datetime format
> Wrapping is used in to_date(), to_time() and to_timestamp() functions.
> Table of joda and ansi patterns which may be replaced
> ||Pattern name||  Ansi format ||  JodaTime format
> | Full name of day|   day |   
> | Day of year |   ddd |   D
> | Day of month|   dd  |   d
> | Day of week |   d   |   e
> | Name of month   |   month   |   
> | Abr name of month   |   mon |   MMM
> | Full era name   |   ee  |   G
> | Name of day |   dy  |   E
> | Time zone   |   tz  |   TZ
> | Hour 12 |   hh  |   h
> | Hour 12 |   hh12|   h
> | Hour 24 |   hh24|   H
> | Minute of hour  |   mi  |   m
> | Second of minute|   ss  |   s
> | Millisecond of minute   |   ms  |   S
> | Week of year|   ww  |   w
> | Month   |   mm  |   MM
> | Halfday am  |   am  |   aa
> | Halfday pm  |   pm  |   aa
> | ref.|   
> https://www.postgresql.org/docs/8.2/static/functions-formatting.html| 
>   
> http://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html
>  |
> Table of ansi pattern modifiers, which may be deleted from string
> ||Description |

[jira] [Commented] (DRILL-4864) Add ANSI format for date/time functions

2016-11-03 Thread ASF GitHub Bot (JIRA)


[ 
https://issues.apache.org/jira/browse/DRILL-4864?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15633293#comment-15633293
 ] 

ASF GitHub Bot commented on DRILL-4864:
---

Github user adeneche commented on the issue:

https://github.com/apache/drill/pull/581
  
+1 LGTM, 

sorry for taking too long


> Add ANSI format for date/time functions
> ---
>
> Key: DRILL-4864
> URL: https://issues.apache.org/jira/browse/DRILL-4864
> Project: Apache Drill
>  Issue Type: Improvement
>Affects Versions: 1.8.0
>Reporter: Serhii Harnyk
>Assignee: Serhii Harnyk
>  Labels: doc-impacting
> Fix For: 1.9.0
>
>
> The TO_DATE() is exposing the Joda string formatting conventions into the SQL 
> layer. This is not following SQL conventions used by ANSI and many other 
> database engines on the market.
> Add new UDF "ansi_to_joda(string)", that takes string that represents ANSI 
> datetime format and returns string that represents equal Joda format.
> Add new session option "drill.exec.fn.to_date_format" that can be one of two 
> values - "JODA"(default) and "ANSI".
> If option is set to "JODA" queries with to_date() function would work in 
> usual way.
> If option is set to "ANSI" second argument would be wrapped with 
> ansi_to_joda() function, that allows user to use ANSI datetime format
> Wrapping is used in to_date(), to_time() and to_timestamp() functions.
> Table of joda and ansi patterns which may be replaced
> ||Pattern name||  Ansi format ||  JodaTime format
> | Full name of day|   day |   
> | Day of year |   ddd |   D
> | Day of month|   dd  |   d
> | Day of week |   d   |   e
> | Name of month   |   month   |   
> | Abr name of month   |   mon |   MMM
> | Full era name   |   ee  |   G
> | Name of day |   dy  |   E
> | Time zone   |   tz  |   TZ
> | Hour 12 |   hh  |   h
> | Hour 12 |   hh12|   h
> | Hour 24 |   hh24|   H
> | Minute of hour  |   mi  |   m
> | Second of minute|   ss  |   s
> | Millisecond of minute   |   ms  |   S
> | Week of year|   ww  |   w
> | Month   |   mm  |   MM
> | Halfday am  |   am  |   aa
> | Halfday pm  |   pm  |   aa
> | ref.|   
> https://www.postgresql.org/docs/8.2/static/functions-formatting.html| 
>   
> http://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html
>  |
> Table of ansi pattern modifiers, which may be deleted from string
> ||Description ||  Pattern ||
> | fill mode (suppress padding blanks and zeroes)  |   fm  |
> | fixed format global option (see usage notes)|   fx  |
> | translation mode (print localized day and month names based on 
> lc_messages) |   tm  |
> | spell mode (not yet implemented)|   sp  |
> | ref.|   
> https://www.postgresql.org/docs/8.2/static/functions-formatting.html|



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)

[jira] [Commented] (DRILL-4864) Add ANSI format for date/time functions

2016-11-01 Thread ASF GitHub Bot (JIRA)


[ 
https://issues.apache.org/jira/browse/DRILL-4864?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15626059#comment-15626059
 ] 

ASF GitHub Bot commented on DRILL-4864:
---

Github user Serhii-Harnyk commented on the issue:

https://github.com/apache/drill/pull/581
  
@adeneche 
What about fixed milliseconds?


> Add ANSI format for date/time functions
> ---
>
> Key: DRILL-4864
> URL: https://issues.apache.org/jira/browse/DRILL-4864
> Project: Apache Drill
>  Issue Type: Improvement
>Affects Versions: 1.8.0
>Reporter: Serhii Harnyk
>Assignee: Serhii Harnyk
>  Labels: doc-impacting
> Fix For: 1.9.0
>
>
> The TO_DATE() is exposing the Joda string formatting conventions into the SQL 
> layer. This is not following SQL conventions used by ANSI and many other 
> database engines on the market.
> Add new UDF "ansi_to_joda(string)", that takes string that represents ANSI 
> datetime format and returns string that represents equal Joda format.
> Add new session option "drill.exec.fn.to_date_format" that can be one of two 
> values - "JODA"(default) and "ANSI".
> If option is set to "JODA" queries with to_date() function would work in 
> usual way.
> If option is set to "ANSI" second argument would be wrapped with 
> ansi_to_joda() function, that allows user to use ANSI datetime format
> Wrapping is used in to_date(), to_time() and to_timestamp() functions.
> Table of joda and ansi patterns which may be replaced
> ||Pattern name||  Ansi format ||  JodaTime format
> | Full name of day|   day |   
> | Day of year |   ddd |   D
> | Day of month|   dd  |   d
> | Day of week |   d   |   e
> | Name of month   |   month   |   
> | Abr name of month   |   mon |   MMM
> | Full era name   |   ee  |   G
> | Name of day |   dy  |   E
> | Time zone   |   tz  |   TZ
> | Hour 12 |   hh  |   h
> | Hour 12 |   hh12|   h
> | Hour 24 |   hh24|   H
> | Minute of hour  |   mi  |   m
> | Second of minute|   ss  |   s
> | Millisecond of minute   |   ms  |   S
> | Week of year|   ww  |   w
> | Month   |   mm  |   MM
> | Halfday am  |   am  |   aa
> | Halfday pm  |   pm  |   aa
> | ref.|   
> https://www.postgresql.org/docs/8.2/static/functions-formatting.html| 
>   
> http://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html
>  |
> Table of ansi pattern modifiers, which may be deleted from string
> ||Description ||  Pattern ||
> | fill mode (suppress padding blanks and zeroes)  |   fm  |
> | fixed format global option (see usage notes)|   fx  |
> | translation mode (print localized day and month names based on 
> lc_messages) |   tm  |
> | spell mode (not yet implemented)|   sp  |
> | ref.|   
> https://www.postgresql.org/docs/8.2/static/functions-formatting.html|



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)

[jira] [Commented] (DRILL-4864) Add ANSI format for date/time functions

2016-10-31 Thread Gautam Kumar Parai (JIRA)


[ 
https://issues.apache.org/jira/browse/DRILL-4864?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15622571#comment-15622571
 ] 

Gautam Kumar Parai commented on DRILL-4864:
---

Sorry, I forgot to reassign it back to the developer. I approved the pull 
request earlier. [~sharnyk] Please commit unless [~adeneche] has further 
comments? 

> Add ANSI format for date/time functions
> ---
>
> Key: DRILL-4864
> URL: https://issues.apache.org/jira/browse/DRILL-4864
> Project: Apache Drill
>  Issue Type: Improvement
>Affects Versions: 1.8.0
>Reporter: Serhii Harnyk
>Assignee: Serhii Harnyk
>  Labels: doc-impacting
> Fix For: 1.9.0
>
>
> The TO_DATE() is exposing the Joda string formatting conventions into the SQL 
> layer. This is not following SQL conventions used by ANSI and many other 
> database engines on the market.
> Add new UDF "ansi_to_joda(string)", that takes string that represents ANSI 
> datetime format and returns string that represents equal Joda format.
> Add new session option "drill.exec.fn.to_date_format" that can be one of two 
> values - "JODA"(default) and "ANSI".
> If option is set to "JODA" queries with to_date() function would work in 
> usual way.
> If option is set to "ANSI" second argument would be wrapped with 
> ansi_to_joda() function, that allows user to use ANSI datetime format
> Wrapping is used in to_date(), to_time() and to_timestamp() functions.
> Table of joda and ansi patterns which may be replaced
> ||Pattern name||  Ansi format ||  JodaTime format
> | Full name of day|   day |   
> | Day of year |   ddd |   D
> | Day of month|   dd  |   d
> | Day of week |   d   |   e
> | Name of month   |   month   |   
> | Abr name of month   |   mon |   MMM
> | Full era name   |   ee  |   G
> | Name of day |   dy  |   E
> | Time zone   |   tz  |   TZ
> | Hour 12 |   hh  |   h
> | Hour 12 |   hh12|   h
> | Hour 24 |   hh24|   H
> | Minute of hour  |   mi  |   m
> | Second of minute|   ss  |   s
> | Millisecond of minute   |   ms  |   S
> | Week of year|   ww  |   w
> | Month   |   mm  |   MM
> | Halfday am  |   am  |   aa
> | Halfday pm  |   pm  |   aa
> | ref.|   
> https://www.postgresql.org/docs/8.2/static/functions-formatting.html| 
>   
> http://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html
>  |
> Table of ansi pattern modifiers, which may be deleted from string
> ||Description ||  Pattern ||
> | fill mode (suppress padding blanks and zeroes)  |   fm  |
> | fixed format global option (see usage notes)|   fx  |
> | translation mode (print localized day and month names based on 
> lc_messages) |   tm  |
> | spell mode (not yet implemented)|   sp  |
> | ref.|   
> https://www.postgresql.org/docs/8.2/static/functions-formatting.html|



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)

[jira] [Commented] (DRILL-4864) Add ANSI format for date/time functions

2016-10-31 Thread Zelaine Fong (JIRA)


[ 
https://issues.apache.org/jira/browse/DRILL-4864?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15622546#comment-15622546
 ] 

Zelaine Fong commented on DRILL-4864:
-

[~gparai], [~adeneche] - any further comments on the pull request for this Jira?

> Add ANSI format for date/time functions
> ---
>
> Key: DRILL-4864
> URL: https://issues.apache.org/jira/browse/DRILL-4864
> Project: Apache Drill
>  Issue Type: Improvement
>Affects Versions: 1.8.0
>Reporter: Serhii Harnyk
>Assignee: Gautam Kumar Parai
>  Labels: doc-impacting
> Fix For: 1.9.0
>
>
> The TO_DATE() is exposing the Joda string formatting conventions into the SQL 
> layer. This is not following SQL conventions used by ANSI and many other 
> database engines on the market.
> Add new UDF "ansi_to_joda(string)", that takes string that represents ANSI 
> datetime format and returns string that represents equal Joda format.
> Add new session option "drill.exec.fn.to_date_format" that can be one of two 
> values - "JODA"(default) and "ANSI".
> If option is set to "JODA" queries with to_date() function would work in 
> usual way.
> If option is set to "ANSI" second argument would be wrapped with 
> ansi_to_joda() function, that allows user to use ANSI datetime format
> Wrapping is used in to_date(), to_time() and to_timestamp() functions.
> Table of joda and ansi patterns which may be replaced
> ||Pattern name||  Ansi format ||  JodaTime format
> | Full name of day|   day |   
> | Day of year |   ddd |   D
> | Day of month|   dd  |   d
> | Day of week |   d   |   e
> | Name of month   |   month   |   
> | Abr name of month   |   mon |   MMM
> | Full era name   |   ee  |   G
> | Name of day |   dy  |   E
> | Time zone   |   tz  |   TZ
> | Hour 12 |   hh  |   h
> | Hour 12 |   hh12|   h
> | Hour 24 |   hh24|   H
> | Minute of hour  |   mi  |   m
> | Second of minute|   ss  |   s
> | Millisecond of minute   |   ms  |   S
> | Week of year|   ww  |   w
> | Month   |   mm  |   MM
> | Halfday am  |   am  |   aa
> | Halfday pm  |   pm  |   aa
> | ref.|   
> https://www.postgresql.org/docs/8.2/static/functions-formatting.html| 
>   
> http://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html
>  |
> Table of ansi pattern modifiers, which may be deleted from string
> ||Description ||  Pattern ||
> | fill mode (suppress padding blanks and zeroes)  |   fm  |
> | fixed format global option (see usage notes)|   fx  |
> | translation mode (print localized day and month names based on 
> lc_messages) |   tm  |
> | spell mode (not yet implemented)|   sp  |
> | ref.|   
> https://www.postgresql.org/docs/8.2/static/functions-formatting.html|



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)

[jira] [Commented] (DRILL-4864) Add ANSI format for date/time functions

2016-10-21 Thread ASF GitHub Bot (JIRA)


[ 
https://issues.apache.org/jira/browse/DRILL-4864?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15595348#comment-15595348
 ] 

ASF GitHub Bot commented on DRILL-4864:
---

Github user Serhii-Harnyk commented on the issue:

https://github.com/apache/drill/pull/581
  
@adeneche - miliseconds issue is fixed in last commit


> Add ANSI format for date/time functions
> ---
>
> Key: DRILL-4864
> URL: https://issues.apache.org/jira/browse/DRILL-4864
> Project: Apache Drill
>  Issue Type: Improvement
>Affects Versions: 1.8.0
>Reporter: Serhii Harnyk
>Assignee: Gautam Kumar Parai
>  Labels: doc-impacting
> Fix For: 1.9.0
>
>
> The TO_DATE() is exposing the Joda string formatting conventions into the SQL 
> layer. This is not following SQL conventions used by ANSI and many other 
> database engines on the market.
> Add new UDF "ansi_to_joda(string)", that takes string that represents ANSI 
> datetime format and returns string that represents equal Joda format.
> Add new session option "drill.exec.fn.to_date_format" that can be one of two 
> values - "JODA"(default) and "ANSI".
> If option is set to "JODA" queries with to_date() function would work in 
> usual way.
> If option is set to "ANSI" second argument would be wrapped with 
> ansi_to_joda() function, that allows user to use ANSI datetime format
> Wrapping is used in to_date(), to_time() and to_timestamp() functions.
> Table of joda and ansi patterns which may be replaced
> ||Pattern name||  Ansi format ||  JodaTime format
> | Full name of day|   day |   
> | Day of year |   ddd |   D
> | Day of month|   dd  |   d
> | Day of week |   d   |   e
> | Name of month   |   month   |   
> | Abr name of month   |   mon |   MMM
> | Full era name   |   ee  |   G
> | Name of day |   dy  |   E
> | Time zone   |   tz  |   TZ
> | Hour 12 |   hh  |   h
> | Hour 12 |   hh12|   h
> | Hour 24 |   hh24|   H
> | Minute of hour  |   mi  |   m
> | Second of minute|   ss  |   s
> | Millisecond of minute   |   ms  |   S
> | Week of year|   ww  |   w
> | Month   |   mm  |   MM
> | Halfday am  |   am  |   aa
> | Halfday pm  |   pm  |   aa
> | ref.|   
> https://www.postgresql.org/docs/8.2/static/functions-formatting.html| 
>   
> http://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html
>  |
> Table of ansi pattern modifiers, which may be deleted from string
> ||Description ||  Pattern ||
> | fill mode (suppress padding blanks and zeroes)  |   fm  |
> | fixed format global option (see usage notes)|   fx  |
> | translation mode (print localized day and month names based on 
> lc_messages) |   tm  |
> | spell mode (not yet implemented)|   sp  |
> | ref.|   
> https://www.postgresql.org/docs/8.2/static/functions-formatting.html|



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)

[jira] [Commented] (DRILL-4864) Add ANSI format for date/time functions

2016-10-18 Thread ASF GitHub Bot (JIRA)


[ 
https://issues.apache.org/jira/browse/DRILL-4864?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15587149#comment-15587149
 ] 

ASF GitHub Bot commented on DRILL-4864:
---

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', '-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


> Add ANSI format for date/time functions
> ---
>
> Key: DRILL-4864
> URL: https://issues.apache.org/jira/browse/DRILL-4864
> Project: Apache Drill
>  Issue Type: Improvement
>Affects Versions: 1.8.0
>Reporter: Serhii Harnyk
>Assignee: Gautam Kumar Parai
>  Labels: doc-impacting
> Fix For: 1.9.0
>
>
> The TO_DATE() is exposing the Joda string formatting conventions into the SQL 
> layer. This is not following SQL conventions used by ANSI and many other 
> database engines on the market.
> Add new UDF "ansi_to_joda(string)", that takes string that represents ANSI 
> datetime format and returns string that represents equal Joda format.
> Add new session option "drill.exec.fn.to_date_format" that can be one of two 
> values - "JODA"(default) and "ANSI".
> If option is set to "JODA" queries with to_date() function would work in 
> usual way.
> If option is set to "ANSI" second argument would be wrapped with 
> ansi_to_joda() function, that allows user to use ANSI datetime format
> Wrapping is used in to_date(), to_time() and to_timestamp() functions.
> Table of joda and ansi patterns which may be replaced
> ||Pattern name||  Ansi format ||  JodaTime format
> | Full name of day|   day |   
> | Day of year |   ddd |   D
> | Day of month|   dd  |   d
> | Day of week |   d   |   e
> | Name of month   |   month   |   
> | Abr name of month   |   mon |   MMM
> | Full era name   |   ee  |   G
> | Name of day |   dy  |   E
> | Time zone   |   tz  |   TZ
> | Hour 12 |   hh  |   h
> | Hour 12 |   hh12|   h
> | Hour 24 |   hh24|   H
> | Minute of hour  |   mi  |   m
> | Second of minute|   ss  |   s
> | Millisecond of minute   |   ms  |   S
> | Week of year|   ww  |   w
> | Month   |   mm  |   MM
> | Halfday am  |   am  |   aa
> | Halfday pm  |   pm  |   aa
> | ref.|   
> https://www.postgresql.org/docs/8.2/static/functions-formatting.html| 
>   
> http://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html
>  |
> Table of ansi pattern modifiers, which may be deleted from string
> ||Description ||  Pattern ||
> | fill mode (suppress padding blanks and zeroes)  |   fm  |
> | fixed format global option (see usage notes)|   fx  |
> | translation mode (print localized day and month names based on 
> lc_messages) |   tm  |
> | spell mode (not yet implemented)|   sp  |
> | ref.|   
> https://www.postgresql.org/docs/8.2/static/functions-formatting.html|



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)

[jira] [Commented] (DRILL-4864) Add ANSI format for date/time functions

2016-10-18 Thread ASF GitHub Bot (JIRA)


[ 
https://issues.apache.org/jira/browse/DRILL-4864?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15585655#comment-15585655
 ] 

ASF GitHub Bot commented on DRILL-4864:
---

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 ansiValuesForDeleting = 
Sets.newTreeSet(new LengthDescComparator());
+  private static final CaseInsensitiveMap 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 = "";
+  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 = "";
+  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_FU

[jira] [Commented] (DRILL-4864) Add ANSI format for date/time functions

2016-10-18 Thread ASF GitHub Bot (JIRA)


[ 
https://issues.apache.org/jira/browse/DRILL-4864?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15585656#comment-15585656
 ] 

ASF GitHub Bot commented on DRILL-4864:
---

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 ansiValuesForDeleting = 
Sets.newTreeSet(new LengthDescComparator());
+  private static final CaseInsensitiveMap 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 = "";
+  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 = "";
+  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_FU

[jira] [Commented] (DRILL-4864) Add ANSI format for date/time functions

2016-10-18 Thread ASF GitHub Bot (JIRA)


[ 
https://issues.apache.org/jira/browse/DRILL-4864?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15585652#comment-15585652
 ] 

ASF GitHub Bot commented on DRILL-4864:
---

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.


> Add ANSI format for date/time functions
> ---
>
> Key: DRILL-4864
> URL: https://issues.apache.org/jira/browse/DRILL-4864
> Project: Apache Drill
>  Issue Type: Improvement
>Affects Versions: 1.8.0
>Reporter: Serhii Harnyk
>Assignee: Gautam Kumar Parai
>  Labels: doc-impacting
> Fix For: 1.9.0
>
>
> The TO_DATE() is exposing the Joda string formatting conventions into the SQL 
> layer. This is not following SQL conventions used by ANSI and many other 
> database engines on the market.
> Add new UDF "ansi_to_joda(string)", that takes string that represents ANSI 
> datetime format and returns string that represents equal Joda format.
> Add new session option "drill.exec.fn.to_date_format" that can be one of two 
> values - "JODA"(default) and "ANSI".
> If option is set to "JODA" queries with to_date() function would work in 
> usual way.
> If option is set to "ANSI" second argument would be wrapped with 
> ansi_to_joda() function, that allows user to use ANSI datetime format
> Wrapping is used in to_date(), to_time() and to_timestamp() functions.
> Table of joda and ansi patterns which may be replaced
> ||Pattern name||  Ansi format ||  JodaTime format
> | Full name of day|   day |   
> | Day of year |   ddd |   D
> | Day of month|   dd  |   d
> | Day of week |   d   |   e
> | Name of month   |   month   |   
> | Abr name of month   |   mon |   MMM
> | Full era name   |   ee  |   G
> | Name of day |   dy  |   E
> | Time zone   |   tz  |   TZ
> | Hour 12 |   hh  |   h
> | Hour 12 |   hh12|   h
> | Hour 24 |   hh24|   H
> | Minute of hour  |   mi  |   m
> | Second of minute|   ss  |   s
> | Millisecond of minute   |   ms  |   S
> | Week of year|   ww  |   w
> | Month   |   mm  |   MM
> | Halfday am  |   am  |   aa
> | Halfday pm  |   pm  |   aa
> | ref.|   
> https://www.postgr

[jira] [Commented] (DRILL-4864) Add ANSI format for date/time functions

2016-10-18 Thread ASF GitHub Bot (JIRA)


[ 
https://issues.apache.org/jira/browse/DRILL-4864?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15585650#comment-15585650
 ] 

ASF GitHub Bot commented on DRILL-4864:
---

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


> Add ANSI format for date/time functions
> ---
>
> Key: DRILL-4864
> URL: https://issues.apache.org/jira/browse/DRILL-4864
> Project: Apache Drill
>  Issue Type: Improvement
>Affects Versions: 1.8.0
>Reporter: Serhii Harnyk
>Assignee: Gautam Kumar Parai
>  Labels: doc-impacting
> Fix For: 1.9.0
>
>
> The TO_DATE() is exposing the Joda string formatting conventions into the SQL 
> layer. This is not following SQL conventions used by ANSI and many other 
> database engines on the market.
> Add new UDF "ansi_to_joda(string)", that takes string that represents ANSI 
> datetime format and returns string that represents equal Joda format.
> Add new session option "drill.exec.fn.to_date_format" that can be one of two 
> values - "JODA"(default) and "ANSI".
> If option is set to "JODA" queries with to_date() function would work in 
> usual way.
> If option is set to "ANSI" second argument would be wrapped with 
> ansi_to_joda() function, that allows user to use ANSI datetime format
> Wrapping is used in to_date(), to_time() and to_timestamp() functions.
> Table of joda and ansi patterns which may be replaced
> ||Pattern name||  Ansi format ||  JodaTime format
> | Full name of day|   day |   
> | Day of year |   ddd |   D
> | Day of month|   dd  |   d
> | Day of week |   d   |   e
> | Name of month   |   month   |   
> | Abr name of month   |   mon |   MMM
> | Full era name   |   ee  |   G
> | Name of day |   dy  |   E
> | Time zone   |   tz  |   TZ
> | Hour 12 |   hh  |   h
> | Hour 12 |   hh12|   h
> | Hour 24 |   hh24|   H
> | Minute of hour  |   mi  |   m
> | Second of minute|   ss  |   s
> | Millisecond of minute   |   ms  |   S
> | Week of year|   ww  |   w
> | Month   |   mm  |   MM
> | Halfday am  |   am  |   aa
> | Halfday pm  |   pm  |   aa
> | ref.|   
> https://www.postgresql.org/docs/8.2/static/functions-formatting.html| 
>   
> http://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html
>  |
> Table of ansi pattern modifiers, which may be deleted from string
> ||Description ||  Pattern ||
> | fill mode (suppress padding blanks and zeroes)  |   fm  |
> | fixed format global option (see usage notes)|   fx  |
> | translation mode (print localized day and month names based on 
> lc_messages) |   tm  |
> | spell mode (not yet implemented)|   sp  |
> | ref.|   
> https://www.postgresql.org/docs/8.2/static/functions-formatting.html|



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)

[jira] [Commented] (DRILL-4864) Add ANSI format for date/time functions

2016-10-18 Thread ASF GitHub Bot (JIRA)


[ 
https://issues.apache.org/jira/browse/DRILL-4864?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15585651#comment-15585651
 ] 

ASF GitHub Bot commented on DRILL-4864:
---

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


> Add ANSI format for date/time functions
> ---
>
> Key: DRILL-4864
> URL: https://issues.apache.org/jira/browse/DRILL-4864
> Project: Apache Drill
>  Issue Type: Improvement
>Affects Versions: 1.8.0
>Reporter: Serhii Harnyk
>Assignee: Gautam Kumar Parai
>  Labels: doc-impacting
> Fix For: 1.9.0
>
>
> The TO_DATE() is exposing the Joda string formatting conventions into the SQL 
> layer. This is not following SQL conventions used by ANSI and many other 
> database engines on the market.
> Add new UDF "ansi_to_joda(string)", that takes string that represents ANSI 
> datetime format and returns string that represents equal Joda format.
> Add new session option "drill.exec.fn.to_date_format" that can be one of two 
> values - "JODA"(default) and "ANSI".
> If option is set to "JODA" queries with to_date() function would work in 
> usual way.
> If option is set to "ANSI" second argument would be wrapped with 
> ansi_to_joda() function, that allows user to use ANSI datetime format
> Wrapping is used in to_date(), to_time() and to_timestamp() functions.
> Table of joda and ansi patterns which may be replaced
> ||Pattern name||  Ansi format ||  JodaTime format
> | Full name of day|   day |   
> | Day of year |   ddd |   D
> | Day of month|   dd  |   d
> | Day of week |   d   |   e
> | Name of month   |   month   |   
> | Abr name of month   |   mon |   MMM
> | Full era name   |   ee  |   G
> | Name of day |   dy  |   E
> | Time zone   |   tz  |   TZ
> | Hour 12 |   hh  |   h
> | Hour 12 |   hh12|   h
> | Hour 24 |   hh24|   H
> | Minute of hour  |   mi  |   m
> | Second of minute|   ss  |   s
> | Millisecond of minute   |   ms  |   S
> | Week of year|   ww  |   w
> | Month   |   mm  |   MM
> | Halfday am  |   am  |   aa
> | Halfday pm  |   pm  |   aa
> | ref.|   
> https://www.postgresql.org/docs/8.2/static/functions-formatting.html| 
>   
> http://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html
>  |
> Table of ansi pattern modifiers, which may be deleted from string
> ||Description ||  Pattern ||
> | fill mode (suppress padding blanks and zeroes)  |   fm  |
> | fixed format global option (see usage notes)|   fx  |
> | translation mode (print localized day and month names based on 
> lc_messages) |   tm  |
> |

[jira] [Commented] (DRILL-4864) Add ANSI format for date/time functions

2016-10-18 Thread ASF GitHub Bot (JIRA)


[ 
https://issues.apache.org/jira/browse/DRILL-4864?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15585654#comment-15585654
 ] 

ASF GitHub Bot commented on DRILL-4864:
---

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.


> Add ANSI format for date/time functions
> ---
>
> Key: DRILL-4864
> URL: https://issues.apache.org/jira/browse/DRILL-4864
> Project: Apache Drill
>  Issue Type: Improvement
>Affects Versions: 1.8.0
>Reporter: Serhii Harnyk
>Assignee: Gautam Kumar Parai
>  Labels: doc-impacting
> Fix For: 1.9.0
>
>
> The TO_DATE() is exposing the Joda string formatting conventions into the SQL 
> layer. This is not following SQL conventions used by ANSI and many other 
> database engines on the market.
> Add new UDF "ansi_to_joda(string)", that takes string that represents ANSI 
> datetime format and returns string that represents equal Joda format.
> Add new session option "drill.exec.fn.to_date_format" that can be one of two 
> values - "JODA"(default) and "ANSI".
> If option is set to "JODA" queries with to_date() function would work in 
> usual way.
> If option is set to "ANSI" second argument would be wrapped with 
> ansi_to_joda() function, that allows user to use ANSI datetime format
> Wrapping is used in to_date(), to_time() and to_timestamp() functions.
> Table of joda and ansi patterns which may be replaced
> ||Pattern name||  Ansi format ||  JodaTime format
> | Full name of day|   day |   
> | Day of year |   ddd |   D
> | Day of month|   dd  |   d
> | Day of week |   d   |   e
> | Name of month   |   month   |   
> | Abr name of month   |   mon |   MMM
> | Full era name   |   ee  |   G
> | Name of day |   dy  |   E
> | Time zone   |   tz  |   TZ
> | Hour 12 |   hh  |   h
> | Hour 12 |   hh12|   h
> | Hour 24 |   hh24|   H
> | Minute of hour  |   mi  |   m
> | Second of minute|   ss  |   s
> | Millisecond of minute   |   ms  |   S
> | Week of year|   ww  |   w
> | Month   |   mm  |   MM
> | Halfday am  |   am  |   aa
> | Halfday pm  |   pm  |   aa
> | ref.|   
> https://www.postgresql.org/docs/8.2/static/functions-formatting.html| 
>   
> http://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html
>  |
> Table of ansi pattern modifiers, which may be deleted from string
> ||Description ||  Pattern ||
> | fill mode (suppress padding blanks and zeroes)  |   fm  |
> | fixed format global option (see usage notes)|   fx  |
> | translation mode (print localized day and month names based on 
> lc_messages) |   tm  |
> | spell mode (not yet implemented)|   sp  |
> | ref.|   
> https://www.postgresql.org/docs/8.2/static/functions-formatting.html|



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)

[jira] [Commented] (DRILL-4864) Add ANSI format for date/time functions

2016-10-18 Thread ASF GitHub Bot (JIRA)


[ 
https://issues.apache.org/jira/browse/DRILL-4864?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15585653#comment-15585653
 ] 

ASF GitHub Bot commented on DRILL-4864:
---

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.


> Add ANSI format for date/time functions
> ---
>
> Key: DRILL-4864
> URL: https://issues.apache.org/jira/browse/DRILL-4864
> Project: Apache Drill
>  Issue Type: Improvement
>Affects Versions: 1.8.0
>Reporter: Serhii Harnyk
>Assignee: Gautam Kumar Parai
>  Labels: doc-impacting
> Fix For: 1.9.0
>
>
> The TO_DATE() is exposing the Joda string formatting conventions into the SQL 
> layer. This is not following SQL conventions used by ANSI and many other 
> database engines on the market.
> Add new UDF "ansi_to_joda(string)", that takes string that represents ANSI 
> datetime format and returns string that represents equal Joda format.
> Add new session option "drill.exec.fn.to_date_format" that can be one of two 
> values - "JODA"(default) and "ANSI".
> If option is set to "JODA" queries with to_date() function would work in 
> usual way.
> If option is set to "ANSI" second argument would be wrapped with 
> ansi_to_joda() function, that allows user to use ANSI datetime format
> Wrapping is used in to_date(), to_time() and to_timestamp() functions.
> Table of joda and ansi patterns which may be replaced
> ||Pattern name||  Ansi format ||  JodaTime format
> | Full name of day|   day |   
> | Day of year |   ddd |   D
> | Day of month|   dd  |   d
> | Day of week |   d   |   e
> | Name of month   |   month   |   
> | Abr name of month   |   mon |   MMM
> | Full era name   |   ee  |   G
> | Name of day |   dy  |   E
> | Time zone   |   tz  |   TZ
> | Hour 12 |   hh  |   h
> | Hour 12 |   hh12|   h
> | Hour 24 |   hh24|   H
> | Minute of hour  |   mi  |   m
> | Second of minute|   ss  |   s
> | Millisecond of minute   |   ms  |   S
> | Week of year|   ww  |   w
> | Month   |   mm  |   MM
> | Halfday am  |   am  |   aa
> | Halfday pm  |   pm  |   aa
> | ref.|   
> https://www.postgresql.org/docs/8.2/static/functions-formatting.html| 
>   
> http://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html
>  |
> Table of ansi pattern modifiers, which may be deleted from string
> ||Description ||  Pattern ||
> | fill mode (suppress padding blanks and zeroes)  |   fm  |
> | fixed format global option (see usage notes)|   fx  |
> | translation mode (print localized day and month names based on 
> lc_messages) |   tm  |
> | spell mode (not yet implemented)|   sp  |
> | ref.|   
> https://www.postgresql.org/docs/8.2/static/functions-formatting.html|



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)

[jira] [Commented] (DRILL-4864) Add ANSI format for date/time functions

2016-09-30 Thread ASF GitHub Bot (JIRA)


[ 
https://issues.apache.org/jira/browse/DRILL-4864?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15537659#comment-15537659
 ] 

ASF GitHub Bot commented on DRILL-4864:
---

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?


> Add ANSI format for date/time functions
> ---
>
> Key: DRILL-4864
> URL: https://issues.apache.org/jira/browse/DRILL-4864
> Project: Apache Drill
>  Issue Type: Improvement
>Affects Versions: 1.8.0
>Reporter: Serhii Harnyk
>Assignee: Gautam Kumar Parai
>  Labels: doc-impacting
> Fix For: 1.9.0
>
>
> The TO_DATE() is exposing the Joda string formatting conventions into the SQL 
> layer. This is not following SQL conventions used by ANSI and many other 
> database engines on the market.
> Add new UDF "ansi_to_joda(string)", that takes string that represents ANSI 
> datetime format and returns string that represents equal Joda format.
> Add new session option "drill.exec.fn.to_date_format" that can be one of two 
> values - "JODA"(default) and "ANSI".
> If option is set to "JODA" queries with to_date() function would work in 
> usual way.
> If option is set to "ANSI" second argument would be wrapped with 
> ansi_to_joda() function, that allows user to use ANSI datetime format
> Wrapping is used in to_date(), to_time() and to_timestamp() functions.
> Table of joda and ansi patterns which may be replaced
> ||Pattern name||  Ansi format ||  JodaTime format
> | Full name of day|   day |   
> | Day of year |   ddd |   D
> | Day of month|   dd  |   d
> | Day of week |   d   |   e
> | Name of month   |   month   |   
> | Abr name of month   |   mon |   MMM
> | Full era name   |   ee  |   G
> | Name of day |   dy  |   E
> | Time zone   |   tz  |   TZ
> | Hour 12 |   hh  |   h
> | Hour 12 |   hh12|   h
> | Hour 24 |   hh24|   H
> | Minute of hour  |   mi  |   m
> | Second of minute|   ss  |   s
> | Millisecond of minute   |   ms  |   S
> | Week of year|   ww  |   w
> | Month   |   mm  |   MM
> | Halfday am  |   am  |   aa
> | Halfday pm  |   pm  |   aa
> | ref.|   
> https://www.postgresql.org/docs/8.2/static/functions-formatting.html| 
>   
> http://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html
>  |
> Table of ansi pattern modifiers, which may be deleted from string
> ||Description ||  Pattern ||
> | fill mode (suppress padding blanks and zeroes)  |   fm  |
> | fixed format global option (see usage notes)|   fx  |
> | translation mode (print localized day and month names based on 
> lc_messages) |   tm  |
> | spell mode (not yet implemented)|   sp  |
> | ref.|   
> https://www.postgresql.org/docs/8.2/static/functions-formatting.html|



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)

[jira] [Commented] (DRILL-4864) Add ANSI format for date/time functions

2016-09-30 Thread ASF GitHub Bot (JIRA)


[ 
https://issues.apache.org/jira/browse/DRILL-4864?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15537662#comment-15537662
 ] 

ASF GitHub Bot commented on DRILL-4864:
---

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 (


> Add ANSI format for date/time functions
> ---
>
> Key: DRILL-4864
> URL: https://issues.apache.org/jira/browse/DRILL-4864
> Project: Apache Drill
>  Issue Type: Improvement
>Affects Versions: 1.8.0
>Reporter: Serhii Harnyk
>Assignee: Gautam Kumar Parai
>  Labels: doc-impacting
> Fix For: 1.9.0
>
>
> The TO_DATE() is exposing the Joda string formatting conventions into the SQL 
> layer. This is not following SQL conventions used by ANSI and many other 
> database engines on the market.
> Add new UDF "ansi_to_joda(string)", that takes string that represents ANSI 
> datetime format and returns string that represents equal Joda format.
> Add new session option "drill.exec.fn.to_date_format" that can be one of two 
> values - "JODA"(default) and "ANSI".
> If option is set to "JODA" queries with to_date() function would work in 
> usual way.
> If option is set to "ANSI" second argument would be wrapped with 
> ansi_to_joda() function, that allows user to use ANSI datetime format
> Wrapping is used in to_date(), to_time() and to_timestamp() functions.
> Table of joda and ansi patterns which may be replaced
> ||Pattern name||  Ansi format ||  JodaTime format
> | Full name of day|   day |   
> | Day of year |   ddd |   D
> | Day of month|   dd  |   d
> | Day of week |   d   |   e
> | Name of month   |   month   |   
> | Abr name of month   |   mon |   MMM
> | Full era name   |   ee  |   G
> | Name of day |   dy  |   E
> | Time zone   |   tz  |   TZ
> | Hour 12 |   hh  |   h
> | Hour 12 |   hh12|   h
> | Hour 24 |   hh24|   H
> | Minute of hour  |   mi  |   m
> | Second of minute|   ss  |   s
> | Millisecond of minute   |   ms  |   S
> | Week of year|   ww  |   w
> | Month   |   mm  |   MM
> | Halfday am  |   am  |   aa
> | Halfday pm  |   pm  |   aa
> | ref.|   
> https://www.postgresql.org/docs/8.2/static/functions-formatting.html| 
>   
> http://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html
>  |
> Table of ansi pattern modifiers, which may be deleted from string
> ||Description ||  Pattern ||
> | fill mode (suppress padding blanks and zeroes)  |   fm  |
> | fixed format global option (see usage notes)|   fx  |
> | translation mode (print localized day and month names based on 
> lc_messages) |   tm  |
> | spell mode (not yet implemented)|   sp  |
> | ref.|   
> https://www.postgresql.org/docs/8.2/static/functions-formatting.html|



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)

[jira] [Commented] (DRILL-4864) Add ANSI format for date/time functions

2016-09-30 Thread ASF GitHub Bot (JIRA)


[ 
https://issues.apache.org/jira/browse/DRILL-4864?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15537661#comment-15537661
 ] 

ASF GitHub Bot commented on DRILL-4864:
---

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?


> Add ANSI format for date/time functions
> ---
>
> Key: DRILL-4864
> URL: https://issues.apache.org/jira/browse/DRILL-4864
> Project: Apache Drill
>  Issue Type: Improvement
>Affects Versions: 1.8.0
>Reporter: Serhii Harnyk
>Assignee: Gautam Kumar Parai
>  Labels: doc-impacting
> Fix For: 1.9.0
>
>
> The TO_DATE() is exposing the Joda string formatting conventions into the SQL 
> layer. This is not following SQL conventions used by ANSI and many other 
> database engines on the market.
> Add new UDF "ansi_to_joda(string)", that takes string that represents ANSI 
> datetime format and returns string that represents equal Joda format.
> Add new session option "drill.exec.fn.to_date_format" that can be one of two 
> values - "JODA"(default) and "ANSI".
> If option is set to "JODA" queries with to_date() function would work in 
> usual way.
> If option is set to "ANSI" second argument would be wrapped with 
> ansi_to_joda() function, that allows user to use ANSI datetime format
> Wrapping is used in to_date(), to_time() and to_timestamp() functions.
> Table of joda and ansi patterns which may be replaced
> ||Pattern name||  Ansi format ||  JodaTime format
> | Full name of day|   day |   
> | Day of year |   ddd |   D
> | Day of month|   dd  |   d
> | Day of week |   d   |   e
> | Name of month   |   month   |   
> | Abr name of month   |   mon |   MMM
> | Full era name   |   ee  |   G
> | Name of day |   dy  |   E
> | Time zone   |   tz  |   TZ
> | Hour 12 |   hh  |   h
> | Hour 12 |   hh12|   h
> | Hour 24 |   hh24|   H
> | Minute of hour  |   mi  |   m
> | Second of minute|   ss  |   s
> | Millisecond of minute   |   ms  |   S
> | Week of year|   ww  |   w
> | Month   |   mm  |   MM
> | Halfday am  |   am  |   aa
> | Halfday pm  |   pm  |   aa
> | ref.|   
> https://www.postgresql.org/docs/8.2/st

[jira] [Commented] (DRILL-4864) Add ANSI format for date/time functions

2016-09-30 Thread ASF GitHub Bot (JIRA)


[ 
https://issues.apache.org/jira/browse/DRILL-4864?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15537657#comment-15537657
 ] 

ASF GitHub Bot commented on DRILL-4864:
---

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 =


> Add ANSI format for date/time functions
> ---
>
> Key: DRILL-4864
> URL: https://issues.apache.org/jira/browse/DRILL-4864
> Project: Apache Drill
>  Issue Type: Improvement
>Affects Versions: 1.8.0
>Reporter: Serhii Harnyk
>Assignee: Gautam Kumar Parai
>  Labels: doc-impacting
> Fix For: 1.9.0
>
>
> The TO_DATE() is exposing the Joda string formatting conventions into the SQL 
> layer. This is not following SQL conventions used by ANSI and many other 
> database engines on the market.
> Add new UDF "ansi_to_joda(string)", that takes string that represents ANSI 
> datetime format and returns string that represents equal Joda format.
> Add new session option "drill.exec.fn.to_date_format" that can be one of two 
> values - "JODA"(default) and "ANSI".
> If option is set to "JODA" queries with to_date() function would work in 
> usual way.
> If option is set to "ANSI" second argument would be wrapped with 
> ansi_to_joda() function, that allows user to use ANSI datetime format
> Wrapping is used in to_date(), to_time() and to_timestamp() functions.
> Table of joda and ansi patterns which may be replaced
> ||Pattern name||  Ansi format ||  JodaTime format
> | Full name of day|   day |   
> | Day of year |   ddd |   D
> | Day of month|   dd  |   d
> | Day of week |   d   |   e
> | Name of month   |   month   |   
> | Abr name of month   |   mon |   MMM
> | Full era name   |   ee  |   G
> | Name of day |   dy  |   E
> | Time zone   |   tz  |   TZ
> | Hour 12 |   hh  |   h
> | Hour 12 |   hh12|   h
> | Hour 24 |   hh24|   H
> | Minute of hour  |   mi  |   m
> | Second of minute|   ss  |   s
> | Millisecond of minute   |   ms  |   S
> | Week of year|   ww  |   w
> | Month   |   mm  |   MM
> | Halfday am  |   am  |   aa
> | Halfday pm  |   pm  |   aa
> | ref.|   
> https://www.postgresql.org/docs/8.2/static/functions-formatting.html| 
>   
> http://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html
>  |
> Table of ansi pattern modifiers, which may be deleted from string
> ||Description ||  Pattern ||
> | fill mode (suppress padding blanks and zeroes)  |   fm  |
> | fixed format global option (see usage notes)|   fx  |
> | translation mode (print localized day and month names based on 
> lc_messages) |   tm  |
> | sp

[jira] [Commented] (DRILL-4864) Add ANSI format for date/time functions

2016-09-30 Thread ASF GitHub Bot (JIRA)


[ 
https://issues.apache.org/jira/browse/DRILL-4864?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15537656#comment-15537656
 ] 

ASF GitHub Bot commented on DRILL-4864:
---

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 ansiValuesForDeleting = 
Sets.newTreeSet(new LengthDescComparator());
+  private static final CaseInsensitiveMap 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 = "";
+  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 = "";
+  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_

[jira] [Commented] (DRILL-4864) Add ANSI format for date/time functions

2016-09-30 Thread ASF GitHub Bot (JIRA)


[ 
https://issues.apache.org/jira/browse/DRILL-4864?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15537658#comment-15537658
 ] 

ASF GitHub Bot commented on DRILL-4864:
---

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-')) from emp;

Would we get select to_date(1234545, 
ansi_to_joda(ansi_to_joda('dd-MM-'))) from emp;?


> Add ANSI format for date/time functions
> ---
>
> Key: DRILL-4864
> URL: https://issues.apache.org/jira/browse/DRILL-4864
> Project: Apache Drill
>  Issue Type: Improvement
>Affects Versions: 1.8.0
>Reporter: Serhii Harnyk
>Assignee: Gautam Kumar Parai
>  Labels: doc-impacting
> Fix For: 1.9.0
>
>
> The TO_DATE() is exposing the Joda string formatting conventions into the SQL 
> layer. This is not following SQL conventions used by ANSI and many other 
> database engines on the market.
> Add new UDF "ansi_to_joda(string)", that takes string that represents ANSI 
> datetime format and returns string that represents equal Joda format.
> Add new session option "drill.exec.fn.to_date_format" that can be one of two 
> values - "JODA"(default) and "ANSI".
> If option is set to "JODA" queries with to_date() function would work in 
> usual way.
> If option is set to "ANSI" second argument would be wrapped with 
> ansi_to_joda() function, that allows user to use ANSI datetime format
> Wrapping is used in to_date(), to_time() and to_timestamp() functions.
> Table of joda and ansi patterns which may be replaced
> ||Pattern name||  Ansi format ||  JodaTime format
> | Full name of day|   day |   
> | Day of year |   ddd |   D
> | Day of month|   dd  |   d
> | Day of week |   d   |   e
> | Name of month   |   month   |   
> | Abr name of month   |   mon |   MMM
> | Full era name   |   ee  |   G
> | Name of day |   dy  |   E
> | Time zone   |   tz  |   TZ
> | Hour 12 |   hh  |   h
> | Hour 12 |   hh12|   h
> | Hour 24 |   hh24|   H
> | Minute of hour  |   mi  |   m
> | Second of minute|   ss  |   s
> | Millisecond of minute   |   ms  |   S
> | Week of year|   ww  |   w
> | Month   |   mm  |   MM
> | Halfday am  |   am  |   aa
> | Halfday pm  |   pm  |   aa
> | ref.|   
> https://www.postgresql.org/docs/8.2/static/functions-formatting.html| 
>   
> http://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html
>  |
> Table of ansi pattern modifiers, which may be deleted from string
> ||Description ||  Pattern ||
> | fill mode (suppress padding blanks and zeroes)  |   fm  |
> | fixed format global option (see usage notes)|   fx  |
> | translation mode (print localized day and month names based on 
> lc_messages) |   tm  |
> | spell mode (not yet implemented)|   sp  |
> | ref.|   
> https://www.postgresql.org/docs/8.2/static/functions-formatting.html|



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)

[jira] [Commented] (DRILL-4864) Add ANSI format for date/time functions

2016-09-30 Thread ASF GitHub Bot (JIRA)


[ 
https://issues.apache.org/jira/browse/DRILL-4864?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15537660#comment-15537660
 ] 

ASF GitHub Bot commented on DRILL-4864:
---

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 ansiValuesForDeleting = 
Sets.newTreeSet(new LengthDescComparator());
+  private static final CaseInsensitiveMap 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 = "";
+  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 = "";
+  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_

[jira] [Commented] (DRILL-4864) Add ANSI format for date/time functions

2016-09-06 Thread ASF GitHub Bot (JIRA)


[ 
https://issues.apache.org/jira/browse/DRILL-4864?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15467326#comment-15467326
 ] 

ASF GitHub Bot commented on DRILL-4864:
---

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 
Date:   2016-09-01T14:48:02Z

DRILL-4864: Add ANSI format for date/time functions




> Add ANSI format for date/time functions
> ---
>
> Key: DRILL-4864
> URL: https://issues.apache.org/jira/browse/DRILL-4864
> Project: Apache Drill
>  Issue Type: Improvement
>Affects Versions: 1.8.0
>Reporter: Serhii Harnyk
>Assignee: Serhii Harnyk
>  Labels: doc-impacting
> Fix For: 1.9.0
>
>
> The TO_DATE() is exposing the Joda string formatting conventions into the SQL 
> layer. This is not following SQL conventions used by ANSI and many other 
> database engines on the market.
> Add new UDF "ansi_to_joda(string)", that takes string that represents ANSI 
> datetime format and returns string that represents equal Joda format.
> Add new session option "drill.exec.fn.to_date_format" that can be one of two 
> values - "JODA"(default) and "ANSI".
> If option is set to "JODA" queries with to_date() function would work in 
> usual way.
> If option is set to "ANSI" second argument would be wrapped with 
> ansi_to_joda() function, that allows user to use ANSI datetime format
> Wrapping is used in to_date(), to_time() and to_timestamp() functions.
> Table of joda and ansi patterns which may be replaced
> ||Pattern name||  Ansi format ||  JodaTime format
> | Full name of day|   day |   
> | Day of year |   ddd |   D
> | Day of month|   dd  |   d
> | Day of week |   d   |   e
> | Name of month   |   month   |   
> | Abr name of month   |   mon |   MMM
> | Full era name   |   ee  |   G
> | Name of day |   dy  |   E
> | Time zone   |   tz  |   TZ
> | Hour 12 |   hh  |   h
> | Hour 12 |   hh12|   h
> | Hour 24 |   hh24|   H
> | Minute of hour  |   mi  |   m
> | Second of minute|   ss  |   s
> | Millisecond of minute   |   ms  |   S
> | Week of year|   ww  |   w
> | Month   |   mm  |   MM
> | Halfday am  |   am  |   aa
> | Halfday pm  |   pm  |   aa
> | ref.|   
> https://www.postgresql.org/docs/8.2/static/functions-formatting.html| 
>   
> http://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html
>  |
> Table of ansi pattern modifiers, which may be deleted from string
> ||Description ||  Pattern ||
> | fill mode (suppress padding blanks and zeroes)  |   fm  |
> | fixed format global option (see usage notes)|   fx  |
> | translation mode (print localized day and month names based on 
> lc_messages) |   tm  |
> | spell mode (not yet implemented)|   sp  |
> | ref.|   
> https://www.postgresql.org/docs/8.2/static/functions-formatting.html|



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)

37 matches

Mail list logo