[ https://issues.apache.org/jira/browse/DRILL-4864?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15587149#comment-15587149 ]
ASF GitHub Bot commented on DRILL-4864: --------------------------------------- Github user adeneche commented on the issue: https://github.com/apache/drill/pull/581 There seem to be an issue with milliseconds. Consider the following: ```TO_DATE('2013-01-01 12:13:14.001', 'YYYY-MM-DD HH:MI:SS:MS')``` This works fine in Postgresql but fails in Drill with the following error: ```Invalid format: "2013-01-01 12:13:14.001" is malformed at ".001"``` The issue seems to be that MS is being converted to S but it should be SSS instead > 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)