[SOLVED] Statistics Gathering By DateTime

[lead2gold]

Is it possible to retrieve hourly (or on an interval) statistics from the database?

For example...

assume the following table simplified to show all the fields i require info from):

table a
{
    pkey BIGINT(20);
    userId CHAR(4);
    logEntry DATETIME;
}

I want to be able to see how many entries there are for user 'XXXX' ever hour... for a given time range.

thus... the output looking something like:

intervalf | intervalt | count
2007-12-31 00:00:00 | 2007-12-31 01:00:00 | 12
2007-12-31 01:00:00 | 2007-12-31 02:00:00 | 112
2007-12-31 02:00:00 | 2007-12-31 03:00:00 | 12
2007-12-31 03:00:00 | 2007-12-31 04:00:00 | 132
... etc

 

is this possible?

 

Chris

 

p.s. Happy new year!

[fenway]

You'll have to extra the hour from the datetime with DATE_FORMAT().

[drranch]

will you be running it every hour manually?

 

If not you will need to set the MYSQL/PHP script up with cron to run every hour.

[lead2gold]

Thanks, i solved it actually;  The purpose of it is to generate statistics that certain actions took place on our system at any given hour, or for a specified number of hours.

 

I had to iterate through the date-time they specified and dump the results into a temporar table.

 

From there i could generate all the statistics i ever needed from the new temporary table. The statistics are used for in-house so the overhead fo the temporary table isn't a problem since only few people have access to run the generation.

 

Thanks though for your feedback.