Using ClickHouse for time-series data

[Alex Kurkin]

hey guys,

is there any example of working with time-series data?

For example, to start with, something as simple as elastic's date_histogram aggregation 

I've seen the timeSlots function, but there's no usage example, I'd appreciate any hints. 

Thanks,

Alex

[man...@gmail.com]

You could use functions https://clickhouse.yandex/reference_en.html#Functions%20for%20working%20with%20dates%20and%20times
to transform and round dates and datetimes.

Example: https://gist.github.com/alexey-milovidov/6fd9246ce44b48345bee3a0df3da5ab0

When rounding to small intervals (for example, 5 seconds), you could convert DateTime to UInt32, then use intDiv function, then multiply and convert back.

Example: https://gist.github.com/alexey-milovidov/2dee968eb95df63b271208f89d3697c3

[Alex Kurkin]

Fantastic, the 5-second example is brilliant, exactly what I was looking for. 

Thanks much for spending time to explain this.

Alex

[Burak Emre Kabakcı]

For small intervals, it works but for bigger intervals such as week, month and year a date truncation function would be great. Here is the Postgresql implementation: https://www.postgresql.org/docs/8.4/static/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC