[ https://issues.apache.org/jira/browse/DRILL-4864?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15585652#comment-15585652 ]
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_r83871296 --- Diff: exec/java-exec/src/main/java/org/apache/drill/exec/expr/fn/impl/AnsiToJoda.java --- @@ -0,0 +1,58 @@ +/* +* 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.exec.expr.fn.impl; + +import io.netty.buffer.DrillBuf; +import org.apache.drill.exec.expr.DrillSimpleFunc; +import org.apache.drill.exec.expr.annotations.FunctionTemplate; +import org.apache.drill.exec.expr.annotations.Output; +import org.apache.drill.exec.expr.annotations.Param; +import org.apache.drill.exec.expr.holders.VarCharHolder; + +import javax.inject.Inject; + +/** + * Replaces all ansi patterns to joda equivalents. + */ +@FunctionTemplate(name = "ansi_to_joda", + scope = FunctionTemplate.FunctionScope.SIMPLE, + nulls= FunctionTemplate.NullHandling.NULL_IF_NULL) +public class AnsiToJoda implements DrillSimpleFunc { + + @Param + VarCharHolder in; + + @Output + VarCharHolder out; + + @Inject + DrillBuf buffer; + + @Override + public void setup() { + } + + @Override + public void eval() { + String pattern = org.apache.drill.exec.expr.fn.impl.StringFunctionHelpers.toStringFromUTF8(in.start, in.end, in.buffer); --- End diff -- Datetime patterns validation isn't a simple task. For example JODA Time has no validation. > 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)