Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

2018- to convert into hive

Solved Go to solution
Highlighted

2018- to convert into hive

New Contributor
 
1 ACCEPTED SOLUTION

Accepted Solutions

Re: 2018- to convert into hive

Super Guru

@Gayathri Devi

Could you try with below query as we are reading 2018-02-27T02:00 value and converting as timestamp value.

Query:-

hive> select from_unixtime(unix_timestamp('2018-02-27T02:00',"yyyy-MM-dd'T'hh:mm"),'yyyy-MM-dd hh:mm:ss');
+----------------------+--+
|         _c0          |
+----------------------+--+
| 2018-02-27 02:00:00  |
+----------------------+--+

(or)

By using regexp_replace function we can replace T in your timestamp value

hive> select regexp_replace('2018-02-27T02:00','T',' ');
+-------------------+--+
|        _c0        |
+-------------------+--+
| 2018-02-27 02:00  |
+-------------------+--+

And use concat function to add missing :00 value to make above value as hive timestamp.

hive> select concat(regexp_replace('2018-02-27T02:00','T',' '),":00");
+----------------------+--+
|         _c0          |
+----------------------+--+
| 2018-02-27 02:00:00  |
+----------------------+--+
1 REPLY 1

Re: 2018- to convert into hive

Super Guru

@Gayathri Devi

Could you try with below query as we are reading 2018-02-27T02:00 value and converting as timestamp value.

Query:-

hive> select from_unixtime(unix_timestamp('2018-02-27T02:00',"yyyy-MM-dd'T'hh:mm"),'yyyy-MM-dd hh:mm:ss');
+----------------------+--+
|         _c0          |
+----------------------+--+
| 2018-02-27 02:00:00  |
+----------------------+--+

(or)

By using regexp_replace function we can replace T in your timestamp value

hive> select regexp_replace('2018-02-27T02:00','T',' ');
+-------------------+--+
|        _c0        |
+-------------------+--+
| 2018-02-27 02:00  |
+-------------------+--+

And use concat function to add missing :00 value to make above value as hive timestamp.

hive> select concat(regexp_replace('2018-02-27T02:00','T',' '),":00");
+----------------------+--+
|         _c0          |
+----------------------+--+
| 2018-02-27 02:00:00  |
+----------------------+--+
Don't have an account?
Coming from Hortonworks? Activate your account here