How do I average the last 5 minutes of entries from a SQL database?

Question

I have a table that stores time, heart_rate, and player_id. I need to somehow take an average of the heart rate data over the last five minutes and group each average by the player ID.

Answer 1

Another option is to use DATEDIFF

SELECT  AVG(heart_rate) AS heart_rate,
        player_id
FROM    tablename
WHERE   DATEDIFF(MINUTE, time, GETDATE()) < 5
GROUP BY player_id

Answer 2

This is partly a response to Donal's answer (and too long for a comment).

You do not want to use the following:

WHERE datepart(minute, (time - GETDATE())) < 5

There are two reasons. First, this cannot make use of an index on time. That is, the calculation is not sargable. Second, the subtraction is backwards, unless time values are in the future.

A better way to write this is:

select player_id, avg(heart_rate)
from table t
where time >= dateadd(minute, -5, getdate())
group by player_id;