That did fix my issue. Thanks! Craig
On Wed, Feb 18, 2015 at 2:56 PM, Jason Dere <jd...@hortonworks.com> wrote: > I believe this is due to https://issues.apache.org/jira/browse/HIVE-9278, > which has been fixed in trunk (also 1.0). > This affects UDFs using the old-style UDF classes (which include > hour()/minute()/second()) > > A workaround is to do "set hive.cache.expr.evaluation=false;" before > running the query. > > > Jason > > On Feb 18, 2015, at 11:06 AM, Craig Jones <cjo...@spryinc.com> wrote: > > I have a unix time that I need to convert time-zones and then do some > calculations on it. In the end I want to create a fractional hour (15:30 -> > 15.5). We are at Hive 0.14.0.2.2.0.0-2041 > > I have the following Hive test query: > > select > time_from_epoch, > cast(hour( from_utc_timestamp( from_unixtime(time_from_epoch, > 'yyyy-MM-dd HH:mm:ss'), "America/Los_Angeles") ) as double) as hour, > > cast(minute( from_utc_timestamp( from_unixtime(time_from_epoch, > 'yyyy-MM-dd HH:mm:ss'), "America/Los_Angeles") ) as double) as minute, > > > cast(minute( from_utc_timestamp( from_unixtime(time_from_epoch, > 'yyyy-MM-dd HH:mm:ss'), "America/Los_Angeles") ) as double)/60.0 as > fractional_hour, > > cast(hour( from_utc_timestamp( from_unixtime(time_from_epoch, > 'yyyy-MM-dd HH:mm:ss'), "America/Los_Angeles") ) as double)+ > cast(minute( from_utc_timestamp( from_unixtime(time_from_epoch, > 'yyyy-MM-dd HH:mm:ss'), "America/Los_Angeles") ) as double)/60.0 as > hour_minute, > > 14+cast(minute( from_utc_timestamp( from_unixtime(time_from_epoch, > 'yyyy-MM-dd HH:mm:ss'), "America/Los_Angeles") ) as double)/60.0 as test1, > > cast(hour( from_utc_timestamp( from_unixtime(time_from_epoch, > 'yyyy-MM-dd HH:mm:ss'), "America/Los_Angeles") ) as double) + 0.15 as test2 > > from the_table > > _c0, hour, minute, fractional_hour, hour_minute, test1, test2 > 1367445469, 14, 57, 0.94999999999999996, *14.233333333333333*, > 14.949999999999999, 14.15 > > This gives an erroneous hour_minute field (14.233) expecting 14.9499. > The test1 and test2 were added in there to see if either of the > > What is odd is if I remove the field and replace with a fixed number: > > select > 1367445469, > cast(hour( from_utc_timestamp( from_unixtime(1367445469, 'yyyy-MM-dd > HH:mm:ss'), "America/Los_Angeles") ) as double) as hour, > > cast(minute( from_utc_timestamp( from_unixtime(1367445469, 'yyyy-MM-dd > HH:mm:ss'), "America/Los_Angeles") ) as double) as minute, > > cast(minute( from_utc_timestamp( from_unixtime(1367445469, > 'yyyy-MM-dd HH:mm:ss'), "America/Los_Angeles") ) as double)/60.0 as > fractional_hour, > > cast(hour( from_utc_timestamp( from_unixtime(1367445469, 'yyyy-MM-dd > HH:mm:ss'), "America/Los_Angeles") ) as double)+ > cast(minute( from_utc_timestamp( from_unixtime(1367445469, 'yyyy-MM-dd > HH:mm:ss'), "America/Los_Angeles") ) as double)/60.0 as hour_minute, > > 14+cast(minute( from_utc_timestamp( from_unixtime(1367445469, > 'yyyy-MM-dd HH:mm:ss'), "America/Los_Angeles") ) as double)/60.0 as test1, > > cast(hour( from_utc_timestamp( from_unixtime(1367445469, 'yyyy-MM-dd > HH:mm:ss'), "America/Los_Angeles") ) as double) + 0.15 as test2 > > from the_table > limit 100; > > I get the following: > > _c0, hour, minute, fractional_hour, hour_minute, test1, test2 > 1367445469, 14, 57, 0.94999999999999996, 14.949999999999999, > 14.949999999999999, 14.15 > > and this is all correct! > > I must be doing something wrong here but it seems to be a bug. Any > ideas? > > Craig > > -- > Craig Jones, PhD > Data Scientist > Spry, Inc > > em: cjo...@spryinc.com > web: http://spryinc.com > > > -- Craig Jones, PhD Data Scientist Spry, Inc em: cjo...@spryinc.com web: http://spryinc.com
