How to convert ISO Date to UTC date in Hive

Question

I have JSON data as below: I need to convert that date or mongo_date into utc timestamp, to analyse the data in hive as per timeline example per year, per month, per week using map reduce

{
    "_id" : ObjectId("51ac77050e9edcdad271ce2d"),
    "company" : null,
    "date" : "19760224",
    "mongo_date" : ISODate("1976-02-24T00:00:00Z")

Allrud

Hi, have you found any workaround to use mongo_date with existing UDFs like Hour or Minutes? Because the Month, Week, and Day work with those UDFs but after 'T' it ignores the hh:mm:ssZ. tnx – Nannette

Answer 1

Hive understands this format: 'yyyy-MM-dd HH:mm:ss.SSS'.

Use unix_timestamp() to convert to seconds passed from 1970-01-01, then use from_unixtime() to convert to proper format:

 select from_unixtime(UNIX_TIMESTAMP("2017-01-01T05:01:10Z", "yyyy-MM-dd'T'HH:mm:ss'Z'"),"yyyy-MM-dd HH:mm:ss"); 

Result:

2017-01-01 05:01:10

Update. This method is to remove Z and replace T with space using regexp_replace and convert to timestamp if necessary, without using unix_timestamp(), this will preserve milliseconds:

select timestamp(regexp_replace("2019-05-17T17:03:09.775Z", '^(.+?)T(.+?)Z$','$1 $2'));

Result:

2019-05-17 17:03:09.775

Capsicum