CODEWITHSUNDEEP
hivehiveql
Bhaskar
Bhaskar

Reputation: 371

Timestamp conversion in Hive

I would like to convert the following string value to timestamp in hive

2016-12-31T07:09:48.507Z --> 2016-12-31 07:09:48.507

Can you please advise me how can we do this.

Thanks.

Upvotes: 2

Views: 1408

Answers (2)

Ramesh
Ramesh

Reputation: 1484

As you have milliseconds, unix_timestamp won't work. I think you need this

SELECT CAST(REGEXP_REPLACE('2016-12-31T07:09:48.507S', 'T|S', ' ') as timestamp) AS formatted_timestamp;

Output

formatted_timestamp
2016-12-31 07:09:48.507

Upvotes: 2

Ankur Alankar Biswal
Ankur Alankar Biswal

Reputation: 1182

hope this will help you to convert string into datetimestamp

SELECT from_unixtime(unix_timestamp(REGEXP_REPLACE('2016-12-31T07:09:48.507S', 'T', ' '), 'yyyy-MM-dd HH:mm:ss'))

Upvotes: 1

Related Questions