I'm quickly developing a prototype application and one of the things I needed to do was calculate the hourly average of a given stock in one hour blocks.
The table is pretty simplistic in that it has a stockid foreign key, a value field (representing the current value at that point in time), and of course the createdat timestamp field.
Here's my first naive attempt using Postgres:
SELECT block ,MAX(block_avg) AS max_avg FROM (SELECT date_trunc('hour', created_at) AS created_at_trunc ,AVG(value) AS block_avg FROM stock_values GROUP BY date_trunc('hour', created_at)) AS x, (SELECT ('today'::date+'1 hour'::interval*q)::timestamp AS block FROM generate_series(0, extract('hour' from localtimestamp)::integer) AS q) AS y WHERE created_at_trunc > block - '1 hour'::interval AND created_at_trunc <= block GROUP BY 1 ORDER BY 1
I worked into this solution beginning with a mailing list post I found here.