Search code examples
hivetimestamptype-conversionunix-timestamp

Convert timestamp value from string to timestamp hive

I have timestamp value stored as string in my table created in hive, and want to convert it to the timestamp type.

I tried the following code:

select date_value, FROM_UNIXTIME(UNIX_TIMESTAMP(date_value, 'dd-MMM-YY HH.mm.ss')) from sales limit 2;

Original time and result is as following:

   Original time              result

07-NOV-12 17.07.03      2012-01-01 17:07:03
25-FEB-13 04.26.53      2012-12-30 04:26:53

What's wrong in my script?

Solution

  • yy instead of YY

    select  date_value
       ,FROM_UNIXTIME(UNIX_TIMESTAMP(date_value, 'dd-MMM-yy HH.mm.ss'))  as ts

from    sales
;

    +--------------------+---------------------+
|     date_value     |         ts          |
+--------------------+---------------------+
| 07-NOV-12 17.07.03 | 2012-11-07 17:07:03 |
| 25-FEB-13 04.26.53 | 2013-02-25 04:26:53 |
+--------------------+---------------------+