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

["ASF GitHub Bot (JIRA)" <ji...@apache.org>]

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