CODEWITHSUNDEEP
sqllinear-interpolationquestdb
Javier Ramirez
Javier Ramirez

Reputation: 3988

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.

Upvotes: 1

Views: 359

Answers (1)

Javier Ramirez
Javier Ramirez

Reputation: 3988

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.

Upvotes: 1

Related Questions