Search code examples
hiveunix-timestampto-date

convert date in 2015-05-09T09:00:12.123462000 to unix timestamp in hive

I want to convert the date which is in '2015-05-09T09:00:12.123462000' format to the unix timestamp in hive. The UNIX_TIMESTAMP('2015-05-09T09:00:12.123462000') doesn't work. I am not sure how i can convert this. I need this to compare two dates in different format. I am converting both the dates to unix timestamp but this fails. can someone please help with this.

Thanks

Solution

  • Your input uses the full ISO 8601 format, with a "T" between date and time, and fractional seconds. Hive expects an SQL format (i.e. with a space between date and time) as seen in java.sql.Timestamp and ODBC, with or without fractional seconds, as stated in the Hive documentation.

    Just apply some very elementary string massaging -- then "cast" the String to a Hive Timestamp. And pleeease forget that lame roundtrip to and from UNIX_TIMESTAMP:

    cast(regexp_replace('2015-05-09T09:00:12.123462000', 'T',' ') as Timestamp)