Hive hour() function returns NULL

Question

Hive's hour function returns NULL. below is my query

select hour(col_name) from hivetable

datatype for col_name is string and value is in format HH:MM:SS

Please give an example for one value that returns NULL. Also please show the result of hex(col_name) for this value — David דודו Markovitz, Mar 08 '17 at 20:43
Hex value is 30333A30343A3531, while value of col_name is "03:04:51" — Vish, Mar 09 '17 at 22:11
This does not make sense. Please add to the post a reproducible use-case: create table + insert + select. — David דודו Markovitz, Mar 09 '17 at 22:25

score 0 · Answer 1 · answered Mar 09 '17 at 05:33

0

I would suggest you to check the col_name format is correct, either it should have a timestamp format or as 'HH:mm:ss', because using 'hour' function on string time will work. example: hour('HH:mm:ss')

hive > select hour('12:58:59') as hour;
OK
hour
12
Time taken: 0.061 seconds, Fetched: 1 row(s)

answered Mar 09 '17 at 05:33

user7343922

316
4
17

Yes the format is correct the value currently i am using is "03:04:51" – Vish Mar 09 '17 at 22:12

score 0 · Answer 2 · answered Mar 09 '17 at 22:15

0

The issue can be resolved by using select hour(from_unixtime(unix_timestamp(col_name, 'HH:mm:ss')) from hivetable.

But i am not sure why we need to do this when the input value is string and in correct format which is "HH:mm:ss"

answered Mar 09 '17 at 22:15

Vish

1
1

Hive hour() function returns NULL

2 Answers2