www.webdeveloper.com
Results 1 to 3 of 3

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

Hybrid View

  1. #1
    Join Date
    Sep 2011
    Location
    Bristol, England, United Kingdom
    Posts
    192

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

    Hi all,

    I have table in SQL which stores the time the user entered my site and the time they left (updated via AJAX). I am using the unix timestamp, not a regular timestamp. With this I have been able to easily figure out the average length of time a user spends on my site, but I also want to be able to work out the average number of hits per day (from the beginning of my records), the average number of hits per week and per month.

    I can't actually figure out how to do this with unix timestamps. I'm aware this can be done in SQL or PHP, and would favour an SQL solution if possible. I've been given one possible solution from Stackoverflow, but I can't figure out how to implement it with my current database. The code I was given is:

    PHP Code:
    select date(from_timestamp(timestamp_start)), count(*)
    from t
    group by date
    (from_timestamp(timestamp_start)) 
    This is table structure (example data):

    PHP Code:
    +----------------+----------------+----------------+----------------+
    start          end            unique_id      id             |
    +----------------+----------------+----------------+----------------+
    1358789867     1358789872     | (hash here)    | 65             |
    +----------------+----------------+----------------+----------------+
    1358789966     1358789972     | (hash here)    | 66             |
    +----------------+----------------+----------------+----------------+
    1358789998     1358790003     | (hash here)    | 67             |
    +----------------+----------------+----------------+----------------+ 
    Help much appreciated, thank you.

  2. #2
    Join Date
    Sep 2011
    Location
    Bristol, England, United Kingdom
    Posts
    192
    I've been advised not to do this with PHP arrays, but to make sure the work is done in SQL. Still no clues?

  3. #3
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,404
    If I correctly understand the situation (you are storing a UNIX timestamp integer for the timestamp_start column?), I think you want to use the FROM_UNIXTIME() function, not FROM_TIMESTAMP()?
    Code:
    select date(from_unixtime(timestamp_start)) as the_date, count(*)
    from t
    group by the_date
    However, if it's not too late to re-factor, I'd recommend that column be an actual MySQL TIMESTAMP type, giving you direct access to date-/time-related functions, instead of always having to cast to a date-time type first.
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles