如何在TimescaleDB中计算行之间的时间戳差值及指定时间范围内的活跃时长？

Got it, this is a common state-duration calculation problem, and TimescaleDB has just the tools to solve this cleanly. Let me walk you through exactly how to replicate your example, plus how to generalize it for any time buckets.

Step 1: Setup Your Test Data #

First, let's define a table matching your example (using numeric timestamps for minutes, as you did):

CREATE TABLE device_status (
  ts NUMERIC,
  status TEXT
);

INSERT INTO device_status VALUES
(2.1, '不可用'),
(3.5, '活跃'),
(6.8, '等待'),
(7.3, '不可用'),
(9.3, '活跃');

Step 2: Calculate State Durations with Window Functions #

We'll use the LEAD() window function to grab the timestamp of the next state change. For the final state in our time bucket, we'll default to the bucket's upper bound (15 minutes in your case):

SELECT
  ts AS start_time,
  status,
  -- Get next state change time, or 15 if it's the last entry
  LEAD(ts, 1, 15) OVER (ORDER BY ts) AS end_time
FROM device_status;

This query gives us a clear view of when each state starts and ends. For your active states, this will return:

• start_time=3.5, end_time=6.8
• start_time=9.3, end_time=15

Step 3: Sum Active Durations in the Bucket #

Now we filter for "活跃" states, calculate how much of each state interval falls within the 0-15 bucket, and sum those values:

SELECT
  SUM(
    -- Calculate the overlapping time between the state interval and the bucket [0,15]
    LEAST(end_time, 15) - GREATEST(start_time, 0)
  ) AS active_duration
FROM (
  SELECT
    ts AS start_time,
    status,
    LEAD(ts, 1, 15) OVER (ORDER BY ts) AS end_time
  FROM device_status
) AS status_periods
WHERE status = '活跃';

Running this will return 9.0, which matches your expected result perfectly.

Generalizing to Dynamic Time Buckets #

If you need to calculate this for recurring time buckets (like every 15 minutes, hours, etc.), TimescaleDB's time_bucket() function makes this straightforward. Let's adjust for timestamp values instead of numeric minutes:

Updated Table & Data #

CREATE TABLE device_status (
  ts TIMESTAMP,
  status TEXT
);

-- Convert your example minutes to timestamps starting at midnight
INSERT INTO device_status VALUES
('2024-01-01 00:02:06', '不可用'), -- 2.1 minutes = 2m6s
('2024-01-01 00:03:30', '活跃'), -- 3.5 minutes
('2024-01-01 00:06:48', '等待'), -- 6.8 minutes
('2024-01-01 00:07:18', '不可用'), --7.3 minutes
('2024-01-01 00:09:18', '活跃'); --9.3 minutes

Query for 15-Minute Buckets #

SELECT
  time_bucket('15 minutes', ts) AS bucket_start,
  SUM(
    LEAST(end_time, bucket_start + INTERVAL '15 minutes') - 
    GREATEST(start_time, bucket_start)
  ) AS active_duration
FROM (
  SELECT
    ts AS start_time,
    status,
    -- Get next state change time, or bucket end if last entry in the bucket
    LEAD(ts, 1, time_bucket('15 minutes', ts) + INTERVAL '15 minutes') OVER (
      PARTITION BY time_bucket('15 minutes', ts) 
      ORDER BY ts
    ) AS end_time
  FROM device_status
) AS status_periods
WHERE status = '活跃'
GROUP BY bucket_start;

This will return the active duration for each 15-minute bucket, automatically handling state transitions that span bucket boundaries.

Key Notes #

• Handle Initial State Gaps: If your first state change isn't at the start of the bucket (e.g., the device was active from 0 to 3.5 instead of starting as unavailable), you'll need to add an initial state record at the bucket start, or adjust the query to account for the implicit initial state.
• Numeric vs. Timestamp: Swap out INTERVAL calculations with numeric arithmetic if you're using numeric timestamps (like your original example) instead of TIMESTAMP types.
• Performance: For large datasets, make sure you have an index on ts to speed up the window function and time bucket operations.

内容的提问来源于stack exchange，提问作者Brian Burns