[
https://issues.apache.org/jira/browse/DRILL-4864?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15633845#comment-15633845
]
Paul Rogers commented on DRILL-4864:
------------------------------------
I wonder about the approach taken here. First, it is not clear that there is a
standard "ANSI" date format... Consider Oracle's format
(https://www.techonthenet.com/oracle/functions/to_date.php, IBM's DB2 format
(http://www.ibm.com/support/knowledgecenter/SSEPEK_10.0.0/sqlref/src/tpc/db2z_bif_timestampformat.html)
or the Postgres format
(https://www.postgresql.org/docs/8.2/static/functions-formatting.html).
Second, changing the format of an existing function will break existing queries.
Third, working around that problem by having a global option is awkward: this
means that all users of a Drill cluster must agree on date format. Further, any
stored queries that use the "Joda" format would have to be rewritten when the
setting changes. Such a change can thus never be made for existing
installations. To prevent data corruption (queries that no longer work) new
installs must be backward compatible and use the old formats. So, only new
installs that know how to set the option could use the "ANSI" formats.
Fourth, the "ANSI" name seems to refer to a specific format (ISO 8601:
https://en.wikipedia.org/wiki/ISO_8601). I can't find references to an ANSI
standard for the format strings. Instead, it seems that there is an Oracle
standard, an IBM standard, etc.
Is there a better way?
Two possibilities.
First, leave the to_date, etc. functions unchanged. They use the Java (formerly
Joda) formats. (Note, Joda is deprecated. Java 8 includes the former Joda
classes in the JDK. Java 7 was the last version that needed Joda, but Java 7
went end-of-life in April, 2015.)
Introduce a new function that takes a new format. Maybe call it sql_to_date,
then pick one of the Oracle/DB2/Postgress/etc. dialects to support.
The second option is modify to_date to take a prefix:
"java:M/D/y" or
"sql:MM/DD/YY"
The default would be "java:" for backward compatibility.
The sql_to_date approach is probably cleaner.
In either case, we have to define the new date/time format strings (or claim we
support some vendor's format.) It appears that the code supports the Postgres
format. How does that differ from the Oracle format?
> 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: 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)
