How can I output values for time intervals with no data in QuestDB

Question

I am using QuestDB to get the amount of events we are receiving every 500 milliseconds. Everything works as expected and I can use SAMPLE BY 500T to aggregate in half a second intervals.

However, for the intervals where we don't have any data, we are not getting any rows. I guess this is expected, but it would be good to have some way of getting a row for those intervals just with null or empty values.

Javier Ramirez · Accepted Answer · 2023-01-12T08:53:06.290

Luckily in QuestDB you have the FILL keyword to do exactly that. Take this query running at the public QuestDB demo:

SELECT 
    timestamp, count()
FROM trades
WHERE timestamp > dateadd('d', -1, now())
SAMPLE BY 500T ALIGN TO CALENDAR;

In this case I am aggregating every 500 milliseconds and getting results only for the intervals where I have data. I am limiting to only the past day. You can run this on the demo site as it is a live dataset and you should see gaps for some intervals.

Now, by using FILL I can add the rows for the periods with no values

SELECT 
    timestamp, count()
FROM trades
WHERE timestamp > dateadd('d', -1, now())
SAMPLE BY 500T FILL(NULL) ALIGN TO CALENDAR;

Note that you could also fill with LINEAR (linear interpolation of previous and next rows), PREV for the value of the row before, or with a constant value.

How can I output values for time intervals with no data in QuestDB

1 Answers1