Home | About | Sematext search-lucene.com search-hadoop.com
 Search Hadoop and all its subprojects:

Switch to Plain View
Hive, mail # user - Change timestamp format in hive

Chunky Gupta 2013-02-13, 09:43
Alexander Alten-Lorenz 2013-02-13, 09:48
Copy link to this message
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
Alexander mentioned.
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
limit 1;

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]
> wrote:

> May
> https://cwiki.apache.org/Hive/languagemanual-udf.html#LanguageManualUDF-DateFunctionshelp you?
> - Alex
> On Feb 13, 2013, at 10:43 AM, Chunky Gupta <[EMAIL PROTECTED]>
> wrote:
> > 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
> format.
> >
> > 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
> http://mapredit.blogspot.com
> German Hadoop LinkedIn Group: http://goo.gl/N8pCF
*Dean Wampler, Ph.D.*