Thread: date_trunc to aggregate by timestamp?

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. 

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