Obtaining an average per day value of a field with MySQL's DATEDIFF function
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.