date_trunc to aggregate by timestamp? - Mailing list pgsql-general
| From | Kirk Wythers |
|---|---|
| Subject | date_trunc to aggregate by timestamp? |
| Date | |
| Msg-id | E567378B-A2A2-48E5-9744-56323D881300@umn.edu Whole thread Raw |
| List | pgsql-general |
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
pgsql-general by date: