Apache Hive: how to convert a string to a timestamp?

Question

I am trying to convert a row in a column REC_TIMEto a timestamp format in a bush.

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 NULL.

Answer 1

Try the following:

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

This works great if your cluster of bushes has a UTC time zone. Suppose your server is in CST, then you need to do as shown below to go 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.

JAVA . this .

Answer 2

, PDT

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

, UTC PDT 2:28:20. , . HH , hh, 3 .

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  |
+----------------------+--+