LordLanky Posted July 7, 2009 Share Posted July 7, 2009 Hi All, does anyone know any efficient way to group mysql data into 'bins' of time interval? What i want is to show a profile over time of how many times someone's page has been visited. Each time someone visits, they get added to a mysql database. So it's basically a collection of timestamps. I'd like to be able to say January: 10 Feb: 12 March: 5 etc etc.... Would it be best as part of the mysql query? or does php have some magic up its sleeve? Thanks all! Quote Link to comment Share on other sites More sharing options...
MatthewJ Posted July 7, 2009 Share Posted July 7, 2009 Probably best in mysql with COUNT and GROUP BY Something like SELECT YEAR(datefield) Y, MONTH(datefield) M, DAY(datefield) D, COUNT(*) Cnt FROM yourtable GROUP BY YEAR(datefield), MONTH(datefield), DAY(datefield) Quote Link to comment Share on other sites More sharing options...
LordLanky Posted July 7, 2009 Author Share Posted July 7, 2009 Cheers MatthewJ - how would you group it exactly? Is there a mysql 'group' for months? Quote Link to comment Share on other sites More sharing options...
josborne Posted July 7, 2009 Share Posted July 7, 2009 GROUP BY sort the results of an aggregate function such as count into groups based on charactaristics of the results. In your case, a characteristic of a timestamp is the month. MySQL understands this and can sort by year, month, day, hour, minute, etc. More info The query MatthewJ posted should work for what you are trying to accomplish. Quote Link to comment Share on other sites More sharing options...
LordLanky Posted July 7, 2009 Author Share Posted July 7, 2009 Thanks both you guys. Works perfect! Quote Link to comment 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.