How to get the current time for a given time zone in the hive
I have 2 tables in Hive.
Table 1 contains
:
timelocation
2015-03-04 15:00 Chicago
2015-03-04 15:00 Denver
2015-03-04 15:00 Honolulu
Table 2 contains
:
ID Description
America/Chicago CENTRAL STANDARD TIME
America/Denver MOUNTAIN STANDARD TIME
Pacific/Honolulu HAWAII-ALEUTIAN STANDARD TIME
For the records in Table 1, such as “2015-03-04 15:00 Chicago”, I need to find the corresponding Chicago record in Table 2. It should read the Chicago ID and description and return Chicago’s current Central Standard Time, which is “2015-05-04 09:11”.
Similarly, for Denver, it must return to Mountain Standard Time; For Honolulu, it must return to Hawaii-Aleutian Standard Time.
The expected output is
timelocation
2015-05-04 09:11
2015-05-04 08:11
2015-05-04 04:11
What should I do?
Solution
I don’t really want to write this query for you, but hopefully this will point you in the right direction. You need to add tbl1 <=> tbl2 to extract the city from the corresponding column of each table; The split() feature will help you. Then you can find a nice function< a href="https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF" rel="noreferrer noopener nofollow">here called from_utc_timestamp() It takes a timestamp (assuming UTC) and converts it to the given time zone. You also need to convert the Description column to its time zone abbreviation. You can find those here