I have a problem related to the sql databases.
Namely I want to create a query get the average value of a certain measurment column, but only for those subsequent rows whose time difference is less than 60 seconds.
An example table would look something like this:
| ID | Measurement | Timestamp |
| 1 | 0.564 | 1535648400|
| 2 | 0.456 | 1535648459|
| 3 | 0.785 | 1535648501|
| 4 | 0.321 | 1535648620|
The difference between the timestamps would be ok for all rows, except between 3 and 4, where they differ for more than 60 seconds. In that case, average would not take row with ID 4, but only the first 3 rows.
Primarily I plan to write this query for a InfluxDB database, but generally a query also for a structural SQL would work as well.
Join table to itself using inner join with timestamp condition to include all records for 60 seconds. Standard SQL below (need to be adjusted for InfluxDB):
SELECT t1.ID, t1.measurement, t1.timestamp, avg(t2.measurement)
FROM table t1, table t2
WHERE t1.timestamp <= t2.timestamp
AND t1.timestamp + 60*1000 > t2.timestamp
GROUP BY t1.ID, t1.measurement, t1.timestamp