2018- to convert into hive

[#1 · Accepted solution]

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