I bought a waterproof temperature sensor for one of my RPis to put outdoors. It is currently taking a reading every minute and putting the data into a MySQL database.

What is put into the DB is....



I tried to put as much as possible into each entry to make it easier if I want to order or sort by a certain method.

I want to put this data into a nice line graph to show the data in a pretty fashion.

It seems there's loads of chart packages out there that look very nice but I'm yet to figure out most of them properly or find any decent guides. I have used phpgraphlib in the past but that couldn't handle the amount of data I have. I have though managed to setup "RGraph" and it looks okay as a start...



But there's definitely issues....

• There is shit loads of data. 1 reading every minute means 1440 data points per day.
• Sometimes there are some data points missing - either due to a false reading which is discarded or a reboot of the RPi every few hours for stability. These aren't added to the DB as empty or anything, they just don't exist in the DB so I'd like to account for that. Essentially having the y-axis as a set length of time with the data points plotted based on matching up what is determined by the y-axis
• The large amount of data means the current y-axis labels are pointless, as you can see. I'd like to show just the day and hour.

Has anyone successfully done anything like this? Any ideas?

I'll keep searching because I can't be the first person to want to do something like this.

Do it all client-side using JavaScript in Flot.

The Y-axis could be wrapped on day, month or year and optionally stacked. That would make it more useful.

What Matt said – I'm using Flot for this:
http://caer.me:2988/monitor.html

And like you I'm using data at short (2-minutes in my case) intervals. Set the period to 7 days and it still handles that much data just find, even on my phone. The 'time' plugin for Flot handles the timestamp stuff on the x axis automagically.

If you need help with the API let me know, I've learned quite a bit from doing that :)

Thanks. I'll definitely give Flot a look, it certainly generates pretty graphs.

How do you format the date for each entry?

In the `options` object you pass to $.plot():

options = {
        xaxes: [{
            tickFormatter: xTickFormatter,
            mode: "time",
            timezone: "browser"
        }],

and xTickFormatter looks like this:

function xTickFormatter (val, axis) {
    var d = new Date(val),
        h, m;

    h = d.getHours();
    m = d.getMinutes();
    m = m < 10 ? "0" + m : m;
    return [
        d.getDate(), " ", months[d.getMonth()],
        "<br>",
        h, ":", m
    ].join("");
}

months[] is an array defined earlier:
 

months = ["Jan", "Feb" . . . "Nov", "Dec"];

 

Thanks!

Definitely need to get a better handle on javascript.

Take a look through the source for my monitor app - there's some other stuff there you might find useful (including a handy data-smoothing thing).

Yeah, I'm currently working my way through monitor.js, hopefully won't take me too long to get something looking reasonable :$

The best thing about Flot, I reckon, is that it gives you a nice-looking graph pretty much by default.

I think the trickiest thing I did on my monitoring thing was the day/weekend shading, and maybe the smoothing, although that was more a case of me being slow to understand how Savitzky-Golay works (it's actually pretty simple).

Well I seemed to have solved the major issue of being able to tell what the date/time is by using a proper time format in the database rather than doing conversions. All new entries are added with a unix time stamp.

Heh yeah using a numeric timestamp makes things much easier, for one thing because that's what the Flot-time thing expects, and for another it makes it easier to do comparisons.

Well that's definitely much better


Now to make it look nicer. Thanks for suggesting Flot!

One thing I did to make mine look slightly nicer is to trim the start to where the time is a whole number if minutes/hours – that way you get nice round "16:00" time labels, rather than "16:42" and such.

Good idea, that would make things look a bit better.

Looking much better

http://www.antthomas.co.uk/temperature/

Only real issue is that the database is on my home server so it's a bit slow, especially on the larger time periods.

Do you have indexes on the columns you are joining / sorting on in your database tables?

Yeah, all have an id which is auto incremented.

I did think of just getting odds or evens to half the data.

What are you monitoring?

I figured you'd have some sort of fancy AJAXy thing to update in real time.

Are you sorting the data (ORDER BY) on the primary key? If you're using another column you'll be wanting to add an index to it. MySQL will really thank you if you do.

You might get better performance by storing the date and time as MySQL's native datetime column and using the MySQL UNIX_TIMESTAMP() function to convert it to a Unix timestamp for Flot, just don't order on the function results as MySQL isn't very good at that, i.e.

SELECT UNIX_TIMESTAMP(datetime_column) as unix_timestamp FROM table ORDER BY datetime_column DESC.