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

Filling gaps and resampling time series data in ClickHouse® can be efficiently handled using the WITH FILL modifier, which addresses the issue of missing rows due to periods without data, by inserting zero values to maintain constant intervals between data points. This technique works seamlessly with Date, DateTime, and numerical columns, with default steps of one second for DateTime and one day for Date columns. For users running older versions of ClickHouse®, an alternative method involves generating a complete time series for the desired interval and joining it with the original dataset to include previously missing rows. Additionally, the process can be streamlined using Tinybird, where queries can be split into nodes, allowing for more readable and manageable code, alongside the advantage of viewing intermediate subquery results in a notebook-like environment.