Get started with Propel's Serverless ClickHouse forever-free plan today. Propel is the only Serverless ClickHouse with a true pay-per-query pricing and instant auto-scaling.‍

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

What is the <span class="code-exp-header">subtractDays</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">subtractDays</span> function. As the name suggests, it enables you to subtract days from a given date or timestamp value.

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

subtractDays(<date>, <days>)

where

• <span class="code-exp-bracket">date</span> is the date or timestamp value to be adjusted.
• <span class="code-exp-bracket">days</span> is the number of days you want to subtract from the date or timestamp.

How to subtract days from a date

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

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

SELECT 
  subtractDays(toDate('2023-04-14'), 7)

To subtract 30 days from the current date, you can use:

SELECT 
  subtractDays(now(), 30)

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">subtractDays</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 = subtractDays(toDate('2023-04-14'), 3)

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.

Conclusion

In this post, we explored ClickHouse's <span class="code-exp">subtractDays</span> function to subtract days from a date or timestamp. By using this function, you can easily perform date subtraction.

Get started with Propel's Serverless ClickHouse forever-free plan today. Propel is the only Serverless ClickHouse with a true pay-per-query pricing and instant auto-scaling. Contact us to learn more about our volume-based discounts. Visit our pricing page for details.