Get average hits (day, week, month) with SQL/PHP

by 4 replies
5
Hey all

Does anybody know how to work out average hits per day, week and month based on UNIX timestamps?

Basically I want to track users time on my site and average hits for analytics (I know I can do this in Google Analytics, but I've built some blogging software for personal use and want some basic analytics). I've already worked out how to calculate average time on my site with my data.

I have a table in MySQL with the following format:

Note "start" and "end" are UNIX timestamps.
Code:
+----------------+----------------+----------------+----------------+
| start          | end            | unique_id      | id             |
+----------------+----------------+----------------+----------------+
| 1358789867     | 1358789872     | (hash here)    | 65             |
+----------------+----------------+----------------+----------------+
| 1358789966     | 1358789972     | (hash here)    | 66             |
+----------------+----------------+----------------+----------------+
| 1358789998     | 1358790003     | (hash here)    | 67             |
+----------------+----------------+----------------+----------------+
Would appreciate some input.

Cheers.
#programming #average #day #hits #month #sql or php #week
  • Are you going to log the amount of visitors in a time range or will it be the individual time for every visitor.

    Your first row in your example is a 5 minute period so are you counting x visitors per 5 minute period.
    • [2] replies
    • I'm logging the UNIX timestamp for when they land on any page on my site, and logging the UNIX timestamp for when they leave (close the browser or navigate away, updated via AJAX), so I'm not using any time frames, simply the length of time each individual is on my site.

      To calculate the hits I know I can just use the "start" time, but I don't know how to extract the data I want or which functions could help.

      Your post is appreciated!


      Thank you.
    • If Im not mistaken UNIX times tamps are the number of seconds since 1/1/1970 so the first row is a 5 second period not a 5 minute.


      To work out averages you can

      1) take the start date / end date convert time to a time stamp with strtotime()
      2) grab all the entries with a start time within that range
      3) count the entries
      • [1] reply
Join the discussion

Next Topics on Trending Feed