[
https://issues.apache.org/jira/browse/HIVE-21575?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Karen Coppage resolved HIVE-21575.
----------------------------------
Resolution: Done
> 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*
> Some conflicting examples:
> SimpleDateTime: 'MMM dd, yyyy HH:mm:ss'
> SQL:2016: 'mon dd, yyyy hh24:mi:ss'
> SimpleDateTime: 'yyyy-MM-dd HH:mm:ss'
> SQL:2016: 'yyyy-mm-dd hh24:mi:ss'
> 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]
> *Continued usage of SimpleDateFormat patterns*
> [Update] This feature will NOT be behind a flag in order to keep things
> simple for users. Existing Hive functions that accept SimpleDateFormat
> patterns as input will continue to do so. Please let me know if you disagree
> with this decision. These are the functions (afaik) affected:
> * 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)
> * 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
(v8.3.2#803003)
