logging - Quantizing unsynchronized log data based on timestamps (MySQL)

Question

I am dealing with a database which logs multiple data values when those values change. The value change events are not synchronized with each other in time: each value is logged independently, using deadband logic to log only when the value's change exceeds a set threshold.

Each log entry has an ID (auto-increment), "name", "value" and "timestamp" (unix timestamp) field.

Example data:

id |  name  | value | timestamp
1  |   one  |  3.6  |  ...123
2  |   two  |  4.7  |  ...145
3  |   one  |  5.8  |  ...148
4  |  three |  6.1  |  ...149
5  |  two   |  7.8  |  ...149
6  |  one   |  5.3  |  ...150
7  |  two   |  6.6  |  ...171
8  |  one   |  5.5  |  ...174
...

I need to chart the values over user-specified timeframes, using user-specified timebases for the chart x axis. (Example: chart all values from 1 January to 5 February, at one-minute resolution). However, in order to do this, all the values must be quantized to the chart x-axis's ticks: each of the items being logged needs to specify a value at each x tick.

Does anyone know of a way to achieve this with MySQL? My thought is that the logged data would need to be iterated and written to another (temporary) table using the requested time resolution. The processing function would extrapolate unchanged values and insert them for each tick. However, this has two problems: 1) it requires reprocessing of the data each time a chart is requested, and 2) would become very cpu-intensive, especially if multiple people are requesting multiple timebases or timespans simultaneously.

Another option would be to write all the data at a preset fixed frequency, but that defeats the purpose of using deadband logging to reduce the dataset size. It also doesn't make it any easier to select other timebases.

Any insight would be appreciated.

question from:https://stackoverflow.com/questions/66064785/quantizing-unsynchronized-log-data-based-on-timestamps-mysql

Answer 1

Waitting for answers