Page 1 of 1

[Postgres] Select avg of a days worth of data

Posted: Mon Jul 12, 2010 10:57 pm
by Weiry
Im new to PGSQL however im fairly used to MySQL.

I am trying to create a query to return the following result set:
[sql]
day | avg(valuePerDay)
13 | 22.2
14 | 23.2
15 | 22.6
[/sql]

The data in the database:
[sql]`id`,`timestamp`, `value`
('1','2010-07-13 1:32:26+00','22.3')[/sql]

Basically i am trying to group an average of `value` for a single day using date_trunc('day',timestamp)

This is my initial query:
[sql]select distinct date_trunc('day',time_stamp) as time_stamp, avg(value) as value from data WHERE id = 1 group by time_stamp order by time_stamp asc[/sql]

However it doesnt seem to get the average of the entire days worth, so i changed it to:
[sql]select distinct date_trunc('day',time_stamp) as time_day, (select avg(value) from data where date_trunc('day',time_stamp) = time_day) as value from data where id = 1 group by time_day order by time_day asc[/sql]

Now im receiving the error:
Warning: pg_query() [function.pg-query]: Query failed: ERROR: column "poll_day" does not exist LINE 1: ... sensor_data where date_trunc('day',poll_stamp) = poll_day)

Is there any way to make this query smaller or get it working properly without a subquery?

Re: [Postgres] Select avg of a days worth of data

Posted: Tue Jul 13, 2010 1:05 pm
by Christopher
Why doesn't it get the averages quite right? Remember that timestamps are usually saved in GMT.

Re: [Postgres] Select avg of a days worth of data

Posted: Tue Jul 13, 2010 1:47 pm
by VladSun
I think you should

Code: Select all

GROUP BY date_trunc('day',time_stamp)
in your first query.

Re: [Postgres] Select avg of a days worth of data

Posted: Tue Jul 13, 2010 6:23 pm
by Weiry
VladSun wrote:I think you should

Code: Select all

GROUP BY date_trunc('day',time_stamp)
in your first query.
That worked wonders.
Much better than the sub query.

Re: [Postgres] Select avg of a days worth of data

Posted: Wed Jul 14, 2010 4:53 am
by VladSun
You should also remove the DISTINCT clause - the GROUP BY clause makes its usage senseless.