This is exactly why specialized timeseries databases exist - they have functions for this sort of thing. (like the FILL function for InfluxDB). We can hopefully build this into the MySQL data source over time but the database itself has no support for most timeseries functions.
To do this with sql:
- you will need to do a group by time - for example group by hour. This will make the data regular - once every hour for example (rather than irregular like it seems it is in your case).
- then your query will need to fill in zeroes for the empty intervals - something like the solutions suggested here:
https://stackoverflow.com/questions/2479773/mysql-filling-empty-fields-with-zeroes-when-using-group-by
I’m not sure how to do part 2 in this case. Maybe someone has already written something similar they could share?