[
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<String> ansiValuesForDeleting =
Sets.newTreeSet(new LengthDescComparator());
+ private static final CaseInsensitiveMap<String> ansiToJodaMap =
CaseInsensitiveMap.newTreeMap(new LengthDescComparator());
+
+ //tokens for deleting
+ public static final String SUFFIX_SP = "sp";
+ public static final String PREFIX_FM = "fm";
+ public static final String PREFIX_FX = "fx";
+ public static final String PREFIX_TM = "tm";
+
+ //ansi patterns
+ public static final String ANSI_FULL_NAME_OF_DAY = "day";
+ public static final String ANSI_DAY_OF_YEAR = "ddd";
+ public static final String ANSI_DAY_OF_MONTH = "dd";
+ public static final String ANSI_DAY_OF_WEEK = "d";
+ public static final String ANSI_NAME_OF_MONTH = "month";
+ public static final String ANSI_ABR_NAME_OF_MONTH = "mon";
+ public static final String ANSI_FULL_ERA_NAME = "ee";
+ public static final String ANSI_NAME_OF_DAY = "dy";
+ public static final String ANSI_TIME_ZONE_NAME = "tz";
+ public static final String ANSI_HOUR_12_NAME = "hh";
+ public static final String ANSI_HOUR_12_OTHER_NAME = "hh12";
+ public static final String ANSI_HOUR_24_NAME = "hh24";
+ public static final String ANSI_MINUTE_OF_HOUR_NAME = "mi";
+ public static final String ANSI_SECOND_OF_MINUTE_NAME = "ss";
+ public static final String ANSI_MILLISECOND_OF_MINUTE_NAME = "ms";
+ public static final String ANSI_WEEK_OF_YEAR = "ww";
+ public static final String ANSI_MONTH = "mm";
+ public static final String ANSI_HALFDAY_AM = "am";
+ public static final String ANSI_HALFDAY_PM = "pm";
+
+ //jodaTime patterns
+ public static final String JODA_FULL_NAME_OF_DAY = "EEEE";
+ public static final String JODA_DAY_OF_YEAR = "D";
+ public static final String JODA_DAY_OF_MONTH = "d";
+ public static final String JODA_DAY_OF_WEEK = "e";
+ public static final String JODA_NAME_OF_MONTH = "MMMM";
+ public static final String JODA_ABR_NAME_OF_MONTH = "MMM";
+ public static final String JODA_FULL_ERA_NAME = "G";
+ public static final String JODA_NAME_OF_DAY = "E";
+ public static final String JODA_TIME_ZONE_NAME = "TZ";
+ public static final String JODA_HOUR_12_NAME = "h";
+ public static final String JODA_HOUR_12_OTHER_NAME = "h";
+ public static final String JODA_HOUR_24_NAME = "H";
+ public static final String JODA_MINUTE_OF_HOUR_NAME = "m";
+ public static final String JODA_SECOND_OF_MINUTE_NAME = "s";
+ public static final String JODA_MILLISECOND_OF_MINUTE_NAME = "S";
+ public static final String JODA_WEEK_OF_YEAR = "w";
+ public static final String JODA_MONTH = "MM";
+ public static final String JODA_HALFDAY = "aa";
+
+ static {
+ ansiToJodaMap.put(ANSI_FULL_NAME_OF_DAY, JODA_FULL_NAME_OF_DAY);
+ ansiToJodaMap.put(ANSI_DAY_OF_YEAR, JODA_DAY_OF_YEAR);
+ ansiToJodaMap.put(ANSI_DAY_OF_MONTH, JODA_DAY_OF_MONTH);
+ ansiToJodaMap.put(ANSI_DAY_OF_WEEK, JODA_DAY_OF_WEEK);
+ ansiToJodaMap.put(ANSI_NAME_OF_MONTH, JODA_NAME_OF_MONTH);
+ ansiToJodaMap.put(ANSI_ABR_NAME_OF_MONTH, JODA_ABR_NAME_OF_MONTH);
+ ansiToJodaMap.put(ANSI_FULL_ERA_NAME, JODA_FULL_ERA_NAME);
+ ansiToJodaMap.put(ANSI_NAME_OF_DAY, JODA_NAME_OF_DAY);
+ ansiToJodaMap.put(ANSI_TIME_ZONE_NAME, JODA_TIME_ZONE_NAME);
+ ansiToJodaMap.put(ANSI_HOUR_12_NAME, JODA_HOUR_12_NAME);
+ ansiToJodaMap.put(ANSI_HOUR_12_OTHER_NAME, JODA_HOUR_12_OTHER_NAME);
+ ansiToJodaMap.put(ANSI_HOUR_24_NAME, JODA_HOUR_24_NAME);
+ ansiToJodaMap.put(ANSI_MINUTE_OF_HOUR_NAME, JODA_MINUTE_OF_HOUR_NAME);
+ ansiToJodaMap.put(ANSI_SECOND_OF_MINUTE_NAME,
JODA_SECOND_OF_MINUTE_NAME);
+ ansiToJodaMap.put(ANSI_MILLISECOND_OF_MINUTE_NAME,
JODA_MILLISECOND_OF_MINUTE_NAME);
+ ansiToJodaMap.put(ANSI_WEEK_OF_YEAR, JODA_WEEK_OF_YEAR);
+ ansiToJodaMap.put(ANSI_MONTH, JODA_MONTH);
+ ansiToJodaMap.put(ANSI_HALFDAY_AM, JODA_HALFDAY);
+ ansiToJodaMap.put(ANSI_HALFDAY_PM, JODA_HALFDAY);
+ }
+
+ static {
+ ansiValuesForDeleting.add(SUFFIX_SP);
+ ansiValuesForDeleting.add(PREFIX_FM);
+ ansiValuesForDeleting.add(PREFIX_FX);
+ ansiValuesForDeleting.add(PREFIX_TM);
+ }
+
+ /**
+ * Replaces all ansi patterns from {@param pattern},
+ * available in ansiToJodaMap keys to jodaTime equivalents.
+ *
+ * @param pattern date pattern in ansi format
+ * @return date pattern with replaced patterns in joda format
+ */
+ public static String toJodaFormat(String pattern) {
+ String preparedString = deleteFromAnsi(pattern);
+ StringBuilder builder = new StringBuilder(preparedString);
+
+ int start = 0; //every time search of ansi token in pattern will
start from this index.
+ int minPos; //min position of the longest ansi token
+ do {
+ //finds first value with max length
+ minPos = builder.length();
+ String firstMatch = null;
+ for (String ansiPattern : ansiToJodaMap.keySet()) { //keys
sorted in length decreasing
--- End diff --
Please add a comment explaining why the keys need to be in length
decreasing maybe with an example. e.g. month and mon?
> 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 | EEEE
> | Day of year | ddd | D
> | Day of month | dd | d
> | Day of week | d | e
> | Name of month | month | MMMM
> | 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)
