[
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*
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.
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*
Some 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'
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]
*Functions NOT affected*
This feature will not be behind a flag for simplicity's sake
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)
* 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*
> 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
(v7.6.3#76005)
