[Postgres] Select avg of a days worth of data

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Weiry
Forum Contributor
Posts: 323
Joined: Wed Sep 09, 2009 5:55 am
Location: Australia

[Postgres] Select avg of a days worth of data

Post 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?
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

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

Post by Christopher »

Why doesn't it get the averages quite right? Remember that timestamps are usually saved in GMT.
(#10850)
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

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

Post by VladSun »

I think you should

Code: Select all

GROUP BY date_trunc('day',time_stamp)
in your first query.
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
Weiry
Forum Contributor
Posts: 323
Joined: Wed Sep 09, 2009 5:55 am
Location: Australia

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

Post 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.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

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

Post by VladSun »

You should also remove the DISTINCT clause - the GROUP BY clause makes its usage senseless.
There are 10 types of people in this world, those who understand binary and those who don't
Post Reply