Overview
This is no easy task. You're looking at a lot of backend work and graphing. Lot's of graphing. So I did it all for you.
So from my understanding you want the following:
- To be able to average the temperature values.
- To be able to sort the data / display the data by week, month etc.
- To have a nice looking, modern, interactive chart/graph.
- To have a clean, efficient, effective way of storing and working with the data.
This whole answer works off of my knowledge of Flot, and the assumption you will be using it, however most of the knowledge and code here can be applied to almost all of the javascript charting libraries.
1. Averaging values
Flot has an Averaging Plugin which places a line across the graph to represent the average. This lets you see graphically where the temperature has fluctuated around this average over time. I'd imagine a sinusoidal pattern between night and day. This works well enough for me, however I do want an actual number for this average.
Using this Sum values from an Array link here, we can extract the average value from the data format that Flot expects if we modify the code slightly. I have done this below.
var myData = [['2013-01-22', 0], ['2013-01-29', 1], ['2013-02-05', 21]];
var myTotal = 0; // Variable to hold your total
var myAverage = 0; // Variable to hold your average
var myNumber = 0; // Variable to hold the number of items to divide by
for(var i = 0, len = myData.length; i < len; i++) {
myTotal += myData[i][1]; // Iterate over your first array and then grab the second element add the values up
myNumber++; // This will give us the number of items we added
}
myAverage = myTotal / myNumber;
document.write(myTotal); // 22 in this instance
document.write(myNumber); // 3 in this instance (useful for knowing how many data points you have)
document.write(myAverage); // 7.3 in this instance
I am not sure what you mean by
how often should I average the data?
Not sure if you want to get rid of data points or just want an average or... But you don't really need to get rid of data points. The average is done above. If you are desperate to save space/processing time with less data points, there is a Downsample Plugin for Flot that removes data points without making a visible difference, exactly for this reason.
2. Displaying the data
Again with the underlying assumption you will end up using Flot, this function is built into Flot. I imagine you have a timestamp for the data you collect. By graphing over time in Flot, you are able to use buttons to easily show the data from either a month, two months, the last day...any time period you want. This is shown in this example Time Series Interaction.
If you want an even better form of interactivity over time, look at the Interactivity Examples for Flot.
3. A nice looking graph
This comes naturally with Flot! You can adjust the colours of different regions, choose to have fill or not, but really, they look good already.
4. Storing and working with the data
Now, there are three outcomes here but I will only be covering two.
- You keep your data in the database.
- You revert to using log files.
- You go real-time (not covered).
Overview
This part attempts to answer
The question I have is more around what is the best method of storing the data. At the moment I poll the temperature every minute and store everything. I then use MySQL queries to get the data for:
Last Hour - MySQL query pulls the last 60 readings (outputs 60 values)
Last Day - MySQL query averages 60 readings for an hour average (outputs 24 values)
Last Week - MySQL query averages hourly readings for an day average (outputs 7 values)
I am really not sure that this is the best method of getting daily and weekly readings and then I would also like monthly and yearly readings.
Can anyone suggest a better way to work with the data? Better queries as they are quite mathematically intensive, would it be better to store the monthly readings in another table of the averaged readings
In my personal opinion the best method of storing the data is in the exact format that the charting library wants. However, if you aren't going real-time than you can pretty much use whatever is easiest, and since you aren't loading millions of data points very quickly it shouldn't matter/make much of a difference.
I'm assuming you did use logs for RRDtool, and now MySQL. For the best method of getting daily and weekly reading, I don't think that's relevant to MySQL. The backend's job is to collect, store and present the data. The front end, a.k.a. Flot will sort this data for you and present the daily and weekly readings, as discussed in part two. This saves you from these complicated queries.
My suggestion for working with the data better is to let the backend not work with the data with queries and separate files for averages etc, but to simply present it to Flot, and let Flot handle the data to present only the data you want. Now for getting the backend to graph the data...
Flot will be expecting data in the format of:
[[timestamp, data], [timestamp, data]]
So let's get it there.
1. MySQL/PHP
Ok, so you can't be bothered to move back to logs. Or you never used them, whichever.
You have to get the MySQL values to the right format, with the right timestamp. Hmm. Now, I don't know how you are storing the timestamp, so let's assume you work off the knowledge that you poll for data every minute (crontab?). We don't know when you started polling this data, so we will have to work backwards with time.
First, get the data.
I found this Export MySQl Table To File pretty quickly. The code below will get the data from your MySQL table and write it into a file. Handy. I'm working off the assumption that each value gets a new line.
<?php
$pdo = new PDO(...);
$results = $pdo->query("SELECT * FROM myTable INTO OUTFILE 'data.txt'");
$dummy = $result->fetchAll();
?>
Right. We have the data in a file, now for the timestamp.
<?php
$time = time() * 1000;
$file = fopen("data.txt", "w+");
foreach(file("data.txt") as $line) {
if ($file > 0) {
$write = ", [" . $time . ", " . $line . "]";
fwrite($file, $write);
} else {
$write = "[" . $time . ", " . $line . "]";
fwrite($file, $write);
}
$time = $time - 60000;
}
fclose($file);
?>
Ok, let me explain that, line by line.
The javascript library will be expecting epoch time in milliseconds, but PHP presents it in seconds, so multiply it by one thousand.
Next, we want to write your data output to a file. This is easy to define in Flot and also gives you more flexibility later. So we open the file early, to present it getting opened every time you parse a new line.
Then we go through line by line and write the data to the file in the appropriate format with the timestamp in front. This assumes the data was just collected.
Then, each iteration of this goes back one minute (60000 milliseconds) so it works backward in time. Flot will process this for you.
Then, we close the file.
After this, Flot can read the data from the file. Nicely done.
2. PHP/Logs
Ok, this is interesting. You reverted back to using RRDtool's log format that I'm completely assuming it has.
I'll walk you through it with the code below.
<?php
$file = fopen("log.txt", "r");
$tempOneFile = fopen("tempOne.txt", "w+");
$tempTwoFile = fopen("tempTwo.txt", "w+");
while ($first = fscanf($file, "%s\t%s\t%s\n")) {
list ($timestamp, $temperatureone, $temperaturetwo) = $first;
$time = $timestamp * 1000;
if ($file > 0) {
$one = ", [" . $time . ", " . $temperatureone . "]";
$two = ", [" . $time . ", " . $temperaturetwo . "]";
$fwrite($tempOneFile, $one);
$fwrite($tempOneFile, $two);
} else {
$one = "[" . $time . ", " . $temperatureone . "]";
$two = "[" . $time . ", " . $temperaturetwo . "]";
$fwrite($tempOneFile, $one);
$fwrite($tempOneFile, $two);
}
}
fclose($file);
?>
Ok. So there might be a better way of handling the data, but I personally like to create files with the data in the correct format. It's easy to read and, as you wanted, it is extremely flexible.
That does it for a log file.
3. Real-time/Nodejs
It was daunting at first, going from PHP/MySQL to real-time javascript and web sockets and all. But it's not that bad. Basically, you stream your data through a web socket. But I won't cover this, as it is not what you need for temperatures.
Conclusion
I hope this extremely long answer covers your question. Next step is actually turning your data into a graph with Flot. Throughout this answer, I made a lot of assumptions about the format of your data. Please do not be afraid to comment if something doesn't work, is different, you can't figure out how to use it or anything like that. Thank you!
