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

["Karen Coppage (JIRA)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/HIVE-21575?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Karen Coppage reassigned HIVE-21575:
------------------------------------


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