Jump to content

Average Views per Hour


dachshund
Go to solution Solved by Barand,

Recommended Posts

Hi,

 

I have a blog which records the amount of views on each article. I now want to be able to work out the average number of views per hour.

 

How can I work out the number of hours passed from a datetime format?

 

From there I can just do views divided by hours passed.

 

Thanks in advance!

 

 

Link to comment
Share on other sites

Sample data

+----+---------------------+
| id | viewtime            |
+----+---------------------+
|  1 | 2014-05-25 00:03:00 |
|  2 | 2014-05-25 00:05:00 |
|  3 | 2014-05-25 00:10:00 |
|  4 | 2014-05-25 00:20:00 |
|  5 | 2014-05-25 00:29:00 |
|  6 | 2014-05-25 00:39:00 |
|  7 | 2014-05-25 00:48:00 |
|  8 | 2014-05-25 00:52:00 |
|  9 | 2014-05-25 00:59:00 |
| 10 | 2014-05-25 01:05:00 |
...
|491 | 2014-05-26 23:06:00 |
|492 | 2014-05-26 23:15:00 |
|493 | 2014-05-26 23:23:00 |
|494 | 2014-05-26 23:32:00 |
|495 | 2014-05-26 23:42:00 |
|496 | 2014-05-26 23:44:00 |
|497 | 2014-05-26 23:51:00 |
|498 | 2014-05-26 23:57:00 |
|499 | 2014-05-26 23:58:00 |
+----+---------------------+

Get counts per hour each day then get the average count

SELECT date, AVG(hourtot) as daily_av_per_hr
FROM (
    SELECT 
        DATE(viewtime) as date
        , HOUR(viewtime) as hr
        , COUNT(*) as hourtot
    FROM blog
    GROUP BY date, hr
    ) tots
GROUP BY date

+------------+-----------------+
| date       | daily_av_per_hr |
+------------+-----------------+
| 2014-05-25 |         10.5417 |
| 2014-05-26 |         10.2500 |
+------------+-----------------+
Edited by Barand
Link to comment
Share on other sites

Thanks for your help so far.

 

The data I have to work with per article is.

 

Total views to date

Datetime post was published

 

So what I need to do is take the "datetime post was published" from the current time, work out how many hours there have been since it was published, divide the "total views to date" by the hours. This will give me the average amount of views per hour.

 

Any help with that would be greatly appreciated!

Link to comment
Share on other sites

if datetime published is '2014-05-26 05:19:00', then

SELECT
    COUNT(*) / HOUR(TIMEDIFF(NOW(), '2014-05-26 05:19:00')) as avg
FROM blog
WHERE viewtime > '2014-05-26 05:19:00';

or, using PHP you can get the hours with

$now = new DateTime();
$pubTime = new DateTime('2014-05-26 05:19:00');
$diff = $now->diff($pubTime);
$hrs = $diff->days * 24 + $diff->h;   //--> 249

 

Link to comment
Share on other sites

ah ok, perfect! that works. is there a way to echo out the articles and order them by $averageviews.

 

For example, I have this at the moment. Obviously it doesn't work but hopefully gives you an idea of what I am trying to achieve:

 

 

 
$format = 'Y-m-j G:i:s'; 
$date = date ( $format ); 
 
// - 7 days from today 
 
$minus3 = date ( $format, strtotime ( '-7 day' . $date ) ); 
$sql = "SELECT * FROM content WHERE `live` LIKE '0' AND `date` > '$minus3' ORDER BY `views` DESC LIMIT 8";
$result=mysql_query($sql);
while($rows=mysql_fetch_array($result)){
 
$hrs = floor((time() - strtotime($rows['date'])) / 3600);
$averageviews = $rows['views'] / $hrs;
 

$sql = "SELECT * FROM content WHERE `live` LIKE '0' AND `date` > '$minus3' ORDER BY '$averageviews' DESC LIMIT 8";
$result=mysql_query($sql);
while($rows=mysql_fetch_array($result)){
 
}

 
Link to comment
Share on other sites

try

$sql = "SELECT
        date
        , views
        , views / HOUR(TIMEDIFF(NOW(), date)) as avg
    FROM content
    WHERE date > CURDATE() - INTERVAL 7 DAY
    ORDER BY avg DESC
    LIMIT 8";
$result=mysql_query($sql);

echo "<table>";
while($rows=mysql_fetch_array($result)) {

    echo '<tr><td>' . join('</td><td>', $rows) . '</td></tr>';
}
echo '</table>';
 

Edited by Barand
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.