[ 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<String> ansiValuesForDeleting = Sets.newTreeSet(new LengthDescComparator()); + private static final CaseInsensitiveMap<String> ansiToJodaMap = CaseInsensitiveMap.newTreeMap(new LengthDescComparator()); + + //tokens for deleting + public static final String SUFFIX_SP = "sp"; + public static final String PREFIX_FM = "fm"; + public static final String PREFIX_FX = "fx"; + public static final String PREFIX_TM = "tm"; + + //ansi patterns + public static final String ANSI_FULL_NAME_OF_DAY = "day"; + public static final String ANSI_DAY_OF_YEAR = "ddd"; + public static final String ANSI_DAY_OF_MONTH = "dd"; + public static final String ANSI_DAY_OF_WEEK = "d"; + public static final String ANSI_NAME_OF_MONTH = "month"; + public static final String ANSI_ABR_NAME_OF_MONTH = "mon"; + public static final String ANSI_FULL_ERA_NAME = "ee"; + public static final String ANSI_NAME_OF_DAY = "dy"; + public static final String ANSI_TIME_ZONE_NAME = "tz"; + public static final String ANSI_HOUR_12_NAME = "hh"; + public static final String ANSI_HOUR_12_OTHER_NAME = "hh12"; + public static final String ANSI_HOUR_24_NAME = "hh24"; + public static final String ANSI_MINUTE_OF_HOUR_NAME = "mi"; + public static final String ANSI_SECOND_OF_MINUTE_NAME = "ss"; + public static final String ANSI_MILLISECOND_OF_MINUTE_NAME = "ms"; + public static final String ANSI_WEEK_OF_YEAR = "ww"; + public static final String ANSI_MONTH = "mm"; + public static final String ANSI_HALFDAY_AM = "am"; + public static final String ANSI_HALFDAY_PM = "pm"; + + //jodaTime patterns + public static final String JODA_FULL_NAME_OF_DAY = "EEEE"; + public static final String JODA_DAY_OF_YEAR = "D"; + public static final String JODA_DAY_OF_MONTH = "d"; + public static final String JODA_DAY_OF_WEEK = "e"; + public static final String JODA_NAME_OF_MONTH = "MMMM"; + public static final String JODA_ABR_NAME_OF_MONTH = "MMM"; + public static final String JODA_FULL_ERA_NAME = "G"; + public static final String JODA_NAME_OF_DAY = "E"; + public static final String JODA_TIME_ZONE_NAME = "TZ"; + public static final String JODA_HOUR_12_NAME = "h"; + public static final String JODA_HOUR_12_OTHER_NAME = "h"; + public static final String JODA_HOUR_24_NAME = "H"; + public static final String JODA_MINUTE_OF_HOUR_NAME = "m"; + public static final String JODA_SECOND_OF_MINUTE_NAME = "s"; + public static final String JODA_MILLISECOND_OF_MINUTE_NAME = "S"; + public static final String JODA_WEEK_OF_YEAR = "w"; + public static final String JODA_MONTH = "MM"; + public static final String JODA_HALFDAY = "aa"; + + static { + ansiToJodaMap.put(ANSI_FULL_NAME_OF_DAY, JODA_FULL_NAME_OF_DAY); + ansiToJodaMap.put(ANSI_DAY_OF_YEAR, JODA_DAY_OF_YEAR); + ansiToJodaMap.put(ANSI_DAY_OF_MONTH, JODA_DAY_OF_MONTH); + ansiToJodaMap.put(ANSI_DAY_OF_WEEK, JODA_DAY_OF_WEEK); + ansiToJodaMap.put(ANSI_NAME_OF_MONTH, JODA_NAME_OF_MONTH); + ansiToJodaMap.put(ANSI_ABR_NAME_OF_MONTH, JODA_ABR_NAME_OF_MONTH); + ansiToJodaMap.put(ANSI_FULL_ERA_NAME, JODA_FULL_ERA_NAME); + ansiToJodaMap.put(ANSI_NAME_OF_DAY, JODA_NAME_OF_DAY); + ansiToJodaMap.put(ANSI_TIME_ZONE_NAME, JODA_TIME_ZONE_NAME); + ansiToJodaMap.put(ANSI_HOUR_12_NAME, JODA_HOUR_12_NAME); + ansiToJodaMap.put(ANSI_HOUR_12_OTHER_NAME, JODA_HOUR_12_OTHER_NAME); + ansiToJodaMap.put(ANSI_HOUR_24_NAME, JODA_HOUR_24_NAME); + ansiToJodaMap.put(ANSI_MINUTE_OF_HOUR_NAME, JODA_MINUTE_OF_HOUR_NAME); + ansiToJodaMap.put(ANSI_SECOND_OF_MINUTE_NAME, JODA_SECOND_OF_MINUTE_NAME); + ansiToJodaMap.put(ANSI_MILLISECOND_OF_MINUTE_NAME, JODA_MILLISECOND_OF_MINUTE_NAME); + ansiToJodaMap.put(ANSI_WEEK_OF_YEAR, JODA_WEEK_OF_YEAR); + ansiToJodaMap.put(ANSI_MONTH, JODA_MONTH); + ansiToJodaMap.put(ANSI_HALFDAY_AM, JODA_HALFDAY); + ansiToJodaMap.put(ANSI_HALFDAY_PM, JODA_HALFDAY); + } + + static { + ansiValuesForDeleting.add(SUFFIX_SP); + ansiValuesForDeleting.add(PREFIX_FM); + ansiValuesForDeleting.add(PREFIX_FX); + ansiValuesForDeleting.add(PREFIX_TM); + } + + /** + * Replaces all ansi patterns from {@param pattern}, + * available in ansiToJodaMap keys to jodaTime equivalents. + * + * @param pattern date pattern in ansi format + * @return date pattern with replaced patterns in joda format + */ + public static String toJodaFormat(String pattern) { + String preparedString = deleteFromAnsi(pattern); --- End diff -- Please add a comment explaining `String preparedString = deleteFromAnsi(pattern)` > 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)