Hopefully this will entice more people into supporting SQLite as their backend DB. There's been more than a few things I've purposefully not installed because they require MySQL (it is forbidden on my servers.)
It's entirely possible MySQL is a superb DB engine now and won't cause me any more trauma but ...
For what it's worth, in the cases where you need a client-server database, I know that Postgres is supposed to be "better," but MySQL is everywhere and it's always been up for almost any task I've thrown at it. I've found it to always be sufficiently good enough.
For Postgres, I would have just created an index on (the equivalent of) `truncate(YMD, timestamp)` (and I demonstrated this as a POC to the higher ups as a good reason to switch to Postgres. But alas, no traction.)
[1] Seems to have arrived some time around 2013-14?
[2] Storing the date and time parts separately would have been sensible but the schema was set in stone a couple of years old before I arrived.
> [2] Storing the date and time parts separately would have been sensible but the schema was set in stone a couple of years old before I arrived.
The real benefit of postgres*: offline transactional schema changes. mysql would've done a table copy in order to change it. if mysql supported offline schema changes, perhaps you'd have been able to change the data type, solving the root cause of the problem.
* I haven't had the pleasure of using postgres in production, so I can't speak to how effective any given feature is -- only what the marketing appeal is.