Convert ISO-8601 time to UTC time in Hive

In order to convert the ISO-8601 datetime string .e.g "2013-06-10T12:31:00+0700" in to UTC time "2013-06-10T05:31:00Z" you can do the following


select from_unixtime(iso8601_to_unix_timestamp('2013-06-10T12:31:00Z'), 'yyyy-MM-dd-HH-mm-ss') from table limit 1;


For this to work you will need the simply measured's hive udf and you will need to add the following jars:

hive> ADD JAR hdfs:///external-jars/commons-codec-1.9.jar;
hive> ADD JAR hdfs:///external-jars/joda-time-2.2.jar;
hive> ADD JAR hdfs:///external-jars/sm-hive-udf-1.0-SNAPSHOT.jar;

hive>select from_unixtime(iso8601_to_unix_timestamp('2013-06-10T12:31:00Z'), 'yyyy-MM-dd-HH-mm-ss') from table limit 1;