I am trying to make a similar website to Hacker News and I found this algorithm which needs to help me sort the links on my site, here it is:
Score = (P-1) / (T+2)^G
P = points of an item (and -1 is to negate submitters vote)
T = time since submission (in hours)
G = Gravity, defaults to 1.8 in news.arc
Table 1 name: links
Table 1 fields: id, title, url, user_id, created_at
Table 2 name: votes
Table 2 fields: id, user_id, link_id, vote, created_at
If you want to use SQL only method.
First let map every parameter with its candidate in tables:
P Sum of vote column in
T Current date -
now() function - minus created_at column in
With this in mind, the following SQL select statement should return the score for each link.
select links.id, (sum(vote) - 1) / POWER(DATEDIFF(now(),links.created_at)*24 + 2, 1.8) from links, votes where links.id = votes.link_id group by links.id