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 {% 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:
%20(1).jpg)
