CODEWITHSUNDEEP
hive
Stat.Enthus
Stat.Enthus

Reputation: 335

converting timestamps in hive to date

I have this format of data in a hive column.

09/05/2015  12:45:00

I am trying to convert it to this format


20150905, or with semicolons - 2015-09-05

What is the most efficient way and without going through linux timestamp?

Upvotes: 0

Views: 1180

Answers (1)

Koushik Roy
Koushik Roy

Reputation: 7387

use date_format.
If your data is in String/Timestamp/Date format,
and you want a string of format yyyy-MM-dd, then use- date_format(date_time_col, 'yyyy-MM-dd')
and you want a date format , then use- cast( date_format(date_time_col, 'yyyy-MM-dd') as date)

EDIT In case you have a string 'MM/dd/yyyy', you need to split the string and concat them before senting them to date_format.

date_format( 
concat_ws('-', substr(split(date_time_col,'/')[2],1,4),split(date_time_col,'/')[0], split(date_time_col,'/')[1] )
, 'yyyy-MM-dd')

And you can combine all like this -

case when date_format(date_time_col, 'yyyy-MM-dd') is null 
date_format( 
concat_ws('-', substr(split(date_time_col,'/')[2],1,4),split(date_time_col,'/')[0], split(date_time_col,'/')[1] )
, 'yyyy-MM-dd') 
else date_format(date_time_col, 'yyyy-MM-dd')
end

Upvotes: 1

Related Questions