tinybird
/
guides

Adding null fields for dates calculations

Easy
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 two range of dates.

How to return all data from two dates range

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 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 filling with null the ones there isn't any info.

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

ON THIS GUIDE