CODEWITHSUNDEEP
timestamphive
ashwini
ashwini

Reputation: 531

How to Change date format in hive

I have one table in hive with column "dates"(String) and value as "5/29/2014 11:58:02 AM". I need to change the datatype from string to timestamp to process the data,I have created temporary table and trying to insert data from the original table:

create table temp as select 
 level,
 from_unixtime(unix_timestamp(dates,'YYYY-MM-DD HH:MM:SS')) as newTime,
 source,
 eventid,
 task,
 description,
 category 
from logs;

It didn't work .How to solve this? please help.

Upvotes: 1

Views: 7734

Answers (2)

Jayanth S N
Jayanth S N

Reputation: 31

I hope understanding of this below example may solve the problem

create table ex(id string,timing string,status string)
row format delimited
fields terminated by '\t'
lines terminated by '\n'
stored as textfile;

load data local inpath '/home/jayanthsn/Jay.txt' into table ex;

o/p:

User01 03-09-2015 10:00 logintime

User02 04-09-2015 10:00 logintime

User03 05-09-2015 10:00 logintime

(Bring the date format as it is with string datatype.)

Later create one more table by making that column as timestamp.

create table ex1(id string,timing timestamp,status string) row format delimited
fields terminated by '\t'
lines terminated by '\n'
stored as textfile;

insert into table ex1 select id,from_unixtime(unix_timestamp(timing,'MM-dd-yyyy HH:mm')),status from ex;

o/p:

User01 2015-03-09 10:00:00 logintime

User02 2015-04-09 10:00:00 logintime

User03 2015-05-09 10:00:00 logintime

now you can get the standard timestamp format.

Upvotes: 1

Lalit Agarwal
Lalit Agarwal

Reputation: 2354

Can you try the unix_timestamp as below and see if this works.

unix_timestamp(dates, 'MM/dd/yyyy h:mm:ss a')

To know more about date formatting check this link : http://docs.oracle.com/javase/7/docs/api/java/text/SimpleDateFormat.html

PS: I have not tried this as I do not have a hive setup in front of me now.

Upvotes: 2

Related Questions