Select Data over time period

I'm a bit of newbie when it comes to postgres, so bear with me a wee bit and i'll see if i can put up enough information.

i insert weather data into a table every 10 mins, i have a time column that is stamped with an epoch date.

I Have a column of the last hrs rain fall, and every hr that number changes of course with the running total (for that hour).

What i would like to do is skim through the rows to the end of each hour, and get that row, but do it over the last 4 hours, so i would only be returning 4 rows say.

Is this possible in 1 query? Or should i do multiple queries?

I would like to do this in 1 query but not fu开发者_如何学Cssed...

Thanks

Thanks guys for your answers, i was/am a bit confused by yours gavin - sorry:) comes from not knowing this terribly well.

I'm still a bit unsure about this, so i'll try and explain it a bit better..

I have a c program that inserts data into the database every 10 mins, it reads the data fom a device that keeps the last hrs rain fall, so every 10 mins it could go up by x amount. So i guess i have 6 rows / hr of data. My plan was to go back (in my php page) every 7, which would be the last entry for every hour, and just grab that value. Hence why i would only ever need 4 rows.. just spaced out a bit!

My table (readings) has data like this

index | time (text)  | last hrs rain fall (text)
1     | 1316069402   | 1.2

All ears to better ways of storing it too :) I very much appreciate your help too guys thanks.

You should be able to do it in one query...

Would something along the lines of:

SELECT various_columns,
    the_hour,
    SUM ( column_to_be_summed )
FROM ( SELECT various_columns,
            column_to_be_summed,
            extract ( hour FROM TIME ) AS the_hour
       FROM readings
      WHERE TIME > ( NOW() - INTERVAL '4 hour' ) ) a
GROUP BY various_columns,
    the_hour ; 

do what you need?

SELECT SUM(rainfall) FROM weatherdata WHERE time > (NOW() - INTERVAL '4 hour' );

I don't know column names but that should do it the ones in caps are pgsql types. Is that what you are after?

I am not sure if this is exactly what you are looking for but perhaps it may serve as a basis for adaptation. I often have a requirment for producing summary data over time periods though I don't use epoch time so there may be better ways of manipulating the values than I have come up with.

create and populate test table

create table epoch_t(etime numeric);
insert into epoch_t 
select extract(epoch from generate_series(now(),now() - interval '6 hours',interval '-10 minutes'));

To divide up time into period buckets:

select generate_series(to_char(now(),'yyyy-mm-dd hh24:00:00')::timestamptz,
                       to_char(now(),'yyyy-mm-dd hh24:00:00')::timestamptz - interval '4 hours', 
                       interval '-1 hour');

Convert epoch time to postgres timestamp:

select timestamptz 'epoch' + etime * '1 second'::interval from epoch_t;

then truncate to hour :

select to_char(timestamptz 'epoch' + etime * '1 second'::interval,
               'yyyy-mm-dd hh24:00:00')::timestamptz  from epoch_t

To provide summary information by hour :

select to_char(timestamptz 'epoch' + etime * '1 second'::interval,
               'yyyy-mm-dd hh24:00:00')::timestamptz,
       count(*)  
  from epoch_t 
 group by 1 
 order by 1 desc;

If you might have gaps in the data but need to report zero results use a generate_series to create period buckets and left join to data table. In this case I create sample hour buckets back prior to the data population above - 9 hours instead of 6 and join on the conversion of epoch time to timestamp truncated to hour.

select per.sample_hour,
       sum(case etime is null when true then 0 else 1 end) as etcount
  from (select generate_series(to_char(now(),
                               'yyyy-mm-dd hh24:00:00')::timestamptz,
                               to_char(now(),'yyyy-mm-dd hh24:00:00')::timestamptz - interval '9 hours', 
                               interval '-1 hour') as sample_hour) as per
       left join epoch_t on to_char(timestamptz 'epoch' + etime * '1 second'::interval,
                                    'yyyy-mm-dd hh24:00:00')::timestamptz = per.sample_hour
group by per.sample_hour
order by per.sample_hour desc;