[
https://issues.apache.org/jira/browse/HIVE-21575?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Karen Coppage updated HIVE-21575:
---------------------------------
Description:
*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'
The SQL:2016 formats will be behind a session-level feature flag. Default
formats will be 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.
was:
*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.
> 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'
> The SQL:2016 formats will be behind a session-level feature flag. Default
> formats will be 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)
