How to convert date

0
votes

I have date in table as Sep 1 2017 2:00 PM as actualshipdate I want to convert it as 01-09-2017 in hive I try with below command but is showing null select actualshipdate,from_unixtime(unix_timestamp(substr(actualshipdate,0,11), 'dd-mm-yyyy')) as newdate from tablename;

2
hivehiveql
stackoverflow.com/questions/25189790/… – The beginner

2 Answers

-3
votes

You are using correct function but the parameters are wrong . The parameters should be unix_timestamp(datestring, date_format) ; this function will convert date to unix date format which you can format further by using from_unixtime(unixdateformat,format_youneed);

hive> select unix_timestamp('Sep 1 2017 2:00 PM' ,'MMM dd yyyy HH:mm a');
OK
1504256400

You need specific pattern of date for this you can use function 

from_unixtime(unixdateformat,format_youneed);

hive> select from_unixtime(1504256400,'dd-MM-yyyy');
OK
01-09-2017


hive>  select from_unixtime(UNIX_TIMESTAMP('Sep 1 2017 2:00 PM','MMM dd yyyy 
HH:mm a'), 'dd-MM-yyyy');
OK
01-09-2017
Time taken: 0.135 seconds, Fetched: 1 row(s)

As you have date stored in actualdate in the table you can use below command to get the result.

**hive>  select from_unixtime(UNIX_TIMESTAMP(actualshipdate,'MMM dd yyyy HH:mm a'), 'dd-MM-yyyy') from tablename;**
0
votes

Use unix_timestamp(string date, string pattern) to convert given date format to seconds passed from 1970-01-01. Then use from_unixtime() to convert to given format:

hive> select from_unixtime(unix_timestamp('Sep 1 2017 2:00 PM' ,'MMM dd yyyy HH:mm a'), 'dd-MM-yyyy');
OK
01-09-2017
Time taken: 0.049 seconds, Fetched: 1 row(s)

See patterns examples here: https://docs.oracle.com/javase/7/docs/api/java/text/SimpleDateFormat.html