Average Views per Hour

[dachshund]

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!

 

 

[fastsol]

Convert the dates to timestamps (which represent seconds), subtract the two, and divide by 3600.

Taken from this post http://stackoverflow.com/questions/13598590/php-timestamp-different-in-total-remaining-hour

[Barand]

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 May 30, 2014 by Barand

[dachshund]

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!

[Barand]

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

 

[dachshund]

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;

[Barand]

What version PHP are you using?

 

DateTime requires 5.2

DateTime::diff requires 5.3

 

You may have to do it the old fashioned way eg

$hrs = floor((time() - strtotime('2014-05-26 05:19:00')) / 3600);

Edited June 5, 2014 by Barand

[dachshund]

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)){
 
}

 

[Barand]

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 June 5, 2014 by Barand

[dachshund]

perfect, thanks so much!

[dachshund]

sorry, me again. it is possible to exclude any articles posted in the last hour?

[Barand]

Define "the last hour". It is now 11:29 (my time) so do you want to exclude everything since 11am, or do you you mean everything in the last 60 minutes to be excluded?

[dachshund]

Hey. Everything in the last 60 minutes would be ideal, although if it is easier just everything in since 11am (if the time is 11:29am).

[Barand]

WHERE date BETWEEN CURDATE() - INTERVAL 7 DAY AND NOW() - INTERVAL 60 MINUTE