You may be wondering if {% code-line %}generate_series{% code-line-end %} exists on ClickHouse like it does on Postgres. The bad news is that it doesn’t (yet); the good news is this post will teach you how to do on ClickHouse everything you can do on Postgres with {% code-line %}generate_series{% code-line-end %}, and more.

First, let’s see how the {% code-line %}numbers{% code-line-end %} function works on ClickHouse. It generates integers between a minimum value (0 by default) and a maximum value that you pass to it:

A similar result can be obtained with the {% code-line %}range{% code-line-end %} function, that returns arrays. If we only provide an argument, it behaves like {% code-line %}numbers{% code-line-end %}. And with {% code-line %}range{% code-line-end %} we can also specify a {% code-line %}start{% code-line-end %}, {% code-line %}end{% code-line-end %} and {% code-line %}step{% code-line-end %}:

This, combined with {% code-line %}arrayJoin{% code-line-end %} lets us do the same as {% code-line %}generate_series{% code-line-end %}:

Generating timestamp ranges on Postgres

The {% code-line %}generate_series{% code-line-end %} on Postgres can produce results with other types different than integers, while ClickHouse’s {% code-line %}range{% code-line-end %} and {% code-line %}numbers{% code-line-end %} only output integers. But, with some smart logic, we can achieve the same results. For example, on Postgres you’d generate a time-series with a DateTime value for each hour in a day this way, as in described here:

Generate a DateTime time series on ClickHouse, specifying the start and end DateTimes, and the step

On ClickHouse, you can achieve the same this way:

Generate a DateTime time series specifying the start date and the number of intervals

Another way of doing the same thing:

More functions like {% code-line %}addHours{% code-line-end %} are available: {% code-line %}dateAdd{% code-line-end %}, {% code-line %}timestampAdd{% code-line-end %} and addYears, addMonths, addWeeks, addDays, addHours, addMinutes, addSeconds, addQuarters.

Generate a time series using timeSlots

Using the {% code-line %}timeSlots{% code-line-end %} function, we can specify the start (DateTime), duration (seconds) and step (seconds) and it generates an array of DateTime values.

Generate a Date time series specifying the start and end date and the step

If you want to generate Date instead of DateTime objects, you’d change the {% code-line %}toDateTime{% code-line-end %} function by {% code-line %}toDate{% code-line-end %} from the first snippet. This is how you’d generate all the dates of January: