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:

  • HOUR
  • DAY
  • WEEK
  • 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

Real-time Data Visualization: How to build faster dashboards
A new way to create intermediate Data Sources in Tinybird
Jun 15, 2023
Export data from Tinybird to Amazon S3 with the S3 Sink
Mar 21, 2024
Tinybird: A ksqlDB alternative when stateful stream processing isn't enough
To the limits of SQL... and beyond
Automating data workflows with plaintext files and Git
Chatting GraphQL with Jamie Barton of Grafbase
Apr 24, 2023
What it takes to build a real-time recommendation system
We launched an open source ClickHouse Knowledge Base
Oct 11, 2022
The definition of real-time data

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.