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.