Date Function in Hive

Hive has built-in date function to convert time zone from utc.

time is in unixtime seconds.

SELECT * FROM_UNIXTIME(time, 'yyyy-MM-dd HH:mm:ss z') as local_zone, 
from_utc_timestamp(FROM_UNIXTIME(time, 'yyyy-MM-dd HH:mm:ss'),'CST') as cst 
from state
where time <= UNIX_TIMESTAMP(to_utc_timestamp('2014-10-01 00:00:00', 'CST'));

Reference: https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-DateFunctions<