From: Dan Nelson
Date: January 6 2007 5:21pm
Subject: Re: Selecting avg()...
List-Archive: http://lists.mysql.com/mysql/204264
Message-Id: <20070106172138.GC21598@dan.emsphone.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

In the last episode (Jan 06), Ashley M. Kirchner said:
> 
>    This is a continuation of something I started with a few weeks ago.  
> So, here's the previous information again:
> 
> +------------+-----------------------+------+-----+-------------------+----------------+ 
> | Field      | Type                  | Null | Key | Default           |  Extra         |
> +------------+-----------------------+------+-----+-------------------+----------------+ 
> | uid        | mediumint(8) unsigned | NO   | PRI | NULL              |  auto_increment|
> | temp_f     | float(4,1)            | YES  |     | NULL              |                |
> | temp_c     | float(4,1)            | YES  |     | NULL              |                |
> | windchill  | float(4,1)            | YES  |     | NULL              |                |
> | dewpoint   | float(4,1)            | YES  |     | NULL              |                |
> | time       | timestamp             | NO   |     | CURRENT_TIMESTAMP |                |
> +------------+-----------------------+------+-----+-------------------+----------------+ 
> 
>    This table gets information every minute.  And for a '24 hour' 
> graph, I perform the following select:
> 
>    select day(time) as the_day,
>           hour(time) as the_hour,
>           avg(temp_f) as avg_temp_f,
>           avg(temp_c) as avg_temp_f,
>           avg(windchill) as avg_windchill,
>           avg(dewpoint) as avg_dewpoint
>           from data
>           where time > now() - interval 24 hour
>           group by the_day, the_hour;
> 
>    And this returns the data I need.  However, I'd like a finer grain 
> select, if possible.  For example, instead of it averaging each 60 
> minute period, I'd like an average per 15 minute.  So that I get 4 data 
> points returned per hour, instead of just one.

select from_unixtime(floor(unix_timestamp(time)/900)*900) as range_start, 
 avg(temp_f) as avg_temp_f, avg(temp_c) as avg_temp_f,
 avg(windchill) as avg_windchill, avg(dewpoint) as avg_dewpoint
 from data
 where time > now() - interval 24 hour 
 group by floor(unix_timestamp(time)/900)

should do the trick.  Actually a TIME_GROUP(timestamp, interval)
function would be really handy for stuff like this: GROUP BY
TIME_GROUP(time, interval 15 minute).

-- 
	Dan Nelson
	dnelson@stripped