Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.

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
Mar 24, 2021
 ・ 
  min read

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 {% code-line %}INTERVAL{% code-line-end %} 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:

  • {% code-line %}SECOND{% code-line-end %}
  • {% code-line %}MINUTE{% code-line-end %}
  • {% code-line %}HOUR{% code-line-end %}
  • {% code-line %}DAY{% code-line-end %}
  • {% code-line %}WEEK{% code-line-end %}
  • {% code-line %}MONTH{% code-line-end %}
  • {% code-line %}QUARTER{% code-line-end %}
  • {% code-line %}YEAR{% code-line-end %}

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 {% code-line %}now(){% code-line-end %}, interval and all the interval types ({% code-line %}day{% code-line-end %}, {% code-line %}year{% code-line-end %}…) are case-insensitive, so you can use their uppercase versions as well:

Subscribe to our newsletter

Musings on transformations, tables and everything in between.