sql - How can I select one row of data per hour, from a table of time stamps? -

excuse me if confusing, not familiar postgresql. have postgres database table full of "sites". each site reports once hour, , when reports, makes entry in table, so:

site |      tstamp -----+-------------------- 6000 | 2013-05-09 11:53:04 6444 | 2013-05-09 12:58:00 6444 | 2013-05-09 13:01:08 6000 | 2013-05-09 13:01:32 6000 | 2013-05-09 14:05:06 6444 | 2013-05-09 14:06:25 6444 | 2013-05-09 14:59:58 6000 | 2013-05-09 19:00:07 

as can see, time stamps never on-the-nose, , there 2 or more within few minutes/seconds of each other. furthermore, sites won't report hours @ time (on occasion). want select 1 entry per site, per hour (as close each hour can get). how can go doing in efficient way? need extend other time frames (like 1 entry per site per day -- close midnight possible).

thank , suggestions.

you use distinct on:

select distinct on (date_trunc('hour', tstamp)) site, tstamp t order date_trunc('hour', tstamp), tstamp 

be careful order if care entry get.

alternatively, use row_number window function mark rows of interest , peel off first result in each group derived table:

select site, tstamp (     select site, tstamp,            row_number() on (partition date_trunc('hour', tstamp) order tstamp) r     t ) dt r = 1 

again, you'd adjust order select specific row of interest each date.