> I have run create partitions on these tables now and that went well. Next I will test some drop/recreate partitions, then try some event to call a routine to do this.
There's an old saying: "if it ain't broke, don't fix it."
Sounds like you are happy with what you have.
> Question. If I have monthly paritions what happens when I get into a new year, will this screw things up?
Looks like it will be fine. The one thing to verify is that MONTH() is a function that PARTITION BY LIST will handle.
The one drawback (I think) with your schema is that all queries will touch all partitions unless they have "AND time = some_constant". However, since you will have 10 empty partitions and 2 that you need to hit, this is not bad.
> Not so good for embedded systems with no IT dept to look after it.
That's a very good argument for using the KISS principle.
> Especially when DB made by engineers with not all the time in the world to fiddle with the database.
So, some of what this forum does is let engineers build on what others have done. Here's my donation to the general version of your "sliding time-based partitions":
http://mysql.rjweb.org/doc.php/partitionmaint