ultrus Posted May 12, 2008 Share Posted May 12, 2008 Hello, I'm creating a basic website traffic stat page, and am trying to decide the best way to store the visit times, and how to group the time/visit counts by month to show on the on this page. Should I use MySQL's built in TIMESTAMP, or should I use 10 digit timestamps instead? As people visit the single page website, their IP address is logged with the timestamp. How would I group these visits by month, then possibly by ip for unique visits? Thanks much for the advice. Quote Link to comment Share on other sites More sharing options...
ultrus Posted May 12, 2008 Author Share Posted May 12, 2008 ah, this might help from: http://forums.mysql.com/read.php?20,131998,132120#msg-132120 SELECT DATE_FORMAT(bdate, '%Y-%m') ym, COUNT(*) FROM foo GROUP BY ym Quote Link to comment Share on other sites More sharing options...
mezise Posted May 12, 2008 Share Posted May 12, 2008 TIMESTAMP type is OK, DATE type is OK too. Your query to get number of visits per month is correct. To get number of visits per month per IP modify it like this: SELECT DATE_FORMAT(bdate, '%Y-%m') ym , ipNumber , COUNT(*) FROM foo GROUP BY ym, ipNumber ORDER BY ym, ipNumber ; Quote Link to comment Share on other sites More sharing options...
fenway Posted May 12, 2008 Share Posted May 12, 2008 DATETIME is much better, IMHO. Quote Link to comment Share on other sites More sharing options...
ultrus Posted May 12, 2008 Author Share Posted May 12, 2008 Thanks for the assist! I was able to transform your tips into something that works great for what I need. Thank you much. 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.