Karen Coppage created HIVE-21575:
------------------------------------
Summary: 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
*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)
