Timescaledb find time difference between consecutive data

devesh joshi 0
Viewed 15

I have a data table in mysql database which has time series data

**Table Sensor Data**  
ID    uuid  server_time
1      a    2021-07-29 11:36:00
2      b    2021-07-29 11:36:00
3      a    2021-07-29 12:36:00
4      b    2021-07-29 11:39:00
5      a    2021-07-29 13:36:00

I want to find time difference in minutes between server_time of the all consecutive data for each uuid (ordered by server_time). For example, the query for above data would return

uuid  difference
a    60
b    3
a    60

And then I want to filter all such entries where time difference is greater than 60 minutes. Is there a way to do this in timescaledb which also performant for greater than 1 million rows?

timescaledb
1 Answers

Not sure if I completely understood the question but here we go with some basic example:


create table sensors_data (id bigint, uid text, server_time timestamp);
insert into sensors_data  values
(1, 'a', '2021-07-29 11:36:15'),
(2, 'b', '2021-07-29 12:36:15'),
(3, 'a', '2021-07-29 12:36:15'),
(4, 'b', '2021-07-29 11:39:15'),
(5, 'a', '2021-07-29 13:36:15'),
(6, 'a', '2021-07-29 13:45:51'),
(7, 'b', '2021-07-29 13:45:51'),
(8, 'a', '2021-07-29 13:54:51');

getting the first date from an hour:

with f as (
        select uid, min(server_time)
        from sensors_data
        group by 1, time_bucket('1h', server_time)
) select f.uid, f.min - sensors_data.server_time
from f
left join sensors_data 
on f.uid = sensors_data.uid

group by 1, 2 having f.min - sensors_data.server_time > interval '1 hour'
order by 2;

if you want to also group data by extra day, add this to your on join clause:

and time_bucket('1d', f.min) = time_bucket('1d', sensors_data.server_time)

I couldn't figure out a way to order by the id but I hope it can give you some direction.

jonatasdp0
Related