On Feb 4, 2013, at 3:26 PM, Jason Dusek <jason.du...@gmail.com> wrote:
> 2013/2/4 Kirk Wythers <wythe...@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 four of each 15 minute records. >> >> Suggestions? > > Are you using an explicit GROUP BY? > Here is what I have in the GROUP BY clause GROUP BY date_trunc('hour', derived_tsoil_fifteen_min_stacked.time2), derived_tsoil_fifteen_min_stacked.time2, data_key.site, data_key.canopy, data_key.variable_name, data_key.plot -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general