lead2gold Posted December 31, 2007 Share Posted December 31, 2007 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! Quote Link to comment https://forums.phpfreaks.com/topic/83855-solved-statistics-gathering-by-datetime/ Share on other sites More sharing options...
fenway Posted December 31, 2007 Share Posted December 31, 2007 You'll have to extra the hour from the datetime with DATE_FORMAT(). Quote Link to comment https://forums.phpfreaks.com/topic/83855-solved-statistics-gathering-by-datetime/#findComment-426925 Share on other sites More sharing options...
drranch Posted January 1, 2008 Share Posted January 1, 2008 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. Quote Link to comment https://forums.phpfreaks.com/topic/83855-solved-statistics-gathering-by-datetime/#findComment-427096 Share on other sites More sharing options...
lead2gold Posted January 2, 2008 Author Share Posted January 2, 2008 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. Quote Link to comment https://forums.phpfreaks.com/topic/83855-solved-statistics-gathering-by-datetime/#findComment-428166 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.