Query timestamp gaps, grouped by a Column value

Community
1

Hello,

I would like to check a table for unexpected data time-gaps, grouped by a column.

For example is there a query for an input table:

Datetime,            Sym
(timestamp)         (symbol)

2024-10-01 08:00:00  AA
2024-10-01 08:00:00  CC
2024-10-01 08:00:00  BB
...
2024-10-01 08:01:00  AA
2024-10-01 08:01:00  BB
2024-10-01 08:01:00  CC
...
2024-10-01 08:02:00  AA
2024-10-01 08:03:00  BB
2024-10-01 08:04:00  CC

which yields result:

Datetime,            Sym, Time_gap
2024-10-01 08:04:00  CC    3
2024-10-01 08:03:00  BB    2
2024-10-01 08:02:00  AA    1
...
2024-10-01 08:01:00  AA    1
2024-10-01 08:01:00  BB    1
2024-10-01 08:01:00  CC    1

For each ā€˜Symā€™ the time-gap between consecutive rows is calculated as ā€˜Time_gapā€™, with the timestamp of the event, and sorted DESC by ā€˜Time_gapā€™.

Note I showed ā€˜Time_gapā€™ as an integer (minutes), but any numeric value (seconds, timestamp) would suffice.

Thanks!

2

sure is!

You can use the first_value() window function to get the timestamp from the row before partitioned by Sym. At the moment we need a workaround, as the timestamp data type is not supported for this window function, but please do vote the github issue to raise awareness :slight_smile:

You could do this to check gaps in all the rows for today. I am displaying the gap in both seconds and millis for completeness. Then you could add a condition to show only those above a certain threshold.

WITH time_and_prev AS (
  SELECT Datetime, Sym, first_value(Datetime::long) OVER (PARTITION BY Sym ORDER BY Datetime ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING ) AS prevTimestamp 
FROM table where timestamp IN today()
)
SELECT Datetime, Sym, prevTimestamp::timestamp, datediff('s', Datetime, prevTimestamp::timestamp) as gap_s, (Datetime - prevTimestamp::timestamp) / 1000 as gap_ms FROM time_and_prev ;
1 Like
3

Thanks Javier so much for the quick & thorough response!

For this section of your query (I forgot to specify my tableā€™s name, so your query has it as ā€˜tableā€™) :

FROM table where timestamp IN today()

I assume you meant:

FROM table where Datetime IN today()

Unfortunately this gives an error: ā€œunknown function name: today()ā€.
Iā€™m not clear why today() didnā€™t work, but I just replaced the today() call with a str constant (eg: ā€˜2024ā€™).

4

You are correct, sorry I messed up the column names!

The today() function was recently added. It might be the case you are running an older version of questdb. In any case, the important part is on the window function, for the where filter you can just use anything you might need.