DEV Community

Cover image for Calculating Dwell Time in Postgres+TimescaleDB
James Blackwood-Sewell
James Blackwood-Sewell

Posted on • Edited on

Calculating Dwell Time in Postgres+TimescaleDB

When you're working with IOT or OT data you often want to look at a series of state transitions for sensors or devices and work out the amount of time spent in each state. Each device can only be in a single state at a time, so when a new state is started, the old one is closed off.

This is often used for billing or metering (think power meters, devices connected to a network, or even a complex state machine) and can be referred to as dwell time or connection time. In this case, the desired output is ( device_id, state, start_time, dwell_time).

Let's consider the following table which tracks states for sensors.

CREATE TABLE testdata
(
    sid INT NOT NULL,
    state VARCHAR(1) NOT NULL,
    ts TIMESTAMPTZ NOT NULL
);
Enter fullscreen mode Exit fullscreen mode

We can populate the table with some test data:

-- Create some test data
INSERT INTO testdata (sid, state, ts) VALUES 
  (1, 'a', '2019-Nov-01 12:01:00'),
  (1, 'a', '2019-Nov-01 12:02:00'),
  (1, 'a', '2019-Nov-01 12:05:00'),
  (1, 'b', '2019-Nov-01 12:10:00'),
  (1, 'b', '2019-Nov-01 12:20:00'),
  (2, 'a', '2019-Nov-01 12:20:00'),
  (2, 'a', '2019-Nov-01 12:22:00'),
  (1, 'a', '2019-Nov-01 13:00:00'),
  (1, 'a', '2019-Nov-01 13:02:00'),
  (1, 'a', '2019-Nov-01 13:06:00'),
  (1, 'a', '2019-Nov-01 13:12:00'),
  (1, 'a', '2019-Nov-01 14:00:00'),
  (1, 'a', '2019-Nov-01 14:12:00'),
  (1, 'a', '2019-Nov-01 14:14:00'),
  (1, 'a', '2019-Nov-01 14:30:00'),
  (1, 'a', '2019-Nov-01 14:35:00');
Enter fullscreen mode Exit fullscreen mode

Now, you can solve this with plain SQL (I'll leave that as a exercise for the reader😉), but if you're using PostgreSQL + TimescaleDB then you can use one of the hyperfunctions which come with the timescaledb-toolkit extension.

The final solution looks like this:

WITH states AS (
  SELECT 
    (state_timeline(state_agg(ts, state))).*,
    sid 
  FROM testdata 
  GROUP BY sid
)
SELECT 
  sid, 
  state, 
  start_time, 
  end_time - start_time dwell_time
FROM states
ORDER BY sid, start_time;
Enter fullscreen mode Exit fullscreen mode

Which gives the following results:

sid     state   start_time              dwell_time
1       a       2019-11-01 12:01:00+00  00:09:00
1       b       2019-11-01 12:10:00+00  00:15:00
1       a       2019-11-01 12:25:00+00  02:10:00
2       a       2019-11-01 12:20:00+00  00:02:00
Enter fullscreen mode Exit fullscreen mode

The magic lies in the state_agg aggregate, which tracks transitions between states within our grouping set (in this case the state column), and also in the state_timeline function, which takes the output of state_agg and creates a timeline of all state changes with the time they were entered and exited.

With that information in hand, it’s easy to work out the dwell_time using end_time - start_time. If you're impressed by this check out the other things hyperfunctions can do!

Billboard image

The Next Generation Developer Platform

Coherence is the first Platform-as-a-Service you can control. Unlike "black-box" platforms that are opinionated about the infra you can deploy, Coherence is powered by CNC, the open-source IaC framework, which offers limitless customization.

Learn more

Top comments (0)

Sentry image

See why 4M developers consider Sentry, “not bad.”

Fixing code doesn’t have to be the worst part of your day. Learn how Sentry can help.

Learn more