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
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
