hive-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Craig Jones <cjo...@spryinc.com>
Subject Re: casting of hour and minute addition issue
Date Wed, 18 Feb 2015 20:15:33 GMT
That did fix my issue.  Thanks!

Craig

On Wed, Feb 18, 2015 at 2:56 PM, Jason Dere <jdere@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 <cjones@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: cjones@spryinc.com
> web: http://spryinc.com
>
>
>


-- 
Craig Jones, PhD
Data Scientist
Spry, Inc

em: cjones@spryinc.com
web: http://spryinc.com

Mime
View raw message