On Wednesday 27 December 2006 17:12, Ashley M. Kirchner wrote:
> Chris Comparini wrote:
> > Try something like this:
> >
> > select hour(time) as the_hour, avg(temp_f) as average_temp_f
> > from data
> > where time > now() - interval 24 hour
> > group by the_hour;
>
> Thanks Chris. Question though, why is it that when I change that to
> 48 hours, it only gives me 24 results (starting 48 hours ago to 24 hours
> ago) as opposed to 48 results (starting 48 hours ago to now) ?
Ahh, that is because hour() returns the hour of the day, as a number
between 0 and 23, regardless of the day. So, when it groups by hour,
everything in each hour from each day is grouped together.
One thing you can do is group by the day of the month as well:
select day(time) as the_day,
hour(time) as the_hour,
avg(temp_f) as average_temp_f
from data
where time > now() - interval 24 hour
group by the_day, the_hour;
Alternately (and this may be slower) you can select the hour
as some unambiguous string, using date_format():
select date_format(time, '%M %d %H:00') as the_hour,
avg(temp_f) as average_temp_f
from data
where time > now() - interval 24 hour
group by the_hour;
Here, the date_format() will return a string which contains
(in this case) the month ("%M") and day ("%d") as well, which
disambiguates the grouping. You'll probably want a different
format, or possibly a different date/time function there, but
you get the idea.
Sorry if that first reply was misleading!
-Chris
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
