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,