[SOLVED] group visits by month using range of timestamps, then by unique visits?

[ultrus]

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.

[ultrus]

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

[mezise]

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
;

[fenway]

DATETIME is much better, IMHO.

[ultrus]

Thanks for the assist! I was able to transform your tips into something that works great for what I need.

 

Thank you much.