Re: date_trunc to aggregate values?

2013/2/4 Kirk Wythers <wythe001@umn.edu>:
> I am looking for suggestions on aggregation techniques using a timestamp column. In my case I have tried:
>
> date_trunc('hour', derived_tsoil_fifteen_min_stacked.time2),
>
> but date_truck only seems to aggregate the timestamp. I thought I could use
>
> AVG(derived_tsoil_fifteen_min_stacked.value)
>
> in combination with date_trunk, but I still get 15 minute values, not the hourly average from the four 15 minute
records.
>
> rowid                                                   date_truck                              time2
         site    canopy  plot    variable        name                    value   avg 
> 2010-07-07_00:00:00_b4warm_a    2010-07-07 00:00:00     1       2010-07-07 00:00:00     cfc     closed  a2
tsoil_sc       tsoil_avg1_sc   21.06   21.0599994659424 
> 2010-07-07_00:15:00_b4warm_a    2010-07-07 00:00:00     1       2010-07-07 00:15:00     cfc     closed  a2
tsoil_sc       tsoil_avg1_sc   20.96   20.9599990844727 
> 2010-07-07_00:30:00_b4warm_a    2010-07-07 00:00:00     1       2010-07-07 00:30:00     cfc     closed  a2
tsoil_sc       tsoil_avg1_sc   20.88   20.8799991607666 
> 2010-07-07_00:45:00_b4warm_a    2010-07-07 00:00:00     1       2010-07-07 00:45:00     cfc     closed  a2
tsoil_sc       tsoil_avg1_sc   20.8    20.7999992370605 
> 2010-07-07_01:00:00_b4warm_a    2010-07-07 01:00:00     1       2010-07-07 01:00:00     cfc     closed  a2
tsoil_sc       tsoil_avg1_sc   20.72   20.7199993133545 
> 2010-07-07_01:15:00_b4warm_a    2010-07-07 01:00:00     1       2010-07-07 01:15:00     cfc     closed  a2
tsoil_sc       tsoil_avg1_sc   20.64   20.6399993896484 
> 2010-07-07_01:30:00_b4warm_a    2010-07-07 01:00:00     1       2010-07-07 01:30:00     cfc     closed  a2
tsoil_sc       tsoil_avg1_sc   20.55   20.5499992370605 
> 2010-07-07_01:45:00_b4warm_a    2010-07-07 01:00:00     1       2010-07-07 01:45:00     cfc     closed  a2
tsoil_sc       tsoil_avg1_sc   20.47   20.4699993133545 
>
> I was tying to get two records out of this set, with the 'avg" column representing the mean of the first and last
fourof each 15 minute records. 
>
> Suggestions?

Are you using an explicit GROUP BY?

--
Jason Dusek
pgp // solidsnack // C1EBC57DC55144F35460C8DF1FD4C6C1FED18A2B