Hotness - and the difficulty of working out postgres cron jobs and raw PG SQL
Okay, so this is embarassing. I'm mostly interacting with my PG database via prisma.io - which is great, but something has come up.
Right now, I have a "posts" table, with an "aggregatedVotes" field to my posts table which goes up and down based on how the user votes. (I also have a post_votes table so that each user can only vote once, and in a pinch can be used to tally votes individually, but that's not algorithmically sound.)
But I also want to calculate "hotness" which is a function of both the total number of votes, and the amount of time that has passed since the post was created. I've created a floating point value - "aggregatedHotness".
It makes sense to me to run a function every 3 hours or so that calculates the values for each post, and updates them accordingly.
This however, looks like a job for pg_cron. And I'm going to be honest, my SQL writing isn't that good.
Basically, here's what I want to do.
For every post in Posts,
- I want to calculate how many 48 hour periods (or fractions of 48 hour periods) have passed since the post was created, and store that value in a variable called "period_48" So if the post was created 12 hours ago, the answer is 0.25, if it was created 96 hours ago, the answer is 2.
period_48 = [ (the_time_right_now) - (dateCreated) ] / 48_hrs
IF the posts's lastAggregated field === null OR [(the_time_right_now - lastAggregated) in hours] >= period_48
I want to calculate hotness and upcomingness according to this formula:
hotness = aggregatedVotes / (2^period_48)
and store that value in aggregatedHotness along with a timestamp of when it was last aggregated in lastAggregated.
The trick is I want to run this every 3 hours. So really, it's: 1) What query is going to help me do that, and 2) How and where do I set up the pg_cron job?
I don't quite know the answer, though I'm on the supabase discord hoping to get some help. I'll keep you updated... this is a tricky one that I want to get right.