Date Format Conversion in Hive

Question

I'm very new to sql/hive. At first, I loaded a txt file into hive using:

drop table if exists Tran_data;
create table Tran_data(tran_time string, 
resort string, settled double)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n';
Load data local inpath 'C:\Users\me\Documents\transaction_data.txt' into table Tran_Data;

The variable tran_time in the txt file is like this:10-APR-2014 15:01. After loading this Tran_data table, I tried to convert tran_time to a "standard" format so that I can join this table to another table using tran_time as the join key. The date format desired is 'yyyymmdd'. I searched online resources, and found this: unix_timestamp(substr(tran_time,1,11),'dd-MMM-yyyy')

So essentially, I'm doing this: unix_timestamp('10-APR-2014','dd-MMM-yyyy'). However, the output is "NULL".

So my question is: how to convert the date format to a "standard" format, and then further convert it to 'yyyymmdd' format?

Answer 1

unix_timestamp('2014-05-01','dd-mmm-yyyy') will work, your input string should be in this format for hive yyyy-mm-dd or yyyy-mm-dd hh:mm:ss

Where as you are trying with '01-MAY-2014' hive won't understand it as a date string

Answer 2

unix_timestamp function will convert given string date format to unix timestamp in seconds , but not like this format dd-mm-yyyy.

You need to write your own custom udf to convert a given string date to the format that you need as present Hive do not have any predefined functions. We have to_date function to convert a timestamp to date , remaining all unix_timestamp functions won't help your problem.

Answer 3

select from_unixtime(unix_timestamp('01032018' ,'MMddyyyy'), 'yyyyMMdd');

input format: mmddyyyy

01032018

output after query: yyyymmdd

20180103

Answer 4

To help someone in the future:

The following function should work as it worked in my case

to_date(from_unixtime(UNIX_TIMESTAMP('10-APR-2014','dd-MMM-yyyy'))

Answer 5

My current Hive Version: Hive 0.12.0-cdh5.1.5

I converted datetime in first column to date in second column using the below hive date functions. Hope this helps!

select inp_dt, from_unixtime(unix_timestamp(substr(inp_dt,0,11),'dd-MMM-yyyy')) as todateformat from table;

inp_dt todateformat
12-Mar-2015 07:24:55 2015-03-12 00:00:00

Answer 6

from_unixtime(unix_timestamp('20150101' ,'yyyyMMdd'), 'yyyy-MM-dd')