mysql - Average of data for every 5 minutes in the given times

Question

I have a problem with MySQL. I need the average of data from MySQL between two particular times.

For that, I'm using:

select avg(Column) 
from Databasename.tablename 
where datetime BETWEEN '2012-09-08 00:00:00' AND '2012-09-08 15:30:00'

Now, what I need is the average of data for every 5 minutes, in the given times. By using this:

select avg(Column) 
from Databasename.Tablename 
where datetime BETWEEN '2012-09-08 15:00:00' 
                   AND '2012-09-08 15:30:00' + 'INTERVAL 5minutes'

It's not showing any error but showing NULL.

How do I get the data for every 5mins within two particular time periods?

Answer 1

Group to intervals

You should use group by to create one group for each interval over which you want to average.

select avg(Column), convert((min(datetime) div 500)*500 + 230, datetime) as time
from Databasename.tablename 
where datetime BETWEEN '2012-09-08 00:00:00' AND '2012-09-08 15:30:00'
group by datetime div 500

This makes use of the fact that a date and time can implicitely be converted to a number, which will then be of the format YYMMDDhhmmss. Dividing such a number by 100 strips the seconds, and similarly dividing by 500 yields a unique number for every 5-minute interval.

The additional column I selected will give the middle of the interval: any time from the interval (here choosing the minimum, but it doesn't really matter), turned into that 5-minute interval number, back into a date-and-time number, and then adding two and a half minutes. You might use similar syntax to compute the beginning (simply leave the + 230 out) or (inclusive) end (+ 459) of the interval.

Tested using sqlfiddle.

End of range handling

Note that your BETWEEN range will include rows from 15:30:00 but no others from the interval started at that time. Maybe you want to exclude the end of the range from your selection:

where datetime >= '2012-09-08 00:00:00' AND datetime < '2012-09-08 15:30:00'

Why you get NULL

As to why your query yields NULL: To do interval arithmetic, you don't enclose the whole interval thing in quotes, and you use singular names for time units. I.e.

'2012-09-08 15:30:00' + INTERVAL 5 minute

But this would simply extend the range to 2012-09-08 15:35:00 and still not create those 5-minute intervals you want. The way you wrote it, you're trying to add two strings, which isn't possible in MySQL unless the strings can be converted to numbers, in which case those numbers will be added.

You might want to use interval arithmetic to compute the exclusive end of the interval, i.e. the first second after the interval:

 convert((min(datetime) div 500)*500, datetime) + INTERVAL 5 minute as endOfInterval

Simply adding 500 instead of the 230 of my query won't work as the resulting number might represent an invalid date for the last interval of each hour.