ClickHouse tips #5: Adding and subtracting intervals

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: