SQL querying data based on the timestamp difference

user_10159650 0
Viewed 73

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.

influxdb mysql sql database
2 Answers

I would like to add an extra answer on top of V.G.'s that I found very good. For performance reasons, you may want to rephrase the query and add a good index, as in:

create index ix1 on table (timestamp);

Then the query could be:

SELECT t1.ID, t1.measurement, t1.timestamp,  avg(t2.measurement) 
FROM table t1, table t2
WHERE t2.timestamp >= t1.timestamp
  AND t2.timestamp < t1.timestamp + 60*1000
GROUP BY t1.ID, t1.measurement, t1.timestamp

Sometimes, a simple "query rephrase" can make a big performance difference.

user_64361910

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
user_36410230
Related