[ 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<String> postgresValuesForDeleting = Sets.newTreeSet(new LengthDescComparator()); + private static final CaseInsensitiveMap<String> postgresToJodaMap = CaseInsensitiveMap.newTreeMap(new LengthDescComparator()); + + // tokens for deleting + public static final String SUFFIX_SP = "sp"; + public static final String PREFIX_FM = "fm"; + public static final String PREFIX_FX = "fx"; + public static final String PREFIX_TM = "tm"; + + // postgres patterns + public static final String POSTGRES_FULL_NAME_OF_DAY = "day"; + public static final String POSTGRES_DAY_OF_YEAR = "ddd"; + public static final String POSTGRES_DAY_OF_MONTH = "dd"; + public static final String POSTGRES_DAY_OF_WEEK = "d"; + public static final String POSTGRES_NAME_OF_MONTH = "month"; + public static final String POSTGRES_ABR_NAME_OF_MONTH = "mon"; + public static final String POSTGRES_FULL_ERA_NAME = "ee"; + public static final String POSTGRES_NAME_OF_DAY = "dy"; + public static final String POSTGRES_TIME_ZONE_NAME = "tz"; + public static final String POSTGRES_HOUR_12_NAME = "hh"; + public static final String POSTGRES_HOUR_12_OTHER_NAME = "hh12"; + public static final String POSTGRES_HOUR_24_NAME = "hh24"; + public static final String POSTGRES_MINUTE_OF_HOUR_NAME = "mi"; + public static final String POSTGRES_SECOND_OF_MINUTE_NAME = "ss"; + public static final String POSTGRES_MILLISECOND_OF_MINUTE_NAME = "ms"; + public static final String POSTGRES_WEEK_OF_YEAR = "ww"; + public static final String POSTGRES_MONTH = "mm"; + public static final String POSTGRES_HALFDAY_AM = "am"; + public static final String POSTGRES_HALFDAY_PM = "pm"; + + // jodaTime patterns + public static final String JODA_FULL_NAME_OF_DAY = "EEEE"; + public static final String JODA_DAY_OF_YEAR = "D"; + public static final String JODA_DAY_OF_MONTH = "d"; + public static final String JODA_DAY_OF_WEEK = "e"; + public static final String JODA_NAME_OF_MONTH = "MMMM"; + public static final String JODA_ABR_NAME_OF_MONTH = "MMM"; + public static final String JODA_FULL_ERA_NAME = "G"; + public static final String JODA_NAME_OF_DAY = "E"; + public static final String JODA_TIME_ZONE_NAME = "TZ"; + public static final String JODA_HOUR_12_NAME = "h"; + public static final String JODA_HOUR_12_OTHER_NAME = "h"; + public static final String JODA_HOUR_24_NAME = "H"; + public static final String JODA_MINUTE_OF_HOUR_NAME = "m"; + public static final String JODA_SECOND_OF_MINUTE_NAME = "s"; + public static final String JODA_MILLISECOND_OF_MINUTE_NAME = "SSS"; + public static final String JODA_WEEK_OF_YEAR = "w"; + public static final String JODA_MONTH = "MM"; + public static final String JODA_HALFDAY = "aa"; + + static { + postgresToJodaMap.put(POSTGRES_FULL_NAME_OF_DAY, JODA_FULL_NAME_OF_DAY); + postgresToJodaMap.put(POSTGRES_DAY_OF_YEAR, JODA_DAY_OF_YEAR); + postgresToJodaMap.put(POSTGRES_DAY_OF_MONTH, JODA_DAY_OF_MONTH); + postgresToJodaMap.put(POSTGRES_DAY_OF_WEEK, JODA_DAY_OF_WEEK); + postgresToJodaMap.put(POSTGRES_NAME_OF_MONTH, JODA_NAME_OF_MONTH); + postgresToJodaMap.put(POSTGRES_ABR_NAME_OF_MONTH, JODA_ABR_NAME_OF_MONTH); + postgresToJodaMap.put(POSTGRES_FULL_ERA_NAME, JODA_FULL_ERA_NAME); + postgresToJodaMap.put(POSTGRES_NAME_OF_DAY, JODA_NAME_OF_DAY); + postgresToJodaMap.put(POSTGRES_TIME_ZONE_NAME, JODA_TIME_ZONE_NAME); + postgresToJodaMap.put(POSTGRES_HOUR_12_NAME, JODA_HOUR_12_NAME); + postgresToJodaMap.put(POSTGRES_HOUR_12_OTHER_NAME, JODA_HOUR_12_OTHER_NAME); + postgresToJodaMap.put(POSTGRES_HOUR_24_NAME, JODA_HOUR_24_NAME); + postgresToJodaMap.put(POSTGRES_MINUTE_OF_HOUR_NAME, JODA_MINUTE_OF_HOUR_NAME); + postgresToJodaMap.put(POSTGRES_SECOND_OF_MINUTE_NAME, JODA_SECOND_OF_MINUTE_NAME); + postgresToJodaMap.put(POSTGRES_MILLISECOND_OF_MINUTE_NAME, JODA_MILLISECOND_OF_MINUTE_NAME); + postgresToJodaMap.put(POSTGRES_WEEK_OF_YEAR, JODA_WEEK_OF_YEAR); + postgresToJodaMap.put(POSTGRES_MONTH, JODA_MONTH); + postgresToJodaMap.put(POSTGRES_HALFDAY_AM, JODA_HALFDAY); + postgresToJodaMap.put(POSTGRES_HALFDAY_PM, JODA_HALFDAY); + } + + static { + postgresValuesForDeleting.add(SUFFIX_SP); + postgresValuesForDeleting.add(PREFIX_FM); + postgresValuesForDeleting.add(PREFIX_FX); + postgresValuesForDeleting.add(PREFIX_TM); + } + + /** + * Replaces all postgres patterns from {@param pattern}, + * available in postgresToJodaMap keys to jodaTime equivalents. + * + * @param pattern date pattern in postgres format + * @return date pattern with replaced patterns in joda format + */ + public static String toJodaFormat(String pattern) { --- End diff -- Yes, you are right. We limited by the set of the date patterns that supports JodaTime. > 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)