How do I get a hourly average of a value which is stored per minute

Question

The following table receives a dataset (nearly) every minute via cronjob:

id  |  timestamp  | humanTime          | userCount  
-----------------------------------------------------
1      1482310202   2016-12-21 09:50:07   120 
2      1482310262   2016-12-21 09:51:07   126
3      1482310322   2016-12-21 09:52:06   110
4      1482310381   2016-12-21 09:54:06   131
5      ...

Since the cronjob is a query via network, it is not ensured that there are 60 entries in every hour due to possible timeouts.

I would like to calculate the hourly average of the column userCount for the last X hours.

Does anyone have an idea?

P.S.: If there's no way to do this via sql, i'd like to solve this problem via PHP

Answer 1

You just need to extract the date and hour from time. I'd stick with the human time:

select date(humanTime) as dd, hour(humanTime) as hh, avg(userCount)
from t
group by date(humanTime), hour(humanTime)
order by dd, hh;

If you want the last "n" hours, then include:

where humanTime >= date_sub(now(), interval "n" hour)

Answer 2

You can use DATEPART. As here I am providing per hour entry count for one day:

SELECT DATEPART(hh, human_time) AS hour, COUNT(id) id
FROM table
WHERE human_time >= '2016-12-21' AND human_time < '2016-12-21'
GROUP BY DATEPART(hh, human_time)

Answer 3

(PHP SOLUTION) Since unix timestamp is in seconds, you can calculate the diff between 2 stamps in hours like this:

$stamp1 = 1482310381;
$stamp2 = 1482310202;
$hours_diff = ($stamp1 - $stamp2) / 60 / 60;

You can iterate the timestamp and the userCount columns simultaneously, and check: if the timestamps diff is (or greater than) the hour range that I want, I will calculate the sum of userCount I got (add each to a var i.e. $sum) and divide it by the diff between the first timestamp id column and the last timestamp id column.

Hope it helps, Good luck! :)