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<mailto: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<mailto:cjo...@spryinc.com> web: http://spryinc.com<http://spryinc.com/>
