In my current project, I am pinging a server hourly every day, which results in 24 records per day; over time, this is a lot of data, and I would like an administrator (in PHP) to be able to pick a date, and delete all of the data before that date, except from one piece of data per day.
E.g. if the date selected was 2/4/16, it would delete all but one record per day from before the date 2/4/16.
The structure of the database table in question is:
server_status:
id int(11)
server_id int(11)
time_checked datetime
status char(1)
I was wondering what MySQL statement I would need to achieve this - I've had a look around the web, and I can see that I might need to create new tables, drop the table and replace it, but it seems to be the response that it blocks the db for a long amount of time. It's more the select statement that I'm confused about - I understand how to select the data from before said date, but I don't know how to 1) pick only 1 piece of data per day, and 2) delete the rest.
Thank you so much for your help!