[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=15537662#comment-15537662 ] 

ASF GitHub Bot commented on DRILL-4864:
---------------------------------------

Github user gparai commented on a diff in the pull request:

    https://github.com/apache/drill/pull/581#discussion_r81438289
  
    --- Diff: exec/java-exec/src/main/java/org/apache/drill/exec/planner/logical/DrillOptiq.java ---
    @@ -408,6 +411,12 @@ private LogicalExpression getDrillFunctionFromOptiqCall(RexCall call) {
     
               return first;
             }
    +      } else if (functionName.equals("to_date") || functionName.equals("to_time") || functionName.equals("to_timestamp")) {
    +        // convert ansi date format string to joda according to session option
    +        OptionManager om = this.context.getPlannerSettings().getOptions();
    +        if(ToDateFormats.valueOf(om.getOption(ExecConstants.TO_DATE_FORMAT).string_val.toUpperCase()).equals(ToDateFormats.ANSI)) {
    --- End diff --
    
    if (


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