[jira] [Commented] (HIVE-21575) Add support for SQL:2016 datetime templates/patterns/masks and CAST(... AS ... FORMAT )

["Gabor Kaszab (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/HIVE-21575?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16810786#comment-16810786 ] 

Gabor Kaszab commented on HIVE-21575:
-------------------------------------

[~klcopp] Thanks for taking care of this!

I have one note: "We will have a session-level feature flag to revert to the legacy Java SimpleDateFormat patterns." This is the other way around. The default for existing functions is to use SimpleDateFormat and using the flag can be changed to use the new SQL:2016 formats. (CAST ..FORMAT.. uses the new approach only.)

> Add support for SQL:2016 datetime templates/patterns/masks and CAST(... AS ... FORMAT <pattern>)
> ------------------------------------------------------------------------------------------------
>
>                 Key: HIVE-21575
>                 URL: https://issues.apache.org/jira/browse/HIVE-21575
>             Project: Hive
>          Issue Type: Improvement
>            Reporter: Karen Coppage
>            Assignee: Karen Coppage
>            Priority: Major
>              Labels: SQL, datetime
>
> *Summary*
>  Timestamp and date handling and formatting is currently implemented in Hive using (sometimes very specific) [Java SimpleDateFormat patterns|http://docs.oracle.com/javase/8/docs/api/java/text/SimpleDateFormat.html] , however, it is not what most standard SQL systems use. For example see [Vertica|https://my.vertica.com/docs/7.2.x/HTML/Content/Authoring/SQLReferenceManual/Functions/Formatting/TemplatePatternsForDateTimeFormatting.htm], [Netezza|http://www.ibm.com/support/knowledgecenter/SSULQD_7.2.1/com.ibm.nz.dbu.doc/r_dbuser_ntz_sql_extns_templ_patterns_date_time_conv.html], [Oracle|https://docs.oracle.com/database/121/SQLRF/sql_elements004.htm#SQLRF00212], and [PostgreSQL|https://www.postgresql.org/docs/9.5/static/functions-formatting.html#FUNCTIONS-FORMATTING-DATETIME-TABLE].
> *Cast...Format*
> SQL:2016 also introduced the FORMAT clause for CAST which is the standard way to do string <-> datetime conversions
> For example:
> {code:java}
> CAST(<datetime> AS <char string type> [FORMAT <template>])
> CAST(<char string> AS <datetime type> [FORMAT <template>])
> cast(dt as string format 'DD-MM-YYYY')
> cast('01-05-2017' as date format 'DD-MM-YYYY')
> {code}
> [Stuff like this|http://bigdataprogrammers.com/string-date-conversion-hive/] wouldn't need to happen.
> *New SQL:2016 Patterns*
> Examples:
> {code:java}
> --old SimpleDateFormat
> select date_format('2015-05-15 12:00:00', 'MMM dd, yyyy HH:mm:ss');
> --new SQL:2016 format
> select date_format('2015-05-15 12:00:00', 'mon dd, yyyy hh:mi:ss');
> {code}
> Some other conflicting examples:
> SimpleDateTime: 'MMM dd, yyyy HH:mm:ss'
>  SQL:2016: 'mon dd, yyyy hh:mi:ss'
> SimpleDateTime: 'yyyy-MM-dd HH:mm:ss'
>  SQL:2016: 'yyyy-mm-dd hh24:mi:ss'
> We will have a session-level feature flag to revert to the legacy Java SimpleDateFormat patterns. This would allow users to chose the behavior they desire and scope it to a session if need be.
> For the full list of patterns, see subsection "Proposal for Impala’s datetime patterns" in this doc: [https://docs.google.com/document/d/1V7k6-lrPGW7_uhqM-FhKl3QsxwCRy69v2KIxPsGjc1k/edit]
> *Existing Hive functions affected*
> Other functions use SimpleDateFormat internally; these are the ones afaik where SimpleDateFormat or some similar format is part of the input:
>  * from_unixtime(bigint unixtime[, string format])
>  * unix_timestamp(string date, string pattern)
>  * to_unix_timestamp(date[, pattern])
>  * add_months(string start_date, int num_months, output_date_format)
>  * trunc(string date, string format) - currently only supports 'MONTH'/'MON'/'MM', 'QUARTER'/'Q' and 'YEAR'/'YYYY'/'YY' as format.
>  * date_format(date/timestamp/string ts, string fmt)
> This description is a heavily edited description of IMPALA-4018.



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)