[
https://issues.apache.org/jira/browse/HIVE-16434?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16062447#comment-16062447
]
Carter Shanklin edited comment on HIVE-16434 at 6/25/17 10:33 PM:
------------------------------------------------------------------
SQL:2016 adds parsing templates and a format keyword as:
CAST(<datetime> AS <char string type> [FORMAT <template>])
CAST(<char string> AS <datetime type> [FORMAT <template>])
The formats include:
YYYY | YYY | YY | Y: Year
RRRR | RR: Rounded Year
MM: Month
DD: Day of month
DDD: Day of year
HH | HH12: 12 hour
HH24: 24 hour
MI: Minute
SS: Second of minute
SSSSS: Second of day
FF1 | … | FF9: Fraction
A.M. | P.M.: AM or PM
TZH: Time zone hour
TZM: Time zone minute
So if I had source data of the form month/day/year with 2-digit years in a CSV
file I could import it using
... cast(column as date format 'MM/DD/YY') ...
Which is a fairly straightforward thing to do, although maybe not as automated
as what you get with other DBs. This approach is worth considering as it is
standardized now and we should see other databases begin to adopt it.
See also: https://modern-sql.com/blog/2017-06/whats-new-in-sql-2016
was (Author: cartershanklin):
SQL:2016 adds parsing templates and a format keyword as:
CAST(<datetime> AS <char string type> [FORMAT <template>])
CAST(<char string> AS <datetime type> [FORMAT <template>])
The formats include:
YYYY | YYY | YY | Y: Year
RRRR | RR: Rounded Year
MM: Month
DD: Day of month
DDD: Day of year
HH | HH12: 12 hour
HH24: 24 hour
MI: Minute
SS: Second of minute
SSSSS: Second of day
FF1 | … | FF9: Fraction
A.M. | P.M.: AM or PM
TZH: Time zone hour
TZM: Time zone minute
So if I had source data of the form month/day/year with 2-digit years in a CSV
file I could import it using
... cast(column as date format 'MM/DD/YY') ...
Which is a fairly straightforward thing to do, although maybe not as automated
as what you get with other DBs. This approach is worth considering as it is
standardized now and we should see other databases begin to adopt it.
> Add support for parsing additional timestamp formats
> ----------------------------------------------------
>
> Key: HIVE-16434
> URL: https://issues.apache.org/jira/browse/HIVE-16434
> Project: Hive
> Issue Type: Bug
> Components: File Formats, Query Planning
> Reporter: Ashutosh Chauhan
>
> Will be useful to handle additional timestamp formats.
--
This message was sent by Atlassian JIRA
(v6.4.14#64029)
