Sliding 1-hour periods aggregation query

Question

In Postgres 9.2, I have a table containing measures taken at specific points in time:

CREATE TABLE measures (dt timestamptz, measure integer);

INSERT INTO measures VALUES
('2015-01-13 12:05', 10), 
('2015-01-13 12:30', 8), 
('2015-01-13 13:02', 16), 
('2015-01-13 13:30', 12), 
('2015-01-13 14:15', 7);

I want to calculate the average and count of lines for 1-hour periods, which I can do this way:

SELECT date_trunc('hour', dt) as d, max(measure), count(*)
FROM measures group by d order by d;

But instead of 1-hour periods starting at 12:00, 13:00 etc. I want 1-hour periods following an event. That's one period from 12:05 to 13:05 and the next from 13:30 to 14:30 in this case.

Is this possible in PostgreSQL?

Answer 1

If you can find a function is postgresql that adds an hour to a datetime then you should be able to join your result set on itself based on the date and date + 1 hour in an inner query then aggergate the values up in an outer query to get the results you need.

SELECT
    LowDate,
    HighDate=DATEADD(HOUR,1,LowDate),
    SumMeasure=SUM(measure),
    ItemCount=COUNT(*)
FROM
(
    SELECT
        LowDate=M1.dt,  
        measure=M2.measure
    FROM
        measures M1 
        INNER JOIN measures M2 ON M2.dt BETWEEN M1.dt AND DATEADD(HOUR,1,M1.dt)
)AS DETAIL  
GROUP BY
    LowDate 
ORDER BY
    LowDate