mysql - Sql Query to order data sorted by ranking but multiplied by how long ago it was published

Question

I have a feed of articles which are currently ranked by "votes" in the SQL query. I would like to factor in the amount of time since it was published in order to give more weight to recent articles.

SELECT * 
FROM articles 
WHERE date >= DATE_SUB(NOW(),INTERVAL 24 HOUR) 
ORDER BY (votes * (720/#####)) DESC 
LIMIT 100

What can I put into place of "#####" to return the amount of time (in minutes) since the article was published?

Therefore, an article with a vote of 1 that was published 10 minutes ago, would equal 72 (1 * 720/10) ... and rank below an article with a vote of 10 but published 60 minutes ago, equaling 120 (10 * 720/60)

Answer 1

Assuming date is the published day, you can use timestampdiff():

SELECT * 
FROM articles 
WHERE date >= DATE_SUB(NOW(),INTERVAL 24 HOUR) 
ORDER BY votes * (720/TIMESTAMPDIFF(minute, date, NOW())) DESC 
LIMIT 100