I'm having a slight issue. I have a PostgreSQL table with such format
time (datetime) | players (int) | servers (int)
---------------------------------------------------
2013-12-06 13:40:01 | 80 | 20
2013-12-06 13:41:13 | 78 | 21
etc.
I would like to group them by 5 minute periods and get an average of the group as a single value, so there will be 20% of the records, each containing an average of ~5 numbers, with time set to the first time value in the group. I have no idea how to do this in PgSQL. So the result would be:
2013-12-06 13:40:01 | avg of players on :40, :41, :42, :43, :44 | same with servers
2013-12-06 13:45:05 | avg of players on :45, :46, :47, :48, :49 | same with servers
2013-12-06 13:50:09 | avg of players on :50, :51, :52, :53, :54 | same with servers
2013-12-06 13:55:12 | avg of players on :55, :56, :57, :58, :59 | same with servers
SELECT grid.t5
, min(t."time") AS min_time
-- , array_agg(extract(min FROM t."time")) AS 'players_on' -- optional
, avg(t.players) AS avg_players
, avg(t.servers) AS avg_servers
FROM (
SELECT generate_series(min("time")
, max("time")
, interval '5 min') AS t5
FROM tbl
) grid
LEFT JOIN tbl t ON t."time" >= grid.t5
AND t."time" < grid.t5 + interval '5 min'
GROUP BY grid.t5
ORDER BY grid.t5;
The subquery grid produces one row for every 5 minutes from minimum to maximum "time" in your table.
LEFT JOIN back to the table slicing data in 5-min intervals. Include the lower bound, exclude the upper border.
To drop 5-min slots where nothing happened, use JOIN instead of LEFT JOIN.
To have your grid-times start at 0:00, 5:00 etc, round down the min("time") in generate_series().
Related:
Aside: I wouldn't use "time" as identifier. It's a reserved word in standard SQL and a function / type name in Postgres.