ClickHouse tips #9: Filling gaps in time-series on ClickHouse

Filling gaps and resampling time series

In the previous tip, we showed you how to fill null values on ClickHouse. Many times, you will group data by a Date or a DateTime column, and there will be rows missing because for those periods there was no data.

To chart data, you’ll ideally have time series where the intervals between one data point and the next one are constant, so you’d want to fill in the empty periods with a value of 0. This is how you’d do it on ClickHouse.

If you have a Data Source like this, with a DataTime column and an Int column counting visits:

WITH FILL

ClickHouse introduced the WITH FILL modifier to ORDER BY clause a couple of years ago. You can read its full documentation here, but with this example you’ll understand what it does at a glance. It solves the problem we described above in one line, and in a very elegant way:

This works with Date, DateTime and any numerical column. The step is 1 by default, and on DateTime columns it’s one second and on Date columns it’s one day.

If you can’t use WITH FILL

If you’re running an older version of ClickHouse, there’s still a way to do this. The way to fill those empty rows would be to generate a time series that contained every second in that time interval, that could be done like this:

And then joining that with the original Data Source would give us this, with all the rows present, including those where there are no values present in the original one:

Last: if you were using Tinybird, you could split the query in two nodes, and write the subquery in another node, that you could name full_dt for example (with the same content as the timeseries_without_gaps node from above). Doing that, the previous query could be rewritten like this, in a much more legible way (plus you get to see the results of intermediate subqueries in our notebook-like interface - quite handy).

If you don’t have a Tinybird account yet, sign up for one here