Hive's hour() function returns 12 hour clock v

问题:

According to the documentation Hives standard function hour() should return a value between 0 and 24 but for some reason I always get a twelve hour clock value, between 0 and 12. I'm using a MySQLDateTime field as a Timestamp field in my Hive table. Anyone know what the problem might be?

回答1:

I think I found it. I looked at the source code and apparently UDFHour.java does have two evaluate() functions. One that does accept a Text object as parameter and one that uses a TimeStampWritable object as parameter. Both work with a Calendar instance but for some reason the first function returns the value of Calendar.HOUR_OF_DAY and the second one Calendar.HOUR.

I've looked in the Hives documentation but I couldn't find anything about that second function, but it's there. I'm using Hive 0.9.0.16, which came with Hortonworks' HDP.

Edit: I've reported this a while back. A patch is now available: https://issues.apache.org/jira/browse/HIVE-3850.

回答2:

Regardless of what hive has done, you could format the date to be returned as 24 hour format.

select FROM_UNIXTIME(mydate)  
from mytable
;

Or you may update all datetimes stamps if it makes sense.

Reference

回答3:

The function hour() returns a 24h formatted result if it works with string format. You may use

hour(cast (column_name as string))

回答4:

for lower version of hive,I got a workaround

    hour(from_unixtime(
unix_timestamp(
from_utc_timestamp(
from_unixtime(round(created_at/1000)),'Etc/GMT-8')
)))

Since I am using EMR, I can not choose to use the latest version of hive, so I got this workaround.

回答5:

Just to show an example for what has already been told above

HOUR(cast (from_utc_timestamp(my_date_timestamp ,'GMT') as string)) -- returns 24 hr format

HOUR( from_utc_timestamp(my_date_timestamp ,'GMT') ) --returns 12 hr format