-Re: Change timestamp format in hive
Dean Wampler 2013-02-13, 14:43
I'll mention some workarounds, but they all add overhead:
1. Use STRING for the column, then parse it with the date functions
2. Use STRING, then replace the offending '-' with a space, e.g.,
select printf("%s %s", substr('2013-02-13-08:11:22', 0, 10),
substr('2013-02-13-08:11:22', 12)) as time from hadoop.stocks limit 1;
Obviously I hard coded a value for the string; you would pass in a column
You could even cast the result to TIMESTAMP:
select cast(printf("%s %s", substr('2013-02-13-08:11:22', 0, 10),
substr('2013-02-13-08:11:22', 12)) as TIMESTAMP) as time from hadoop.stocks
3. If you just need the year-month-day, i.e., the date, the to_date
function appears to work fine with these strings. However, there isn't a
corresponding to_time function for the HMS.
Ugly and not something you would want to do for every query. However, you
could hide any of these hacks behind a view.
The best thing to do would be to have your ETL process convert these
strings while loading into HDFS, if possible.
I'm experimenting with Hive v0.10, by the way.
On Wed, Feb 13, 2013 at 3:48 AM, Alexander Alten-Lorenz <[EMAIL PROTECTED]
> https://cwiki.apache.org/Hive/languagemanual-udf.html#LanguageManualUDF-DateFunctionshelp you?
> - Alex
> On Feb 13, 2013, at 10:43 AM, Chunky Gupta <[EMAIL PROTECTED]>
> > Hi,
> > I have a log file which has timestamp in format "YYYY-MM-DD-HH:MM:SS".
> But since the timestamp datatype format in hive is "YYYY-MM-DD HH:MM:SS".
> > I created a table with datatype of that column as TIMESTAMP. But when I
> load the data it is throwing error. I think it is because of difference in
> > Is there any way to set the timestamp format while creating the table.
> Or is there some other solution for this issue ?
> > Thanks,
> > Chunky.
> Alexander Alten-Lorenz
> German Hadoop LinkedIn Group: http://goo.gl/N8pCF
*Dean Wampler, Ph.D.*