drill-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "ASF GitHub Bot (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DRILL-4864) Add ANSI format for date/time functions
Date Fri, 03 Feb 2017 11:11:52 GMT

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


> 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)

Mime
View raw message