Pieter Bron February 2016

Trying to take the average of 60 points in an hour, then output in graph

I have a database running, which gets new data stored every minute. So, in one hour there are 60 entries. From those entries, I'm trying to generate the average per hour (using timestamp), but I haven't got the faintest on how I go about doing that.

Here's my MySQL selection code for getting all the data from last hour:

$value = "SELECT `brutoValue`, `nettoValue`, `regDate`
                    FROM `myDB` 
                    WHERE regDate > DATE_SUB( 
                    CURRENT_TIMESTAMP , INTERVAL 1 HOUR )";
$result = $dbhandle->query($value);

And here's my MySQL code for getting all the data from last week (168 hours in a week, right?):

$valueWeek = "SELECT `brutoValue`, `nettoValue`, `regDate`
                    FROM `myDB` 
                    WHERE regDate > DATE_SUB( 
                    CURRENT_TIMESTAMP , INTERVAL 168 HOUR )";
$resultWeek = $dbhandle->query($valueWeek);

The latter renders an extensive list with 10080 entries (168 * 60). What I need is that for every hour, the 60 entries are summed up, divided by 60, and the result added to an array of average values over the past 168 hours.

While I got it working for the last hour, I can't figure out how to take the averages. This is the code for storing the values of the last hour into arrays which I can use for my graph:

if ($result->num_rows > 0) {
        // output data of each row
        while($row = $result->fetch_assoc()) {
            $dateArray[] = $row["regDate"];
            $brutoVal[] = $row["brutoValue"];
            $nettoVal[] = $row["nettoValue"];
        } 

    }

Could anyone help me out please? I'm at loss here. Thanks in advance.

Answers


Bob Nocraz February 2016

You could use mysql's date functions to group by date and hour. Notice the GROUP BY clause at the end and the AVG() usage in the columns.

$valueWeek = "SELECT AVG(`brutoValue`) AS `brutoValue`, AVG(`nettoValue`) AS `nettoValue`, CONCAT(DATE(`regDate`), '_', HOUR(`regDate`)) AS `date_hour`
                    FROM `myDB` 
                    WHERE regDate > DATE_SUB( 
                    CURRENT_TIMESTAMP , INTERVAL 168 HOUR )
                    GROUP BY DATE(`regDate`), HOUR(`regDate`)";

Your return data should have a single row for every hour of the day(that is in the db) with an averaged value for bruto and netto.

EDIT: slightly changed the mysql above to have a concatenated value of date_hour which would be something like 2016-02-08_17, so you could output the data into an array during your while loop (to match your previous setup) like:

while($row = $result->fetch_assoc()) {
    $dateArray[] = $row["date_hour"];
    $brutoVal[] = $row["brutoValue"];
    $nettoVal[] = $row["nettoValue"];
}

Post Status

Asked in February 2016
Viewed 2,887 times
Voted 9
Answered 1 times

Search




Leave an answer