Search code examples
hiveunix-timestamp

Convert dd/mmm/yyyy to yyyy-mm-dd

I have a date in this format:

08/Jul/2012:06:19:00 -0200

and I am trying to do select like:

from_unixtime(unix_timestamp(substr(hiredate,0,10),'dd/MMM/yyyy HH:mm:ss'),'yyyy-MM-dd')

But it's not working. I want to convert it into 2012-07-08. I'm using quickstart cloudera 5.12.

Solution

  • See if this two-step process works for you:

    1. convert a string from this unusual format to unixtimestamp
    2. format the unixtimestamp (result from step 1) to a desired form 
    0: jdbc:hive2://beeline> select from_unixtime(unix_timestamp('08/Jul/2012:06:19:00 -0200','dd/MMM/yyyy:HH:mm:ss Z'),'yyyy-MM-dd') d;

+-------------+--+
|      d      |
+-------------+--+
| 2012-07-08  |
+-------------+--+
1 row selected (0.825 seconds)