You may be wondering if generate_series 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 generate_series, and more.
First, let’s see how the numbers 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 range function, that returns arrays. If we only provide an argument, it behaves like numbers. And with range we can also specify a start, end and step:
This, combined with arrayJoin lets us do the same as generate_series:
The generate_series on Postgres can produce results with other types different than integers, while ClickHouse®’s range and numbers 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:
On ClickHouse®, you can achieve the same this way:
Another way of doing the same thing:
More functions like addHours are available: dateAdd, timestampAdd and addYears, addMonths, addWeeks, addDays, addHours, addMinutes, addSeconds, addQuarters.
Using the timeSlots function, we can specify the start (DateTime), duration (seconds) and step (seconds) and it generates an array of DateTime values.
If you want to generate Date instead of DateTime objects, you’d change the toDateTime function by toDate from the first snippet. This is how you’d generate all the dates of January: