CODEWITHSUNDEEP
group-byhiveepoch
RpB
RpB

Reputation: 315

How do I group records by epoch time intervals in Hive table?

I have time stamp column values in epoch ( ex. min value = 1276570880, max value = 1276657260). How do I group records in my Hive table based on 30 min intervals.

I need count a value for every 30 min starting from the min time stamp value until the max time stamp value in the time stamp column.

I have tried the following query, but it has not resulted any results.

SELECT  COUNT(method) AS mycount, FROM_UNIXTIME(floor(UNIX_TIMESTAMP(ts)/1800)*1800)   
FROM http
WHERE ts >= '2010-06-14 20:01:20'
AND ts <= '2010-06-14 22:01:20'
AND method='GET'
GROUP 
    BY FROM_UNIXTIME(
         floor(UNIX_TIMESTAMP(ts)/1800)*1800)

Upvotes: 1

Views: 2681

Answers (1)

Jared
Jared

Reputation: 2954

This should work. Using round on the timestamps is important for the grouping to work correctly. Here is a SQLfiddle example which shows your specific example.

select count(method) as mycount,
       from_unixtime(round(unix_timestamp(ts))
from http
where ts >= '2010-06-14 20:01:20'
and ts <= '2010-06-14 22:01:20'
and method='GET'
group by round(ts/1800)

Upvotes: 1

Related Questions