Hive database convert epochtime into YYYY-MM-DD format

0
votes

I have to access a hive database.In that database the time is stored as epochtime inside a bigint column.I retrive the data in yyyy-mm-dd format.Can someone please help me with this

Table description

temp_table name string ts bigint age int

ts column stores the data in epoch time stamp format

when I give select * from temp_table

values retrived are

bob 1374752536 12

I need the output as

bob 2013-07-25 12:14:17 12

1
hive

1 Answers

0
votes

You could make use of from_unixtime() date function provided with Hive. It converts the timestamp to a string representing the that timestamp.

Usage :

hive> select from_unixtime(1374752536) from demo;

Example :

Input :

bob 1374752536 12
tariq 1374778369 25

Query :

hive> create external table demo2(name string, ts bigint, age int) row format delimited fields terminated by ' ' location '/inputs/date/';

hive> select from_unixtime(ts) from demo2;

Output :

OK

2013-07-25 17:12:16 
2013-07-26 00:22:49 
Time taken: 6.3 seconds

HTH