On my website I use timestamps to track my users average length of time on my site. Each unique IP is logged with a "time entered" and a "time exited" record (updated using AJAX). With this I've been able to calculate the average length of time my users spend on my site.
However, I want to be able to calculate the average hits per day, week and month as well. I'm certain the timestamps could be used to achieve this, but I don't know how to go about solving the problem in PHP and MySQL. I've read on other websites that MySQL has some functions that can work out averages, but I don't know how to extract the timestamps for each day/week/month so wouldn't even be able to pass any data to said function(s).
I could make a table with "start" and "end" timestamps for every single day/week/month, and extract values in between using a loop/associative array, but that method seems over the top and verbose and would take a very long time. Is there an easier way to achieve this?
My table structure currently is:
+----------------+----------------+----------------+----------------+
| start | end | unique_id | id |
+----------------+----------------+----------------+----------------+
| 1358789867 | 1358789872 | (hash here) | 65 |
+----------------+----------------+----------------+----------------+
| 1358789966 | 1358789972 | (hash here) | 66 |
+----------------+----------------+----------------+----------------+
| 1358789998 | 1358790003 | (hash here) | 67 |
+----------------+----------------+----------------+----------------+
For the average hits per day, you can just use the start timestamp. However, the exact syntax depends on whether you are using timestamp or unix_timestamp. For the first:
select date(timestamp_start), count(*)
from t
group by date(timestamp_start)
For unix_timestamp:
select date(from_timestamp(timestamp_start)), count(*)
from t
group by date(from_timestamp(timestamp_start))
This does the grouping by day, represented as a date. Once you have it in that format, other functions, such as year(), month()anddate_format()` can be used for the other time periods.