Re: [GENERAL] date_trunc to aggregate values?
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 sitecanopy plot
variablenamevalue avg
2010-07-07_00:00:00_b4warm_a2010-07-07 00:00:00 1 2010-07-07
00:00:00 cfc closed a2 tsoil_sctsoil_avg1_sc 21.06
21.054659424
2010-07-07_00:15:00_b4warm_a2010-07-07 00:00:00 1 2010-07-07
00:15:00 cfc closed a2 tsoil_sctsoil_avg1_sc 20.96
20.950844727
2010-07-07_00:30:00_b4warm_a2010-07-07 00:00:00 1 2010-07-07
00:30:00 cfc closed a2 tsoil_sctsoil_avg1_sc 20.88
20.871607666
2010-07-07_00:45:00_b4warm_a2010-07-07 00:00:00 1 2010-07-07
00:45:00 cfc closed a2 tsoil_sctsoil_avg1_sc 20.8
20.792370605
2010-07-07_01:00:00_b4warm_a2010-07-07 01:00:00 1 2010-07-07
01:00:00 cfc closed a2 tsoil_sctsoil_avg1_sc 20.72
20.713133545
2010-07-07_01:15:00_b4warm_a2010-07-07 01:00:00 1 2010-07-07
01:15:00 cfc closed a2 tsoil_sctsoil_avg1_sc 20.64
20.633896484
2010-07-07_01:30:00_b4warm_a2010-07-07 01:00:00 1 2010-07-07
01:30:00 cfc closed a2 tsoil_sctsoil_avg1_sc 20.55
20.542370605
2010-07-07_01:45:00_b4warm_a2010-07-07 01:00:00 1 2010-07-07
01:45:00 cfc closed a2 tsoil_sctsoil_avg1_sc 20.47
20.463133545
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?
--
Jason Dusek
pgp // solidsnack // C1EBC57DC55144F35460C8DF1FD4C6C1FED18A2B
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] date_trunc to aggregate values?
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 sitecanopy plot
variablenamevalue avg
2010-07-07_00:00:00_b4warm_a2010-07-07 00:00:00 1 2010-07-07
00:00:00 cfc closed a2 tsoil_sctsoil_avg1_sc 21.06
21.054659424
2010-07-07_00:15:00_b4warm_a2010-07-07 00:00:00 1 2010-07-07
00:15:00 cfc closed a2 tsoil_sctsoil_avg1_sc 20.96
20.950844727
2010-07-07_00:30:00_b4warm_a2010-07-07 00:00:00 1 2010-07-07
00:30:00 cfc closed a2 tsoil_sctsoil_avg1_sc 20.88
20.871607666
2010-07-07_00:45:00_b4warm_a2010-07-07 00:00:00 1 2010-07-07
00:45:00 cfc closed a2 tsoil_sctsoil_avg1_sc 20.8
20.792370605
2010-07-07_01:00:00_b4warm_a2010-07-07 01:00:00 1 2010-07-07
01:00:00 cfc closed a2 tsoil_sctsoil_avg1_sc 20.72
20.713133545
2010-07-07_01:15:00_b4warm_a2010-07-07 01:00:00 1 2010-07-07
01:15:00 cfc closed a2 tsoil_sctsoil_avg1_sc 20.64
20.633896484
2010-07-07_01:30:00_b4warm_a2010-07-07 01:00:00 1 2010-07-07
01:30:00 cfc closed a2 tsoil_sctsoil_avg1_sc 20.55
20.542370605
2010-07-07_01:45:00_b4warm_a2010-07-07 01:00:00 1 2010-07-07
01:45:00 cfc closed a2 tsoil_sctsoil_avg1_sc 20.47
20.463133545
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
