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! Link to comment https://forums.phpfreaks.com/topic/165053-solved-grouping-data-by-time-intervals-to-make-graphs/ 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) Link to comment https://forums.phpfreaks.com/topic/165053-solved-grouping-data-by-time-intervals-to-make-graphs/#findComment-870369 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? Link to comment https://forums.phpfreaks.com/topic/165053-solved-grouping-data-by-time-intervals-to-make-graphs/#findComment-870370 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. Link to comment https://forums.phpfreaks.com/topic/165053-solved-grouping-data-by-time-intervals-to-make-graphs/#findComment-870396 Share on other sites More sharing options...
LordLanky Posted July 7, 2009 Author Share Posted July 7, 2009 Thanks both you guys. Works perfect! Link to comment https://forums.phpfreaks.com/topic/165053-solved-grouping-data-by-time-intervals-to-make-graphs/#findComment-870410 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.