如何获得蜂巢中给定时区的当前时间 [英] How to get current time for a given timezone in hive

问题描述

table1包含

  timelocation 
 2015-03-04 15:00芝加哥
 2015-03-04 15:00丹佛
 2015-03-04 15:00火奴鲁鲁
  
 
 
 
 $ b $ p $  ID $ b $ $ b美国/芝加哥中央标准时间
美国/丹佛山地标准时间
太平洋/檀香山HAWAII-ALEUTIAN标准时间
  

对于表1中记录的2015-03-04 15:00芝加哥记录，我需要在table2中查找相应的芝加哥记录。它应该读取ID&对芝加哥的描述，并返回当前芝加哥的中央标准时间，即2015-05-04 09:11。

同样，对于丹佛，它必须返回Mountain Standard Time&对于檀香山来说，它必须返回HAWAII-ALEUTIAN STANDARD TIME。

预期产量是

  timelocation 
 2015-05-04 09:11 
 2015-05-04 08:11 
 2015-05-04 04:11 
  
  
      我真的不想为你写这个查询，但希望这会指出你在正确的方向。您需要加入 tbl1< => TBL2 。从每个表中的相应列中提取城市;  split（）函数会帮助你。然后有一个漂亮的函数，您可以在这里找到 from_utc_timestamp（），它需要一个时间戳（假定为UTC）并将其转换为给定的时区。您还需要将列描述转换为其时区缩写。您可以在这里找到 。祝你好运！ 
I have 2 tables in Hive.

table1 contains
timelocation
2015-03-04 15:00 Chicago
2015-03-04 15:00 Denver
2015-03-04 15:00 Honolulu
table2 contains 
ID                       Description
America/Chicago          CENTRAL STANDARD TIME                              
America/Denver           MOUNTAIN  STANDARD TIME                            
Pacific/Honolulu         HAWAII-ALEUTIAN STANDARD TIME                       
For a record present in table1 say "2015-03-04 15:00 Chicago" , I need to do a lookup in table2 for the corresponding Chicago record. It should read the ID & Description for chicago and return the current Central Standard Time for Chicago i.e "2015-05-04 09:11".

Similarly for Denver it must return Mountain Standard Time &  for Honolulu it must return HAWAII-ALEUTIAN STANDARD TIME.

Expected output is 
timelocation
2015-05-04 09:11
2015-05-04 08:11
2015-05-04 04:11
How can I do it?
解决方案
I don't really want to write this query for you but hopefully this will point you in the right direction.  You need to join tbl1 <=> tbl2.  Extract the city from the respective columns in each table; split() function will help you.  Then there is a nifty function you can find here called from_utc_timestamp() that takes a time stamp (assumed to be UTC) and converts it to a given time zone.  You'll also need to convert the column Description to its time zone abbreviation.  You can find those here.  Good Luck!

                        
这篇关于如何获得蜂巢中给定时区的当前时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！