Best way to make graphs with data

AshleyS

Hello,

I store continuous updating data every 60 seconds, and I want to use the JQuery graphing tool called Flot (http://code.google.com/p/flot/).

I would like to show the data in datasets of 'Per hour' and 'Per day', but I have no idea how to show this, and what data to use. I'm familiar with SQL and PHP, but when it comes to math - not so bright!

Can someone please help me out and point me in the right direction of what queries I need to run and what data to output?

Many thanks.

johanafm

AshleyS;10936563 wrote:
Can someone please help me out and point me in the right direction of what queries I need to run and what data to output?

That would depend entirely on the data you have and how it is stored.

Consider the two grapsh "Users online" (1) and "Average users online" (2), showing time on the x-axis (horizontal) and number of users on the y-axis. Furthermore, let's assume your x-axis scale is whole hours.
For (1), you'd simply retrieve the number of users online at the full hours: 10, 11, 12 etc.
For (2), you'd need to calculate the average, meaning you'd pull all data you have about users online, down to a limit suiting your needs. You may store information on number of users online 10 times per second, that is once per 0.1 seconds.
"Your needs" when showing average users may not be very precise, meaning you could fetch the exact same data as in (1). Or you might need very accurate data, meaning you'd pull ALL the stored data and calculate the average from that.

But, should there be a point to using data from that small time intervals, you'd probably want higher x-axis granularity as well.

AshleyS

Thank you for you reply.

The data I have stored are ping times. I have these stored in a MySQL database, and a new row gets inserted every 60 seconds. The time of the record is stored as a Unix Timestamp, using time().

What I wanted to accomplish is a graph to demonstrate when the ping is 0, since the majority of the time it will be a normal value of around 60-100. (I have stored a ping of 0 when I failed to get a response - i.e. the server was offline)

I would assume I would need to do an average of all the rows from a timestamp between time() and (time() - (86400 * no. of days in month)). But where would I go from there?

Many thanks.

johanafm

To me it sounds like what you want is rather the number of times per time interval (hour, day, week...) that you have a ping time of 0. So, what you want is something like

-- Statistics for last month, count of timeouts per day
SELECT count(*), date_format(ping_time, '%Y-%m-%d') AS day
FROM ping_time_table
WHERE ping_time  BETWEEN UNIX_TIMESTAMP(CURDATE() - INTERVAL 1 month) AND UNIX_TIMESTAMP(CURDATE())
GROUP BY day
ORDER BY day ASC

And for other time intervals you just change the date formats

-- grouped by hour
date_format(ping_time, '%Y-%m-%d %H') AS hour