Geek Answers Handbook

SQL query to get average value for given time periods

I have a MySQL database that is used to store power readings, with reads being added once per minute. (i.e. 1440 counts per day).

time      power
----      -----
00:00:00    346
00:01:00    352
00:02:00    247

Using PHP, I want to create a graph from this data, but I do not want 1440 points on the graph. I could split it into 15 minute pieces (which will give me 96 points). In addition, I do not want to simply accept every fifteenth value, as this will give incorrect results. I want to use an SQL query that returns data in 15 minute blocks and gives a power reading as an average.

The result might look something like this:

starttime   avgpower
---------   --------
00:00:00         342
00:15:00         490
00:30:00         533

Is there an SQL function that will do this for me? or will I have to do this calculation in my php code?

+4
3

:

SELECT STR_TO_DATE(CONCAT(DATE_FORMAT(`time`, '%H'), ':', (FLOOR(DATE_FORMAT(`time`, '%i') / 15) * 15), ':00'), '%H:%i:%s') `starttime`, AVG(`power`) `avgpower`
FROM `tablea`
GROUP BY `starttime`;

(tablea) (time power) .

, .

+2

/4, . , .

+2

. Don Kirkby, :

SELECT time, AVG(power) AS avgpower,  ROUND(UNIX_TIMESTAMP(time)/(15*60)) AS timekey
FROM table
GROUP BY timekey
0

More articles:


All Articles