mySQL script to get results in given date and insert into table

Question

I'm really struggling for days with (what i think) is a pretty advanced operation that i plan to schedule to run in my database every week.

this is the structure of my table (unit_uptime_daily):

What i need to do is run a script every week that, for every unit_id that exists in that table, gets all the rows of that unit_id thats timestamp is that present day < 6 days (so all the unit_ids with a timestamp of the previous week) add up the total_uptime column of the 7 rows and insert the new row into a weekly table.

Effectively, i am grabbing the 7 latest rows for each unit_id, adding up the total_uptime and then inserting unit_id, result of added total_uptime and timestamp into a new table.

Thanks in advance, if this is even possible to do!

Answer 1

Use cron jobs. Most of hosting providers provides this facility. Google cron jobs to find more about it and here's an answer that could help you.

Run a PHP file in a cron job using CPanel

Answer 2

I have solved this by using PHP, I got the list of unique ids, and did all the maths in a loop, inserting each result into the new table. I had already done this but would have liked it to be possible in SQL.