Mastering Time Calculations in Snowflake with DATEDIFF

In data warehousing, understanding the passage of time is crucial for tasks like tracking trends, analyzing customer behavior, or scheduling events. Snowflake, the powerful cloud-based data platform, provides an elegant solution for tackling time-related calculations – the DATEDIFF function.

What is DATEDIFF?

At its core, the DATEDIFF function calculates the difference between two date, time, or timestamp values. It’s your go-to tool for determining durations such as:

• Number of days between orders
• Number of months a customer has been active
• Number of seconds to process a transaction

DATEDIFF Syntax

The DATEDIFF function follows this structure:

SQL

DATEDIFF(date_part, start_date, end_date)

Use code with caution.

content_copy

Let’s break down the components:

• date_part: The units of measurement you want to use, like ‘year,’ ‘month,’ ‘day,’ ‘hour,’ ‘second,’ and more.
• start_date: The beginning date, time, or timestamp.
• end_date: The ending date, time, or timestamp.

Examples in Action

1. Days Between Orders
2. SQL
3. SELECT order_id, DATEDIFF(day, order_date, shipped_date) AS delivery_time
4. FROM orders; 
5. Use code with caution.
6. content_copy
7. Customer Subscription Length (in Months)
8. SQL
9. SELECT customer_id, DATEDIFF(month, signup_date, CURRENT_DATE) AS months_active 
10. FROM customers;
11. Use code with caution.
12. content_copy
13. Time to Resolve an Issue (in Seconds)
14. SQL
15. SELECT ticket_id, DATEDIFF(second, opened_timestamp, closed_timestamp) AS resolution_time
16. FROM support_tickets;
17. Use code with caution.
18. content_copy

Controlling ‘Week’ Calculations

When using the ‘week’ date part, Snowflake’s DATEDIFF considers the setting of the WEEK_START session parameter. This parameter determines the first day of the week. To change it, you can use:

SQL

ALTER SESSION SET WEEK_START = 1; — Sets Monday as the first day of the week

Use code with caution.

content_copy

Beyond Basics

Remember that Snowflake also offers the TIMEDIFF and TIMESTAMPDIFF functions, working identically to DATEDIFF for specific use with time and timestamp data types.

Key Takeaways

• DATEDIFF is your time-calculation ally in Snowflake.
• Employ different date parts for granular calculations.
• Manage week calculations by controlling the WEEK_START parameter.