hive把字符串转换为时间,在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?

解决方案

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