8

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

  • 1
    In your sample data, e.g. 2012/01/01 00:10, 10, are all those values in a single column, or is the comma a column delimiter? Also, are the exact hours (1:00, 2:00, 3:00, etc.) guaranteed to be stored in the time series table, or might it skip over the :00 and have entries such as 2012/01/01 03:50 followed by 2012/01/01 04:10? – dartonw Jun 10 '14 at 21:40
  • What if you have an hour with no source data? Do you still want an output like 2012/01/01 04:00, 2012/01/01 05:00, 0? or should that hour just be omitted from the summary? – Joshua Huber Jun 10 '14 at 21:51
  • @dartonw - the comma is a column delimiter. So the date time and the value are different columns in a table. The exact hours are guaranteed to be always stored. – dan Jun 11 '14 at 5:23
8
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.