Mysql – How to keep “approximate” date data points

For the love of Fortran, stop what you're trying to do!

You should have all your database schema, stored procedures, etc checked into a version control system rather than only in the database.

If the database is only used with a single app, then you'd generally want to put the schema in the same repository as the application's source code, otherwise you should put the in a separate repository. Some version control systems like git makes it easy to work with multiple repositories like with git submodules.

This way the developer had just to run a script that drops the database and reloads the schema. When you need to make changes to the schema, you should write a database migration script. This way the developers can upgrade their own developments database at their own leisure, rather than having you breaking their code while they're in the middle of other works.

Let's think out of the box. Instead of having a "snapshot", let's have a "log". What you currently have is "current" state of things; adding a "log" would provide the "history", from which could be derived the 'lost' info.

One way to implement the log is to have a TRIGGER on INSERT or UPDATE of the table, and have the trigger write to the log file. This log will not be pleasant for the ad hoc queries, so have a nightly job (or maybe hourly) that summarizes the changes for the day -- net gain (or loss) of number of posts, etc. The "day2" info and the "last month" info can then be derived from this summary table quite quickly. Or perhaps a second level of summarization that declares what the state was for each day. I doubt if UNION would be needed. The "snapshot" would not be involved.