How to subtract days from a date in ClickHouse

Learn how to subtract days from a date in ClickHouse using the powerful date_sub function.

Photo: Propel

Discover the power of ClickHouse's date functions to manipulate dates with precision and ease.

What is the <span class="code-exp-header">date_sub</span> function?

ClickHouse provides a range of powerful date and time functions that help you manipulate date and time data types in various ways. One such versatile function is the <span class="code-exp">date_sub</span> function. As the name suggests, it enables you to subtract a specific interval from a given date or timestamp value.

The <span class="code-exp">date_sub</span> function has the following syntax:

date_sub(<unit>, <value>, <date>)

where

  • <span class="code-exp-bracket">unit</span> is the interval you want to subtract, such as “day”, “hour”, “minute”, “second”, etc.
  • <span class="code-exp-bracket">value</span> is the number of intervals you want to subtract from the date or timestamp.
  • <span class="code-exp-bracket">date</span> is the date or timestamp value to be adjusted.

How to subtract days from a date

You can subtract days from a date in ClickHouse using the <span class="code-exp">date_sub</span> function.

For example, if you want to subtract 7 days from a date, the query would be:

SELECT date_sub('day', 7, toDate('2023-04-14'))

Remember that the <span class="code-exp-bracket">unit</span> parameter must be in lowercase letters.

Example: TacoSoft date manipulation

Let's look at an example using TacoSoft, our imaginary SaaS app to manage taquerias. Suppose you have a table called <span class="code-exp">orders</span> with the following columns:

  • <span class="code-exp">order_id</span>: Unique identifier for each order.
  • <span class="code-exp">order_date</span>: The date when the order was placed.
  • <span class="code-exp">customer_id</span>: Unique identifier for each customer.

Your task is to retrieve a list of all orders placed three days before a specific date. In this case, let's say you want to retrieve all orders placed three days before April 14, 2023. You can use the <span class="code-exp">date_sub</span> function to subtract days from a date in ClickHouse and achieve this goal.

Here is the SQL query for this task:

SELECT
  order_id,
  order_date,
  customer_id
FROM
  orders
WHERE
  order_date = date_sub('day', 3, toDate('2023-04-14'));

This query would return all the rows in the <span class="code-exp">orders</span> table where the <span class="code-exp">order_date</span> is exactly three days before April 14, 2023.

Build faster with Propel: A Serverless Clickhouse for developers

At Propel, we offer a fully managed ClickHouse service that allows you to focus more on drawing insights from your data and less on infrastructure management. Propel provides an API for data access, React components, and built-in multi-tenant access controls, making it easier and faster for you to build data-intensive applications.

You can connect your own ClickHouse with Propel, whether it's self-hosted or on the ClickHouse Cloud, or take advantage of our fully managed serverless cloud.

Connect your own ClickHouse



✅ Works with self-hosted ClickHouse.
✅ Works with ClickHouse Cloud.
✅ Full control of your ClickHouse deployment.
✅ Data stays in your infrastructure.
✅ Data is encrypted in transit.
✅ Only pay for data passthrough.

Read the docs

Fully managed serverless cloud



✅ Ingest data from any data source.
✅ No infrastructure to manage or scale.
✅ Serverless auto-scaling.
✅ Mission-critical availability.
✅ Unlimited storage.
✅ Data is encrypted at rest and in transit.
✅ Only pay for the storage you use and queries you make.

Read the docs

Conclusion

In this post, we explored ClickHouse's <span class="code-exp">date_sub</span> function to subtract days from a date or timestamp. By using this function, you can easily perform date subtraction. This tool can be valuable for filtering and analyzing time-based data in your ClickHouse database.

ClickHouse offers many other date and time functions to help you manipulate and work with date and time data types. Don't hesitate to explore the official ClickHouse documentation to learn more about these functions and how to use them effectively.

Further Reading

For more insights on how to use ClickHouse for your data operations, including other date and time functions, check out our other posts. We cover a wide range of topics from advanced querying techniques to performance tuning for large datasets. Whether you're a beginner or an experienced data professional, there's always something new to learn!

Related posts

Start shipping today

Deliver the analytics your customers have been asking for.