Remove special character using hive

user_11883090 0
Viewed 234

In a raw table, the date column has been saved in string format (e.g.,2020-11-19T15:59:30.702+0000). I need to convert this to timestamp format(2020-11-19 15:59:30.702).

I tried with concat_ws cast(concat_ws('.',from_unixtime(unix_timestamp(regexp_replace('2020-11-19T15:59:30.702+0000','T',''), 'yyyy-MM-ddHH:mm:ss')),REGEXP_REPLACE(split(createdat,'\\.')[1],'[^0-9A-Za-z ]+', ''))

hiveql hive timestamp sql
1 Answers

If you do not need timezone conversion (if it is always +0000 timezone), remove T and timezone:

 select timestamp(regexp_replace("2020-11-19T15:59:30.702+0000", '^(.+?)T(.+?)\\+','$1 $2'));

Result:

2020-11-19 15:59:30.702
user_27003440
Related