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.

 

This is the query I'm using...

$query = "SELECT * FROM (SELECT * FROM outside ORDER BY id DESC LIMIT $limit) AS foo ORDER BY id ASC";

	    $result = mysql_query($query);
	    while($row = mysql_fetch_assoc($result))
	    {
	    $dataset1[] = array(($row['unixtime'] * 1000 ),$row['temperature']);
            }