How to make a Wilson score interval that decreases by time

Question

So I'm working on a Wilson score interval to sort out trending content on my website, but for now it is only static. What I mean by that is the it will always keep the same score until someone up votes or downvotes it.

This is why I would like to implement that the score decreases by time. Like in this article. How Reddit ranking algorithms work. In that article they explain that Reddit score on the content decreases by time.

For the present I have this php function that gives me a score based on up and down votes :

function wilsonScore($up, $down)
{

    $score = (($up + 1.9208) / ($up + $down) - 1.96 * sqrt(($up * $down) / 
             ($up + $down) + 0.9604) / ($up + $down)) / (1 + 3.8416 / ($up + $down)) ;

    return $score;
}

I would like the score to be the same but add something to the SQL code when sorting out.

The SQL code looks like this :

SELECT *
FROM photos
WHERE   status = 0 
    AND net_votes > 0          // display only picture that got rated over 0
ORDER BY score DESC

An idea I have would be to end up with an algorithm that would decrease the score logarithmically, the first 1-2-3 days it is posted the time doesn't affect the score that much then the score starts to go down.

Edit

And would it be theoretically feasible to add something to the score so that the more reputation (like SO) the user gets on the website the more his rating is worth ? does something similar already exist on the internet ?

Did someone here already use something like this here ?

Answer 1

You could just do this in SQL:

$sql = "SELECT  `up`,
                `down`, 
                `date`, 
                (((`up` + 1.9208) / (`up` + `down`) - 1.96 * SQRT((`up` * `down`) / (`up` + `down`) + 0.9604) / (`up` + `down`)) / (1 + 3.8416 / (`up` + `down`))) AS `wilson`, 
                ((((`up` + 1.9208) / (`up` + `down`) - 1.96 * SQRT((`up` * `down`) / (`up` + `down`) + 0.9604) / (`up` + `down`)) / (1 + 3.8416 / (`up` + `down`))) / LN(DATEDIFF(NOW(), `date`) + EXP(1))) AS `weighted_wilson` 

    FROM        `photos`

    ORDER BY    `weighted_wilson` DESC
    ";

Using a natural logarithmic decay (adjusted to start from the original Wilson Score). Obviously you can play with the values.

You could of course adjust the number of up or down votes granted on a per user basis (i.e. experienced users grant larger up / down movements).