ClickHouse tips #8: Generating time-series on ClickHouse

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:

Generating timestamp ranges on Postgres

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:

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 addHours are available: dateAdd, timestampAdd and addYears, addMonths, addWeeks, addDays, addHours, addMinutes, addSeconds, addQuarters.

Generate a time series using timeSlots

Using the timeSlots 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 toDateTime function by toDate from the first snippet. This is how you’d generate all the dates of January: