Apache Hive: How to convert string to timestamp?

Question

I'm trying to convert the string in REC_TIME column to a timestamp format in hive.

Ex: Sun Jul 31 09:28:20 UTC 2016 => 2016-07-31 09:28:20

SELECT xxx, UNIX_TIMESTAMP(REC_TIME, "E M dd HH:mm:ss z yyyy") FROM wlogs LIMIT 10;

When I execute the above SQL it returns a NULL value.

Solution 1

Try this :

select from_unixtime(unix_timestamp("Sun Jul 31 09:28:20 UTC 2016","EEE MMM dd HH:mm:ss zzz yyyy"));

This works fine if your hive cluster has UTC timezone. Say suppose your server is in CST then you need to do as below to get to UTC;

select to_utc_timestamp(from_unixtime(unix_timestamp("Sun Jul 31 09:28:20 UTC 2016","EEE MMM dd HH:mm:ss zzz yyyy")),'CST');

Hope this helps.

EDIT Hive date functions use the JAVA simple date formater for the patterns . Refer this for the patterns.

Solution 2

Be aware my computers runs on PDT

[cloudera@quickstart ~]$ date +%Z
PDT

So the UTC time is converted to 2:28:20 PDT. Anyway this is not the point. You are using HH for hours, use hh and you need at least 3 M for the month.

0: jdbc:hive2://quickstart:10000/default> select from_unixtime(unix_timestamp("Sun Jul 31 09:28:20 UTC 2016", 'E MMM dd hh:mm:ss z yyyy')) as date;
+----------------------+--+
|         date         |
+----------------------+--+
| 2016-07-31 02:28:20  |
+----------------------+--+