[
https://issues.apache.org/jira/browse/DRILL-4864?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Serge Harnyk updated DRILL-4864:
--------------------------------
Description:
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 border="0" cellspacing="0" cellpadding="0" class="Table1"><colgroup><col
width="229"/><col width="250"/><col width="264"/></colgroup><tr><td
style="text-align:left;width:2.0625in; " class="Table1_A1"><p
class="P1">Pattern name</p></td><td style="text-align:left;width:2.25in; "
class="Table1_A1"><p class="P3">Ansi format</p></td><td
style="text-align:left;width:2.375in; " class="Table1_A1"><p
class="P3">JodaTime format</p></td></tr><tr><td
style="text-align:left;width:2.0625in; " class="Table1_A1"><p class="P1">FULL
NAME OF DAY</p></td><td style="text-align:left;width:2.25in; "
class="Table1_A1"><p class="P3">day</p></td><td
style="text-align:left;width:2.375in; " class="Table1_A1"><p
class="P3">EEEE</p></td></tr><tr><td style="text-align:left;width:2.0625in; "
class="Table1_A1"><p class="P1">DAY OF YEAR</p></td><td
style="text-align:left;width:2.25in; " class="Table1_A1"><p
class="P3">ddd</p></td><td style="text-align:left;width:2.375in; "
class="Table1_A1"><p class="P3">D</p></td></tr><tr><td
style="text-align:left;width:2.0625in; " class="Table1_A1"><p class="P1">DAY OF
MONTH</p></td><td style="text-align:left;width:2.25in; " class="Table1_A1"><p
class="P3">dd</p></td><td style="text-align:left;width:2.375in; "
class="Table1_A1"><p class="P3">d</p></td></tr><tr><td
style="text-align:left;width:2.0625in; " class="Table1_A1"><p class="P1">DAY OF
WEEK</p></td><td style="text-align:left;width:2.25in; " class="Table1_A1"><p
class="P3">d</p></td><td style="text-align:left;width:2.375in; "
class="Table1_A1"><p class="P3">e</p></td></tr><tr><td
style="text-align:left;width:2.0625in; " class="Table1_A1"><p class="P1">NAME
OF MONTH</p></td><td style="text-align:left;width:2.25in; "
class="Table1_A1"><p class="P3">month</p></td><td
style="text-align:left;width:2.375in; " class="Table1_A1"><p
class="P3">MMMM</p></td></tr><tr><td style="text-align:left;width:2.0625in; "
class="Table1_A1"><p class="P1">ABR NAME OF MONTH</p></td><td
style="text-align:left;width:2.25in; " class="Table1_A1"><p
class="P3">mon</p></td><td style="text-align:left;width:2.375in; "
class="Table1_A1"><p class="P3">MMM</p></td></tr><tr><td
style="text-align:left;width:2.0625in; " class="Table1_A1"><p class="P1">FULL
ERA NAME</p></td><td style="text-align:left;width:2.25in; "
class="Table1_A1"><p class="P3">ee</p></td><td
style="text-align:left;width:2.375in; " class="Table1_A1"><p
class="P3">G</p></td></tr><tr><td style="text-align:left;width:2.0625in; "
class="Table1_A1"><p class="P1">NAME OF DAY</p></td><td
style="text-align:left;width:2.25in; " class="Table1_A1"><p
class="P3">dy</p></td><td style="text-align:left;width:2.375in; "
class="Table1_A1"><p class="P3">E</p></td></tr><tr><td
style="text-align:left;width:2.0625in; " class="Table1_A1"><p class="P1">TIME
ZONE</p></td><td style="text-align:left;width:2.25in; " class="Table1_A1"><p
class="P3">tz</p></td><td style="text-align:left;width:2.375in; "
class="Table1_A1"><p class="P3">TZ</p></td></tr><tr><td
style="text-align:left;width:2.0625in; " class="Table1_A1"><p class="P1">HOUR
12 </p></td><td style="text-align:left;width:2.25in; " class="Table1_A1"><p
class="P3">hh</p></td><td style="text-align:left;width:2.375in; "
class="Table1_A1"><p class="P3">h</p></td></tr><tr><td
style="text-align:left;width:2.0625in; " class="Table1_A1"><p class="P1">HOUR
12 </p></td><td style="text-align:left;width:2.25in; " class="Table1_A1"><p
class="P3">hh12</p></td><td style="text-align:left;width:2.375in; "
class="Table1_A1"><p class="P3">h</p></td></tr><tr><td
style="text-align:left;width:2.0625in; " class="Table1_A1"><p class="P1">HOUR
24</p></td><td style="text-align:left;width:2.25in; " class="Table1_A1"><p
class="P3">hh24</p></td><td style="text-align:left;width:2.375in; "
class="Table1_A1"><p class="P3">H</p></td></tr><tr><td
style="text-align:left;width:2.0625in; " class="Table1_A1"><p class="P1">MINUTE
OF HOUR</p></td><td style="text-align:left;width:2.25in; " class="Table1_A1"><p
class="P3">mi</p></td><td style="text-align:left;width:2.375in; "
class="Table1_A1"><p class="P3">m</p></td></tr><tr><td
style="text-align:left;width:2.0625in; " class="Table1_A1"><p class="P1">SECOND
OF MINUTE</p></td><td style="text-align:left;width:2.25in; "
class="Table1_A1"><p class="P3">ss</p></td><td
style="text-align:left;width:2.375in; " class="Table1_A1"><p
class="P3">s</p></td></tr><tr><td style="text-align:left;width:2.0625in; "
class="Table1_A1"><p class="P1">MILLISECOND OF MINUTE</p></td><td
style="text-align:left;width:2.25in; " class="Table1_A1"><p
class="P3">ms</p></td><td style="text-align:left;width:2.375in; "
class="Table1_A1"><p class="P3">S</p></td></tr><tr><td
style="text-align:left;width:2.0625in; " class="Table1_A1"><p class="P1">WEEK
OF YEAR</p></td><td style="text-align:left;width:2.25in; " class="Table1_A1"><p
class="P3">ww</p></td><td style="text-align:left;width:2.375in; "
class="Table1_A1"><p class="P3">w</p></td></tr><tr><td
style="text-align:left;width:2.0625in; " class="Table1_A1"><p
class="P1">MONTH</p></td><td style="text-align:left;width:2.25in; "
class="Table1_A1"><p class="P3">mm</p></td><td
style="text-align:left;width:2.375in; " class="Table1_A1"><p
class="P3">MM</p></td></tr><tr><td style="text-align:left;width:2.0625in; "
class="Table1_A1"><p class="P1">HALFDAY AM</p></td><td
style="text-align:left;width:2.25in; " class="Table1_A1"><p
class="P3">am</p></td><td style="text-align:left;width:2.375in; "
class="Table1_A1"><p class="P3">aa</p></td></tr><tr><td
style="text-align:left;width:2.0625in; " class="Table1_A1"><p
class="P1">HALFDAY PM</p></td><td style="text-align:left;width:2.25in; "
class="Table1_A1"><p class="P3">pm</p></td><td
style="text-align:left;width:2.375in; " class="Table1_A1"><p
class="P3">aa</p></td></tr><tr><td style="text-align:left;width:2.0625in; "
class="Table1_A21"><p class="P2">ref.</p></td><td
style="text-align:left;width:2.25in; " class="Table1_A21"><p
class="P4">https://www.postgresql.org/docs/8.2/static/functions-formatting.html</p></td><td
style="text-align:left;width:2.375in; " class="Table1_A21"><p
class="P4">http://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html</p></td></tr></table>
was:
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.
> 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: Serge Harnyk
> Assignee: Serge Harnyk
> Fix For: 1.9.0
>
> Attachments: jodatime_ansi.odt
>
>
> 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 border="0" cellspacing="0" cellpadding="0"
> class="Table1"><colgroup><col width="229"/><col width="250"/><col
> width="264"/></colgroup><tr><td style="text-align:left;width:2.0625in; "
> class="Table1_A1"><p class="P1">Pattern name</p></td><td
> style="text-align:left;width:2.25in; " class="Table1_A1"><p class="P3">Ansi
> format</p></td><td style="text-align:left;width:2.375in; "
> class="Table1_A1"><p class="P3">JodaTime format</p></td></tr><tr><td
> style="text-align:left;width:2.0625in; " class="Table1_A1"><p class="P1">FULL
> NAME OF DAY</p></td><td style="text-align:left;width:2.25in; "
> class="Table1_A1"><p class="P3">day</p></td><td
> style="text-align:left;width:2.375in; " class="Table1_A1"><p
> class="P3">EEEE</p></td></tr><tr><td style="text-align:left;width:2.0625in; "
> class="Table1_A1"><p class="P1">DAY OF YEAR</p></td><td
> style="text-align:left;width:2.25in; " class="Table1_A1"><p
> class="P3">ddd</p></td><td style="text-align:left;width:2.375in; "
> class="Table1_A1"><p class="P3">D</p></td></tr><tr><td
> style="text-align:left;width:2.0625in; " class="Table1_A1"><p class="P1">DAY
> OF MONTH</p></td><td style="text-align:left;width:2.25in; "
> class="Table1_A1"><p class="P3">dd</p></td><td
> style="text-align:left;width:2.375in; " class="Table1_A1"><p
> class="P3">d</p></td></tr><tr><td style="text-align:left;width:2.0625in; "
> class="Table1_A1"><p class="P1">DAY OF WEEK</p></td><td
> style="text-align:left;width:2.25in; " class="Table1_A1"><p
> class="P3">d</p></td><td style="text-align:left;width:2.375in; "
> class="Table1_A1"><p class="P3">e</p></td></tr><tr><td
> style="text-align:left;width:2.0625in; " class="Table1_A1"><p class="P1">NAME
> OF MONTH</p></td><td style="text-align:left;width:2.25in; "
> class="Table1_A1"><p class="P3">month</p></td><td
> style="text-align:left;width:2.375in; " class="Table1_A1"><p
> class="P3">MMMM</p></td></tr><tr><td style="text-align:left;width:2.0625in; "
> class="Table1_A1"><p class="P1">ABR NAME OF MONTH</p></td><td
> style="text-align:left;width:2.25in; " class="Table1_A1"><p
> class="P3">mon</p></td><td style="text-align:left;width:2.375in; "
> class="Table1_A1"><p class="P3">MMM</p></td></tr><tr><td
> style="text-align:left;width:2.0625in; " class="Table1_A1"><p class="P1">FULL
> ERA NAME</p></td><td style="text-align:left;width:2.25in; "
> class="Table1_A1"><p class="P3">ee</p></td><td
> style="text-align:left;width:2.375in; " class="Table1_A1"><p
> class="P3">G</p></td></tr><tr><td style="text-align:left;width:2.0625in; "
> class="Table1_A1"><p class="P1">NAME OF DAY</p></td><td
> style="text-align:left;width:2.25in; " class="Table1_A1"><p
> class="P3">dy</p></td><td style="text-align:left;width:2.375in; "
> class="Table1_A1"><p class="P3">E</p></td></tr><tr><td
> style="text-align:left;width:2.0625in; " class="Table1_A1"><p class="P1">TIME
> ZONE</p></td><td style="text-align:left;width:2.25in; " class="Table1_A1"><p
> class="P3">tz</p></td><td style="text-align:left;width:2.375in; "
> class="Table1_A1"><p class="P3">TZ</p></td></tr><tr><td
> style="text-align:left;width:2.0625in; " class="Table1_A1"><p class="P1">HOUR
> 12 </p></td><td style="text-align:left;width:2.25in; " class="Table1_A1"><p
> class="P3">hh</p></td><td style="text-align:left;width:2.375in; "
> class="Table1_A1"><p class="P3">h</p></td></tr><tr><td
> style="text-align:left;width:2.0625in; " class="Table1_A1"><p class="P1">HOUR
> 12 </p></td><td style="text-align:left;width:2.25in; " class="Table1_A1"><p
> class="P3">hh12</p></td><td style="text-align:left;width:2.375in; "
> class="Table1_A1"><p class="P3">h</p></td></tr><tr><td
> style="text-align:left;width:2.0625in; " class="Table1_A1"><p class="P1">HOUR
> 24</p></td><td style="text-align:left;width:2.25in; " class="Table1_A1"><p
> class="P3">hh24</p></td><td style="text-align:left;width:2.375in; "
> class="Table1_A1"><p class="P3">H</p></td></tr><tr><td
> style="text-align:left;width:2.0625in; " class="Table1_A1"><p
> class="P1">MINUTE OF HOUR</p></td><td style="text-align:left;width:2.25in; "
> class="Table1_A1"><p class="P3">mi</p></td><td
> style="text-align:left;width:2.375in; " class="Table1_A1"><p
> class="P3">m</p></td></tr><tr><td style="text-align:left;width:2.0625in; "
> class="Table1_A1"><p class="P1">SECOND OF MINUTE</p></td><td
> style="text-align:left;width:2.25in; " class="Table1_A1"><p
> class="P3">ss</p></td><td style="text-align:left;width:2.375in; "
> class="Table1_A1"><p class="P3">s</p></td></tr><tr><td
> style="text-align:left;width:2.0625in; " class="Table1_A1"><p
> class="P1">MILLISECOND OF MINUTE</p></td><td
> style="text-align:left;width:2.25in; " class="Table1_A1"><p
> class="P3">ms</p></td><td style="text-align:left;width:2.375in; "
> class="Table1_A1"><p class="P3">S</p></td></tr><tr><td
> style="text-align:left;width:2.0625in; " class="Table1_A1"><p class="P1">WEEK
> OF YEAR</p></td><td style="text-align:left;width:2.25in; "
> class="Table1_A1"><p class="P3">ww</p></td><td
> style="text-align:left;width:2.375in; " class="Table1_A1"><p
> class="P3">w</p></td></tr><tr><td style="text-align:left;width:2.0625in; "
> class="Table1_A1"><p class="P1">MONTH</p></td><td
> style="text-align:left;width:2.25in; " class="Table1_A1"><p
> class="P3">mm</p></td><td style="text-align:left;width:2.375in; "
> class="Table1_A1"><p class="P3">MM</p></td></tr><tr><td
> style="text-align:left;width:2.0625in; " class="Table1_A1"><p
> class="P1">HALFDAY AM</p></td><td style="text-align:left;width:2.25in; "
> class="Table1_A1"><p class="P3">am</p></td><td
> style="text-align:left;width:2.375in; " class="Table1_A1"><p
> class="P3">aa</p></td></tr><tr><td style="text-align:left;width:2.0625in; "
> class="Table1_A1"><p class="P1">HALFDAY PM</p></td><td
> style="text-align:left;width:2.25in; " class="Table1_A1"><p
> class="P3">pm</p></td><td style="text-align:left;width:2.375in; "
> class="Table1_A1"><p class="P3">aa</p></td></tr><tr><td
> style="text-align:left;width:2.0625in; " class="Table1_A21"><p
> class="P2">ref.</p></td><td style="text-align:left;width:2.25in; "
> class="Table1_A21"><p
> class="P4">https://www.postgresql.org/docs/8.2/static/functions-formatting.html</p></td><td
> style="text-align:left;width:2.375in; " class="Table1_A21"><p
> class="P4">http://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html</p></td></tr></table>
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
