CODEWITHSUNDEEP
hadoophive
user2902443
user2902443

Reputation: 1

Convert date '11/2/2014' (mm/d/yyyy) into timestamp in hive

I want to convert date '11/2/2014' into timestamp using Hive. This date is in string format. I tried many ways but I am getting NULL values.

Upvotes: 0

Views: 7278

Answers (2)

Samson Scharfrichter
Samson Scharfrichter

Reputation: 9067

Alternative using only String functions:

SELECT CAST(
         CONCAT(
           REGEX_REPLACE(
           REGEX_REPLACE( dirty_date
                        , '^([0-9]*)/([0-9]*)/([0-9]*)$','$3-0$1-0$2')
                        , '-0([0-9][0-9])','-$1')
          , ' 00:00:00')
       AS TIMESTAMP)
FROM dirty_input

Upvotes: 0

Sparky
Sparky

Reputation: 717

This is the syntax:

CAST(FROM_UNIXTIME(UNIX_TIMESTAMP(<date_column>,'MM/dd/yyyy')) as TIMESTAMP)

You always need to go to unix_timestamp and then cast from unix timestamp to normal timestamp. It doesn't look great, but it works.

Just to check, it might be that your syntax is right but your format is wrong. Perhaps your format is dd/MM/yyyy? That is the reason why you should give us your attempts to get it work next time.

Upvotes: 2

Related Questions