使用时间戳计算平均命中率

Question

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             |
+----------------+----------------+----------------+----------------+

Answer 1

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.