Mar 24, 2021

ClickHouse tips #5: Adding and subtracting intervals

Tips and recipes to learn how to make the most of ClickHouse, curated weekly by the Tinybird team.
Alberto Romeu
Backend Developer

ClickHouse lets you add and subtract time and date intervals. For example, to subtract 1 year to the current date you would to

You can also add or subtract more than one interval at a time:

And the INTERVAL syntax you’ve used in other databases like Postgres is also supported. One advantage of it is that it lets you get rid of parentheses. So the previous query can be rewritten as follows:

These are all the intervals supported, from the docs:

  • SECOND
  • MINUTE
  • HOUR
  • DAY
  • WEEK
  • MONTH
  • QUARTER
  • YEAR

So you could get it as complex as you want:

The smallest interval precision supported is second, and if you want to go lower than that there are some workarounds like the described in this issue.

Finally, note that now(), interval and all the interval types (day, year…) are case-insensitive, so you can use their uppercase versions as well:

Do you like this post?

Related posts

ClickHouse tips #9: Filling gaps in time-series on ClickHouse
ClickHouse tips #8: Generating time-series on ClickHouse
ClickHouse tips #3: the transform function
Clickhouse Tips #1: Calculating Aggregations After a Given Date
Coming soon on ClickHouse: Window functions
ClickHouse Tips #12: Apply Functions to Columns with a Single Call

Tinybird

Team

Sep 29, 2021
Roll up data with Materialized Views
Experimental ClickHouse: Projections
ClickHouse JOINs... 100x faster
Want a managed ClickHouse? Here are some options

Build fast data products, faster.

Try Tinybird and bring your data sources together and enable engineers to build with data in minutes. No credit card required, free to get started.
Need more? Contact sales for Enterprise support.