CODEWITHSUNDEEP
questdb
Nick The Greek
Nick The Greek

Reputation: 453

How to find gaps in data of more than 5s in QuestDB?

I’m using QuestDB to store time-series data, and I have a dataset ordered by timestamp. I’d like to calculate the time difference (delta) between consecutive records to identify gaps. For example, I want to know if there's been a delay (like 5 seconds or more) since the last recorded price tick.

Here’s an example structure of my data:

timestamp price symbol
2024-11-05T12:00:01 100 BTCUSD
2024-11-05T12:00:02 102 BTCUSD
2024-11-05T12:00:07 101 BTCUSD
2024-11-05T12:00:08 103 BTCUSD

In this example, I'd want to flag the gap between 12:00:02 and 12:00:07, as it exceeds a 5-second threshold.

Is there a recommended way or a specific function in QuestDB to accomplish this?

Upvotes: 1

Views: 37

Answers (1)

0x3h
0x3h

Reputation: 462

This can be achieved with first_value window function

with twindow AS (
  SELECT
    first_value(timestamp::LONG)
      OVER (
        ORDER BY timestamp
        ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING
      )               AS tprevious,
      timestamp::LONG AS tcurrent
  FROM "price_data"
),
intergaps AS (
  SELECT 
    tprevious  AS gapstart,
    tcurrent   AS gapend
  FROM twindow WHERE tprevious IS NOT NULL AND (tcurrent - tprevious) >= 5000000
),
combinedgaps AS (
  SELECT * FROM intergaps
)
SELECT gapstart, gapend FROM combinedgaps

Upvotes: 1

Related Questions