CODEWITHSUNDEEP
hadoophive
jumpman8947
jumpman8947

Reputation: 581

Hadoop Hive Date String to UTC Time SQL

I have a String column in my database which looks like

07/12/2019 04:17:08 PM

I use the function 

cast(from_unixtime(unix_timestamp(myfield, 'MM/dd/yyyy hh:mm:ss'),'yyyy-MM-dd HH:mm:ss') as timestamp)as mytime

This gives me the result of

2019-07-12 04:17:08.0

I want the result to be in utc format and look something like

2019-07-12 16:17:08.

How can i change this to be in utc format?

Upvotes: 0

Views: 454

Answers (1)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

Use aaa to parse the AM/PM in datetime. from_unixtime converts it to yyyy-MM-dd hh:mm:ss by default where the hour part is 24 hour format.

from_unixtime(unix_timestamp(myfield, 'MM/dd/yyyy hh:mm:ss aaa'))

Upvotes: 1

Related Questions