InfluxDB V3 (Cloud) Any idea how to get duration from a time series?

InfluxDB IOx
1

Hi,

I have a time series where I trace the location of an object, and I would like to know how much time an object was in a given location, For example:

10:00:00; locationA
10:05:00; locationB
10:06:00; locationC
etc

What I would like to get is information about each visit duration:
10:00:00; locationA; 5min
10:05:00; locationB; 1 min
10:06:00; locationC; null

Is that possible in InfluxDB cloud SQL? I saw some solutions in previous versions of Influx using functions like elapsed, lag, lead etc. But none of that is available here.

Thanks for your help!

2

You can do this in SQL as well, I just tried it on my dataset, reference this code and feel free to change it for your measurement assuming it’s called “locations”, values and time duration etc:

WITH ranked_data AS (
    SELECT
        time AS start_time,
        LEAD(time) OVER (ORDER BY time) AS end_time,
        location
    FROM
        "locations"
    WHERE
        time >= now() - INTERVAL '1 day'
),
durations AS (
    SELECT
        start_time,
        end_time,
        location,
        EXTRACT(EPOCH FROM end_time) - EXTRACT(EPOCH FROM start_time) AS duration_seconds
    FROM
        ranked_data
)
SELECT
    start_time,
    location,
    CASE 
        WHEN end_time IS NOT NULL THEN 
            CONCAT(
                CAST(FLOOR(duration_seconds / 60) AS STRING), ' min'
            )
        ELSE
            'null'
    END AS duration
FROM
    durations
ORDER BY
    start_time;

Basically, you want to do the following 3 things:

  1. Identify the start and end times for each location entry.
  2. Calculate the duration between consecutive entries.
  3. Format the duration in a readable format (e.g., minutes).
3

Thank you for the answer. I tried a similar solution before using Data Explorer UI, but getting an error like this:

Error while planning query: Error during planning: Invalid function ‘lead’.
Did you mean ‘lpad’?: rpc error: code = InvalidArgument desc = Error while planning query: Error during planning: Invalid function ‘lead’.
Did you mean ‘lpad’?

Is it possible that Data Explorer UI does not allow it but a different client does?

4

Well it could be that the function is not supported, have a look at this list to make sure: SQL string functions | InfluxDB Cloud Serverless Documentation

5

It looks like it is not supported, that’s why I posted this question in the first place :slight_smile: