Support Questions

Find answers, ask questions, and share your expertise
Celebrating as our community reaches 100,000 members! Thank you!

Query in hive to find the hours from timestamp and its range.

Rising Star

Super Guru

Hi @Gayathri Devi,

Hive provides lot of Date functions which you can check here. You can use them with the 'where' clause or between for your range queries.

You can check this post for the best practices for hive partitioning for Dates




@Gayathri Devi

If i understand your question right, you want to pick the hour from the timestamp column which can be easily done from

select hour(current_timestamp);

But I could get want you meant by range. Do you want to generate the hour provided the start and end time/range? Is that what you are trying to convey?


@Gayathri Devi

I couldn't think of any built in function in hive to handle this scenario.

The other way of doing this is by using something like below:

select from_unixtime(unix_timestamp(current_timestamp)+7200);

7200 implies seconds needed for 2 hours as mentioned in your quetion. You can alter it based on your need.

Instead of the hardcoded value you can pass variable or row_number()over() * 3600 can be used to generate sequentially.

Hope it Helps!!