Graphs over time, where to store data and how to?

Hi there, interesting one here. Usually I take a problem like this, sit down with MySQL and play about but i'm coming stuck and would like to know what you guys think or what you best practices are!

OK, so lets say I'm making a graph on my home page, its a line graph and has 12 dots, each is number of hits every-hour.

To log all hits you could have a bit of code that goes in mysql and updates that number by one to show all hits, but to hold back data what is the best practice, some script to move data down one column or im not sure, im stuck to be honest?!

Apologies if this has already been raised, my googling was epic failed..

Thanxs Guys, oh since its my first time here, Hi!

Alex Barlow ARBarlow

Alex Barlow wrote: [...]

OK, so lets say I'm making a graph on my home page, its a line graph and has 12 dots, each is number of hits every-hour.

(This is not really a Rails question...)

So you want hits for the last 12 hours?

To log all hits you could have a bit of code that goes in mysql and updates that number by one to show all hits, but to hold back data what is the best practice, some script to move data down one column

No need. Just use appropriate criteria in your DB query...something like (from memory; untested):

Hit.count :conditions => ['created_at > ?', 12.hours.ago], :group => 'hour(created_at)'

Remember, your database will do a lot of the work for you if you're nice to it. (If you use PostgreSQL, it will do even more...but that's another post.)

Best,

Ok, so your saying that mysql holds back data automatically? I have never heard of this..

Hit.count :conditions => ['created_at > ?', 12.hours.ago], :group => 'hour(created_at)'

this piece of code would require multiple rows each with a different time, so that would require a table of hits etc. but how would you log those into the table ever hour?, day?, week? etc, im talking not only about hits but user statistics, profile views for example?

Thanxs for your reply, Alex

Hi there, interesting one here. Usually I take a problem like this, sit down with MySQL and play about but i'm coming stuck and would like to know what you guys think or what you best practices are!

OK, so lets say I'm making a graph on my home page, its a line graph and has 12 dots, each is number of hits every-hour.

What is a 'hit'?

Website hits, visitors..

Website hits, visitors..

Ah, I understand. So is the question (or at least the first part of the question) how to log website hits in a Rails app? Is the best way to log the data to the database or would it be possible to parse the apache (or whatever) log I wonder?

Colin

Alex Barlow wrote:

Ok, so your saying that mysql holds back data automatically? I have never heard of this..

Unless I'm misunderstanding what you mean by "holds back data", that's what a DB does!

Hit.count :conditions => ['created_at > ?', 12.hours.ago], :group => 'hour(created_at)'

this piece of code would require multiple rows each with a different time, so that would require a table of hits etc. but how would you log those into the table ever hour?, day?, week? etc,

There's an Apache module (can't recall the name) that will put the access logs in a mySQL table instead of a text file...

im talking not only about hits but user statistics, profile views for example?

This would have to be done in the application. For example, in ProfilesController#view, you'd need to insert a stats record into the appropriate table (or just increment the view count, depending on your DB structure). I wonder if there's a Rails plugin to makw this easier.

Thanxs for your reply, Alex

Best,