CODEWITHSUNDEEP
mysql
Ritesh
Ritesh

Reputation: 4756

How can i get aggregated data of every 5th minute in last 10 mins in Mysql?

I have a table which contains multiple data on same time interval for every minute. I want to aggregate data of every 5th minute in last 10 mins. I have tried all the solution provided on stack overflow but its not getting accurate data for me as none of them has tried to get data for fix interval of time.

I am using this query :

SELECT ROUND(unix_timestamp(footable.createdTime)/(60 * 5)) AS timekey, avg(mainData) as aggData
FROM footable 
WHERE footable.createdTime > date_sub(now(), INTERVAL 10 MINUTE) 
GROUP BY timekey

It should return max 2 records everytime but most of the time it returning 3 records.

Note:- table contains data for every minute its confirmed and this is test condition for 10 mins it could be a data of last hour. Its not a duplicate question read the description carefully.

Any help will be appreciated..!!

Upvotes: 0

Views: 59

Answers (1)

Ritesh
Ritesh

Reputation: 4756

I figured it out myself and below is the query to solve the issue :

SET @timeStmp := UNIX_TIMESTAMP(date_sub(now(), INTERVAL 10 MINUTES));
SELECT @timeStmp := IF(((unix_timestamp(footable.createdTime) - @timeStmp) >= 295), unix_timestamp(footable.createdTime),@timeStmp) as timekey, avg(mainData) as aggData
FROM footable 
WHERE footable.createdTime > date_sub(now(), INTERVAL 10 MINUTE) 
GROUP BY timekey

This query will give exact minute to minute interval aggregated data on every execution. Enjoy..!!

Upvotes: 0

Related Questions