Truncate dates or times in Snowflake with DATE_TRUNC

Handling dates and times is a common task for data engineers, which makes sense. So much of our data analysis involves examining time: How much did we sell last week? How are we performing compared to last month?

Luckily, Snowflake has a handful of powerful functions that make it easier to work with and manage time when it comes to your data. One of those functions is `DATE_TRUNC`.

In this post, we’ll look at what `DATE_TRUNC` does, how to use it, and when to use it versus other time-related functions in Snowflake.

What is the DATE_TRUNC Function in Snowflake?

When raw date or timestamp data arrives in your warehouse, it often looks something like this: 2024-11-20 23:59:59. Which is fine. It’s accurate, and you can read it, but you don’t always need to-the-second granularity. Including more details than you need can make things hard to read or group in the way that you want to.

`DATE_TRUNC` helps you eliminate the fine-grain details by truncating a date, time, or timestamp to a specified part, like day, month, or year. This way, you can focus one the information you need, get rid of the information you don’t, and easily conduct your time-based analysis.

Syntax of DATE_TRUNC

The syntax for using `DATE_TRUNC` is pretty simple:

```sql

DATE_TRUNC(part, date_or_time_expression)

```

In this function, `part` is the time unit you want to truncate to (like `YEAR`, `MONTH`, `WEEK`, `DAY`, or `HOUR`). `date_or_time_expression` is the input date, time, or timestamp you want to truncate.

Let’s walk through an example. You’re the data engineer for a customer support platform, and you’re ​​analyzing response times to see how quickly support agents are resolving tickets. Your raw data, though, is down to the second, making it hard to summarize the data.

Your team wants to analyze how many tickets were resolved each hour to track productivity trends throughout the day, so you use this bit of SQL to truncate the `response_time` to the start of each hour.

```sql

SELECT DATE_TRUNC('HOUR', response_time) AS hour_start, COUNT(*) AS tickets_resolved

FROM ticket_data 

GROUP BY hour_start 

ORDER BY hour_start;

```

Now, your results look like this, showing you how many tickets were resolved during each hour time block.

Examples of when to use `DATE_TRUNC`

So much of data engineering is about taking the huge amount of data you're given and getting rid of what’s not relevant, so that you can pull out the insights you need—and `DATE_TRUNC` is no different. By removing a level of specificity around your raw data, you can better group data how you need it. Here are a few common reasons you might use `DATE_TRUNC` in a Snowflake. 

1. Aggregating data by a certain timeframe

Need to know how many leads the marketing team got last month? Or what total sales were in a given week? `DATE_TRUNC` is your answer. `DATE_TRUNC` lets you truncate dates and times by `YEAR`, `QUARTER`, `MONTH`, `WEEK`, `DAY`, or `HOUR`, so you can analyze data by any timeframe you need.

2. Comparing data against specific time periods

Perhaps just as common as needing to aggregate data for weekly or monthly reports is wanting to compare that data against a previous time period. `DATE_TRUNC` can help you format your data just how you need for easy week-over-week, month-over-month, and year-over-year reporting.

3. Standardizing timestamps

When data is flowing into Snowflake from different sources, the level of timestamp granularity might look different coming from each source—making it hard to integrate your data. `DATE_TRUNC` can help you standardize the format and granularity of your data, making it much easier to work with.

Using `DATE_TRUNC` vs. `DATE_PART` vs. `TIMESTAMPADD`

`DATE_TRUNC` isn’t the only date-formatting function that Snowflake offers. There are a couple of other time-based functions that are similar to `DATE_TRUNC` but perform slightly different jobs.

• `DATE_PART`: Extracts a specific part of a date or time (e.g., just the month or day in a date).
• `TIMESTAMPADD`: Adds or subtracts time units from a timestamp.

Use `DATE_PART` if you need just the numeric value, and `TIMESTAMPADD` for offset adjustments.

Tips to keep in mind when using `DATE_TRUNC`

We’ve covered how and when to use `DATE_TRUNC`, but some quirks within this function are worth noting before you start using it.

Weeks start on Sunday: By default, Snowflake treats Sunday as the start of the week. If your organization bucks social norms and starts their week on Monday, you’ll need to adjust calculations.‍

Time zones matter: `DATE_TRUNC` operates on the timezone context of the timestamp. If you’re working with data from multiple regions, ensure that timestamps are standardized to the desired time zone using `CONVERT_TIMEZONE` before truncating:

```sql

SELECT DATE_TRUNC('DAY', CONVERT_TIMEZONE('UTC', 'America/New_York', event_time)) AS local_day_start

FROM global_events;

```

Granularity compatibility: The `DATE_TRUNC` part must be equal to or larger than the granularity of the input. For example, Truncating a `DATE` to `HOUR` will return an error because dates lack time components.

Leap years are accounted for: When truncating to larger units like months or years, Snowflake properly accounts for calendar quirks like leap years.

Transform your date and time data how you need it with `DATE_TRUNC`

`DATE_TRUNC` is a lifesaver for formatting dates and times how you need them in Snowflake. It's particularly helpful when you're grouping data, standardizing timestamps, or preparing data for reports—letting you group your time-based data by whatever unit of time you need.