Obtaining an average per day value of a field with MySQL's DATEDIFF function

Jun 10th, 2009 at 11:58 am
filed under: Web Development / MySQL
Comments (0)

Quite some time ago, I added a views field to my articles table, and implemented a small function to log a "view" on each of my articles as it was read. A lot of CMS & blog systems do this, and people often display the number of clicks/views/reads on the article page.

While I don't display that specific information currently on my articles, I wanted to calculate the average number of views/reads per day as a measure of determining which of my articles were the most popular, as simply putting up the total number of view/reads is basically going to give you a persistent list of the oldest articles, unless you get dugg, that list will likely not change.

This is easy to accomplish when using a MySQL datetime field for the article publish date.

Here is the basic MySQL code used;

SELECT title, views / DATEDIFF(CURDATE(), date) AS views_per_day
FROM articles
WHERE DATEDIFF(CURDATE(), date) > '14'
    AND views > '100'
ORDER BY `views_per_day` DESC
LIMIT 0, 10

This produces a list of the top 10 articles (over 14 days old with an excess of 100 views to avoid recent/front page posts diluting results), and displays their titles along with the average views per day, descending in order from highest views per day.

bookmark / share this: Bookmark and Share
rated 3.75/5 (4 votes)

MySQL

You may also be interested in:

Comments

No comments posted yet, why not be the first?

Subscribe to comments feed

Write a comment

* = required field

:

:

:

:

You may insert urls in plain text, urls will be automatically linkified for trusted users and on seasoned posts only. All first comments are moderated, so use your email if you want to be remembered.


Back to top