Re: date_trunc to aggregate by timestamp?

On 2013-01-24, Kirk Wythers <wythe001(at)umn(dot)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