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

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

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.

Solution

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.