| I am trying to some up with an approach that uses "date_truc" to aggregate 15 minute time series data to hourly bins. My current query which utilizes a view, does performs a join after which I use a series a WHERE statements to specify which of the 15 minute records I want to look at. I think what I need to do is to add a date_truc function to this query which would aggregate the 15 minute records to hourly means by plot. In other words each of the bolded records listed below (the four records from plot e2 with a timestamp from hour 15 would get averaged to a single record. I can see in the docs that date_trunc uses a select statement to grab the specified time unit. SELECT date_trunc('hour', TIMESTAMP '2001-02-16 20:38:40');
Result: 2001-02-16 20:00:00However, I am unclear as to how I need to implement this in my query in such a way that the aggregation averages "value" by "plot". Any suggestions would be appreciated. SELECT data_key.site, data_key.canopy, data_key.interval, data_key.treatment_code, data_key.treatment_abbr, data_key.plot, fifteen_min_stacked_proper.* FROM data_key, fifteen_min_stacked_proper WHERE data_key.variable_channel = fifteen_min_stacked_proper.variable AND data_key.block_name = fifteen_min_stacked_proper.block_name AND 2012 = EXTRACT(YEAR FROM time2) --AND fifteen_min_stacked_proper.block ~ 'b4warm_[ace]' AND fifteen_min_stacked_proper.value IS NOT NULL AND fifteen_min_stacked_proper.variable ~ 'scld' Here is a snip of the query output: site canopy interval plot rowid time2 block variable value cfc open 0:00:15 e2 2009-03-19_15:00:00_b4warm_e 3/19/09 15:00 b4warm_e scldout_avg1 0 cfc open 0:00:15 e8 2009-03-19_15:00:00_b4warm_e 3/19/09 15:00 b4warm_e scldout_avg2 0 cfc open 0:00:15 e1 2009-03-19_15:00:00_b4warm_e 3/19/09 15:00 b4warm_e scldout_avg3 0 cfc open 0:00:15 e5 2009-03-19_15:00:00_b4warm_e 3/19/09 15:00 b4warm_e scldout_avg4 0 cfc open 0:00:15 e2 2009-03-19_15:15:00_b4warm_e 3/19/09 15:15 b4warm_e scldout_avg1 0 cfc open 0:00:15 e8 2009-03-19_15:15:00_b4warm_e 3/19/09 15:15 b4warm_e scldout_avg2 0 cfc open 0:00:15 e1 2009-03-19_15:15:00_b4warm_e 3/19/09 15:15 b4warm_e scldout_avg3 0 cfc open 0:00:15 e5 2009-03-19_15:15:00_b4warm_e 3/19/09 15:15 b4warm_e scldout_avg4 0 cfc open 0:00:15 e2 2009-03-19_15:30:00_b4warm_e 3/19/09 15:30 b4warm_e scldout_avg1 7999 cfc open 0:00:15 e8 2009-03-19_15:30:00_b4warm_e 3/19/09 15:30 b4warm_e scldout_avg2 7999 cfc open 0:00:15 e1 2009-03-19_15:30:00_b4warm_e 3/19/09 15:30 b4warm_e scldout_avg3 3579 cfc open 0:00:15 e5 2009-03-19_15:30:00_b4warm_e 3/19/09 15:30 b4warm_e scldout_avg4 3579 cfc open 0:00:15 e2 2009-03-19_15:45:00_b4warm_e 3/19/09 15:45 b4warm_e scldout_avg1 7999 cfc open 0:00:15 e8 2009-03-19_15:45:00_b4warm_e 3/19/09 15:45 b4warm_e scldout_avg2 7999 cfc open 0:00:15 e1 2009-03-19_15:45:00_b4warm_e 3/19/09 15:45 b4warm_e scldout_avg3 4000 cfc open 0:00:15 e5 2009-03-19_15:45:00_b4warm_e 3/19/09 15:45 b4warm_e scldout_avg4 4000 cfc open 0:00:15 e2 2009-03-19_16:00:00_b4warm_e 3/19/09 16:00 b4warm_e scldout_avg1 7999 cfc open 0:00:15 e8 2009-03-19_16:00:00_b4warm_e 3/19/09 16:00 b4warm_e scldout_avg2 7999 cfc open 0:00:15 e1 2009-03-19_16:00:00_b4warm_e 3/19/09 16:00 b4warm_e scldout_avg3 4000 cfc open 0:00:15 e5 2009-03-19_16:00:00_b4warm_e 3/19/09 16:00 b4warm_e scldout_avg4 4000 cfc open 0:00:15 e2 2009-03-19_16:15:00_b4warm_e 3/19/09 16:15 b4warm_e scldout_avg1 7999 cfc open 0:00:15 e8 2009-03-19_16:15:00_b4warm_e 3/19/09 16:15 b4warm_e scldout_avg2 7999 cfc open 0:00:15 e1 2009-03-19_16:15:00_b4warm_e 3/19/09 16:15 b4warm_e scldout_avg3 4000 cfc open 0:00:15 e5 2009-03-19_16:15:00_b4warm_e 3/19/09 16:15 b4warm_e scldout_avg4 4000 cfc open 0:00:15 e2 2009-03-19_16:30:00_b4warm_e 3/19/09 16:30 b4warm_e scldout_avg1 7999 cfc open 0:00:15 e8 2009-03-19_16:30:00_b4warm_e 3/19/09 16:30 b4warm_e scldout_avg2 7999 cfc open 0:00:15 e1 2009-03-19_16:30:00_b4warm_e 3/19/09 16:30 b4warm_e scldout_avg3 4000 cfc open 0:00:15 e5 2009-03-19_16:30:00_b4warm_e 3/19/09 16:30 b4warm_e scldout_avg4 4000 cfc open 0:00:15 e2 2009-03-19_16:45:00_b4warm_e 3/19/09 16:45 b4warm_e scldout_avg1 3889 cfc open 0:00:15 e8 2009-03-19_16:45:00_b4warm_e 3/19/09 16:45 b4warm_e scldout_avg2 3882 cfc open 0:00:15 e1 2009-03-19_16:45:00_b4warm_e 3/19/09 16:45 b4warm_e scldout_avg3 4000 cfc open 0:00:15 e5 2009-03-19_16:45:00_b4warm_e 3/19/09 16:45 b4warm_e scldout_avg4 4000 cfc open 0:00:15 f4 2009-03-19_16:45:00_b4warm_f 3/19/09 16:45 b4warm_f scldout_avg1 7999 cfc open 0:00:15 f6 2009-03-19_16:45:00_b4warm_f 3/19/09 16:45 b4warm_f scldout_avg2 7999 cfc open 0:00:15 f3 2009-03-19_16:45:00_b4warm_f 3/19/09 16:45 b4warm_f scldout_avg3 3769 cfc open 0:00:15 f5 2009-03-19_16:45:00_b4warm_f 3/19/09 16:45 b4warm_f scldout_avg4 3769 cfc closed 0:00:15 a2 2009-03-19_17:00:00_b4warm_a 3/19/09 17:00 b4warm_a scldout_avg1 7999 |
