Search code examples
hivetimestampsimpledateformathuehive-query

Convert String to Timestamp in Hive HQL

I'm having a string like "08/03/2018 02:00:00" which i'm trying to convert into a timestamp value.

I'm using the below code:

unix_timestamp("08/03/2018 02:00:00", "yyyy-MM-dd'T'HH:mm:ss.SSSXXX")

when i use the above code it's throwing a NULL value.

How can i convert this string to Timestamp in Hive/Hue Editor?

Solution

  • The format you specified does not match to the actual timestamp. If 08/03 in your example is dd/MM then:

    select unix_timestamp("08/03/2018 02:00:00", "dd/MM/yyyy HH:mm:ss")
OK
1520503200
Time taken: 0.299 seconds, Fetched: 1 row(s)



select from_unixtime(unix_timestamp("08/03/2018 02:00:00", "dd/MM/yyyy HH:mm:ss"))
OK
2018-03-08 02:00:00
Time taken: 0.068 seconds, Fetched: 1 row(s)

    See this answer if you want convert from ISO timestamp https://stackoverflow.com/a/23520257/2700344

    You can specify date pattern for unix_timestamp for non-standard format. See docs here: https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-DateFunctions