Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

2018- to convert into hive

avatar
Rising Star
 
1 ACCEPTED SOLUTION

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

View solution in original post

1 REPLY 1

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