How to merge time interval start and end time into one point

InfluxDB 2
,
1

I’m the beginner in InfluxDB, and now I try to use it to handle IoT event data.

The data simply contains three fields: the station mac address, the device mac address and the timestamp (epoch in ms) when the station received a message from a device. And the data has now been stored in InfluxDB successfully.

Now, I have got such a problem. I want to merge these discrete data into continuous one by the following role:
For each station and for each device, if the time interval of two adjacent records (sort by the _time field) is great than 5 minutes, the timestamp of former record will become the end time of the one continuous data and the timestamp of the latter one will be the start time of another continuous data.

For example, if the raw data likes:

station     device    _time
AABB         CCDD      2021-08-11T01:00:00Z
AABB         CCDD      2021-08-11T01:01:00Z
AABB         CCDD      2021-08-11T01:03:00Z
AABB         CCDD      2021-08-11T01:11:00Z
AABB         CCDD      2021-08-11T01:12:00Z
AABB         CCEE      2021-08-11T01:01:00Z
AABB         CCEE      2021-08-11T01:11:00Z
BBAA         CCDD      2021-08-11T01:00:00Z
BBAA         CCDD      2021-08-11T01:01:00Z

then, the expected result may look like:

station     device     start                      end
AABB         CCDD      2021-08-11T01:00:00Z       2021-08-11T01:03:00Z
AABB         CCDD      2021-08-11T01:11:00Z       2021-08-11T01:12:00Z
AABB         CCEE      2021-08-11T01:01:00Z       2021-08-11T01:01:00Z
AABB         CCEE      2021-08-11T01:11:00Z       2021-08-11T01:11:00Z
BBAA         CCDD      2021-08-11T01:00:00Z       2021-08-11T01:01:00Z

I found another post shows how to calculate the time interval. However, I’ve got no idea about how to merge the start and end timestamp into one point.

2

Hello @Tonny_Tc,
I’m a little confused.
I’m having trouble understanding how your expected results match your description. Do you see any typos maybe? Specifically, this confuses me:

AABB         CCEE      2021-08-11T01:01:00Z       2021-08-11T01:01:00Z
AABB         CCEE      2021-08-11T01:11:00Z       2021-08-11T01:11:00Z

Why do you have two results there? Since the time between records is >5m I would expect just one result

AABB         CCEE      2021-08-11T01:01:00Z       2021-08-11T01:11:00Z

Maybe using stateDuration() and map() with conditional querying would help? Query using conditional logic in Flux | InfluxDB OSS 2.0 Documentation
You might even have to use timeshift() and then join the data to get that former latter logic in place.

Do you mind sharing a small amount of annotated CSV data with me so I can try it?

3

Hello @Anaisdg ,
Thanks a lot for your reply and sorry for my late response.

For the record you mentioned, the original records are

AABB         CCEE      2021-08-11T01:01:00Z
AABB         CCEE      2021-08-11T01:11:00Z

These records mean the device CCEE captured by the station AABB at 2021-08-11T01:01:00Z and 2021-08-11T01:11:00Z. Because there are a gap of 10 minutes between them which is greater then 5 minutes, it means each record itself contains the start and end time (though they are the same).

Thus, the two records should be the two records in result which have the same start and end time.

In fact, the business logic is that once a record is captured all records within 5 minutes of the previous record should be considered in this start/end period.

You can find sample data from link.

4

You’ll have to filter first for when time duration between points is greater than 5 minutes using conditional filtering.
Then you’ll probably have to query for the specific timestamp and use reduce() to resign it. I don’t think there’s a way to apply this logic at one time because you can’t shift records in a table by their index.

@scott do you have any suggestions for this that I’m not thinking of?