Jul 08, 2021

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

This simple trick will teach you how to fill date and datetime gaps in time-series on ClickHouse
Xoel López
Founder at TheirStack

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:


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

Do you like this post?

Related posts

Real-time Data Visualization: How to build faster dashboards
A new way to create intermediate Data Sources in Tinybird
Jun 15, 2023
Export data from Tinybird to Amazon S3 with the S3 Sink
Mar 21, 2024
Tinybird: A ksqlDB alternative when stateful stream processing isn't enough
To the limits of SQL... and beyond
Automating data workflows with plaintext files and Git
Chatting GraphQL with Jamie Barton of Grafbase
Apr 24, 2023
What it takes to build a real-time recommendation system
We launched an open source ClickHouse Knowledge Base
Oct 11, 2022
The definition of real-time data

Build fast data products, faster.

Try Tinybird and bring your data sources together and enable engineers to build with data in minutes. No credit card required, free to get started.
Need more? Contact sales for Enterprise support.