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:

SCHEMA > Evolution
Get 10 links weekly to the Data and AI articles the Tinybird team is reading.

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 #1: Calculating Aggregations After a Given Date
Best practices for timestamps and time zones in databases
ClickHouse tips #11: Best way to get query types

Tinybird

Team

Sep 22, 2021
ClickHouse Tips #12: Apply Functions to Columns with a Single Call

Tinybird

Team

Sep 29, 2021
ClickHouse tips #3: the transform function
Changelog: BigQuery connector, CLI improvements, Pagination on endpoints and more

Tinybird

Team

Mar 04, 2021
ClickHouse tips #7: Forward and backfilling null values
ClickHouse Meetup Madrid videos
A big performance boost, adding columns and more

Tinybird

Team

May 31, 2021

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.
Tinybird is not affiliated with, associated with, or sponsored by ClickHouse, Inc. ClickHouse® is a registered trademark of ClickHouse, Inc.