Jump to content

Average Views per Hour


dachshund

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

I tried the code below but it didn't echo out anything.

 

$now = new DateTime();
$pubTime = new DateTime('2014-05-26 05:19:00');
$diff = $now->diff($pubTime);
$hrs = $diff->days * 24 + $diff->h;   //--> 249
 
echo $hrs;
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>';
 

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • 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.