SQL hourly data aggregation in postgresql

Question

I am a newbie with database so I'm seeking your help with this one.

I have a table containing time series data.

2012/01/01 00:10, 10
2012/01/01 00:30, 5
2012/01/01 01:00, 10
2012/01/01 01:40, 10
2012/01/01 02:00, 20

The table is storing interval based data by keeping only the upper limit of the interval. For example the first row represents an interval from [00:00 - 00:10] with a value of 10, second row represents an interval from (00:10 - 00:30] with a value of 5 and the third one represents an interval from (00:30 - 01:00) with a value of 10.

I need an efficient query in Postgres to aggregate hourly data for a structure like the one described above. So the result would be something like this:

2012/01/01 00:00, 2012/01/01 01:00, 25
2012/01/01 01:00, 2012/01/01 02:00, 30

Note that the time-series data is big so any help with indexing this would be much appreciated.

Thanks, dan

Answer 1

select
  date_trunc('hour', t - interval '1 minute') as interv_start,
  date_trunc('hour', t - interval '1 minute')  + interval '1 hours' as interv_end,
 sum(v)
  from myt 
    group by date_trunc('hour', t - interval '1 minute')
order by interv_start

see sqlfiddle

As for the index: you could try a function index on date_trunc('hour', t - interval '1 minute') but I'm not sure postgresql can use it.