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
