[ https://issues.apache.org/jira/browse/DRILL-4864?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15851319#comment-15851319 ]
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_r99312091 --- 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); + String pattern = org.apache.drill.common.expression.fn.JodaDateValidator.toJodaFormat(formatString); + format = org.joda.time.format.DateTimeFormat.forPattern(pattern); + } + + public void eval() { + // Get the input + byte[] buf1 = new byte[left.end - left.start]; + left.buffer.getBytes(left.start, buf1, 0, left.end - left.start); + String input = new String(buf1, com.google.common.base.Charsets.UTF_8); + + <#if type == "Date"> + out.value = (org.joda.time.DateMidnight.parse(input, format).withZoneRetainFields(org.joda.time.DateTimeZone.UTC)).getMillis(); + <#elseif type == "TimeStamp"> + out.value = org.joda.time.DateTime.parse(input, format).withZoneRetainFields(org.joda.time.DateTimeZone.UTC).getMillis(); + <#elseif type == "Time"> + out.value = (int) ((format.parseDateTime(input)).withZoneRetainFields(org.joda.time.DateTimeZone.UTC).getMillis()); + </#if> --- End diff -- Yes, we should. Fixed, thanks. > 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)