Search code examples
hive

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?

Solution

  • 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