On 2013-01-24, Kirk Wythers <wythe001@umn.edu> wrote:
> 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.
you probaly want to do a
group by date_trunc('hour', time2)
http://www.postgresql.org/docs/9.2/interactive/queries-table-expressions.html#QUERIES-GROUP
probalbly want to group by several other columns too.
and use the avg() agregate on others.
http://www.postgresql.org/docs/9.2/interactive/functions-aggregate.html
it's probably easiest to start with a query that only returns two
columns and then add columns to it once it does what you want.
SELECT
avg(fifteen_min_stacked_proper.value)
date_trunc('hour',fifteen_min_stacked_proper.time2)
FROM
fifteen_min_stacked_proper
WHERE
fifteen_min_stacked_proper.variable='scldout_avg1'
GROUP BY
date_trunc('hour',fifteen_min_stacked_proper.time2);
--
⚂⚃ 100% natural
