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:00
However, 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
