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?
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
