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 date_sub 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 date_sub function. As the name suggests, it enables you to subtract a specific interval from a given date or timestamp value.

The date_sub function has the following syntax:

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

where

unit is the interval you want to subtract, such as “day”, “hour”, “minute”, “second”, etc.
value is the number of intervals you want to subtract from the date or timestamp.
date 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 date_sub 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 unit 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 orders with the following columns:

order_id: Unique identifier for each order.
order_date: The date when the order was placed.
customer_id: 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 date_sub 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 orders table where the order_date 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 date_sub 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.

Heading 1

Heading 2

Heading 3

Heading 4

Heading 5

Heading 6

Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur.