Adding null fields for date calculations

In this guide, you'll learn how to fill with NULLs the gaps where you don't have data between a range of two dates.

Working with dates is pretty easy with Tinybird. ClickHouse provides several functions for managing Date{Time} types. Unfortunately, there isn't a straightforward way to fill the empty gaps between a range of two dates.

How to return all data from a range of two dates

This is an example of how to get data from a start date to an end date, grouping by day, but returning all the days between these two dates, setting to null the values for those rows that don't have any data for a day.

{% tip-box title="Data Source example" %}We will run the following example with the first part of our ecommerce dataset, which contains 50M of rows. Check this guide to learn how to import the data in your account.{% tip-box-end %}

Using the UI

Let's start by creating a new empty Pipe.

Create empty Pipe button in the Dashboard

After creating the Pipe, we add the following SQL in the first node. We will name this node as {% code-line %}dates{% code-line-end %}.

This first snippet generates the range of days from the 20th to the 30th of November 2015.

And finally, we create the second node, where we will select any user info for each day, and fill with null the ones where there isn't any info.

We check that the first two days don't contain any information, because our events Data Source doesn't contain anything previous to 22nd November.