2

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.

Bhaskar
  • 271
  • 7
  • 20

2 Answers2

1

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'))
Ankur Alankar Biswal
  • 1,184
  • 1
  • 12
  • 23
1

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
Ramesh
  • 1,405
  • 10
  • 19