Snowflake DATEDIFF: Calculate time differences in Snowflake

Working with dates in a data warehouse often means measuring the time between events. How long did it take for a customer to convert after signing up? What's the average time between support tickets? How many days has it been since a user last logged in?

That's where Snowflake's `DATEDIFF` function comes in. It helps you calculate the difference between two dates or timestamps, making it easy to analyze time-based patterns in your data.

What is the DATEDIFF function in Snowflake?

The `DATEDIFF` function calculates the difference between two dates or timestamps in a specified unit of time (like days, hours, or minutes). Think of it as a specialized calculator that helps you measure the distance between two points on a timeline.

Syntax of DATEDIFF

The basic syntax for `DATEDIFF` in Snowflake is straightforward:

```sql

DATEDIFF(date_or_time_part, start_date, end_date)

```

The function takes three arguments:

• `date_or_time_part`: The unit you want to measure the difference in (like `DAY`, `MONTH`, `YEAR`, `HOUR`, etc.)
• `start_date`: The earlier date or timestamp
• `end_date`: The later date or timestamp

Let’s look at a quick example to see it in action. Say you're a data engineer at a SaaS company, and you want to know how many days it took for a customer to upgrade from their free trial.

```sql

SELECT 

    user_id,

    DATEDIFF(day, trial_start_date, upgrade_date) AS days_to_upgrade

FROM user_conversions

WHERE upgrade_date IS NOT NULL;

```

This query calculates the number of days between the `trial_start_date` and the `upgrade_date`, giving you how many days it took for them to upgrade as `days_to_upgrade`.

Examples for when to use DATEDIFF

Think about how many times you have to calculate the difference between two dates in your head in real life. Working with data, it’s no different. It’d be impossible to list the number of ways to use DATEDIFF, but let’s look at some pretty universal ways a data team might deploy it.

1. Identify churn risk

Imagine you're at a SaaS company and you need to identify users at risk of churning based on their login patterns. Here's how you might do this.

```sql

SELECT 

    user_id,

    email,

    last_login_date,

    DATEDIFF(day, last_login_date, CURRENT_DATE()) AS days_since_login,

    subscription_status,

    CASE 

        WHEN DATEDIFF(day, last_login_date, CURRENT_DATE()) > 30 THEN 'High Risk'

        WHEN DATEDIFF(day, last_login_date, CURRENT_DATE()) > 14 THEN 'Medium Risk'

        ELSE 'Low Risk'

    END AS churn_risk

FROM users

WHERE subscription_status = 'active'

ORDER BY days_since_login DESC;

```

This query uses DATEDIFF to calculate how long it’s been since a user has logged in. Then, it segments users into risk categories based on how long it’s been since they last logged in.

2. Optimize customer support operations

For support teams, response and resolution times are crucial metrics. Here's how you might analyze support ticket efficiency.

```sql

SELECT 

    agent_name,

    COUNT(*) as tickets_resolved,

    AVG(DATEDIFF(minute, created_at, first_response_at)) AS avg_first_response_time,

    AVG(DATEDIFF(hour, created_at, resolved_at)) AS avg_resolution_time,

    COUNT(CASE 

        WHEN DATEDIFF(minute, created_at, first_response_at) > 30 THEN 1 

    END) AS sla_breaches

FROM support_tickets

WHERE status = 'resolved'

    AND resolved_at >= DATEADD(day, -30, CURRENT_DATE())

GROUP BY agent_name

ORDER BY avg_resolution_time;

```

This query tracks response and resolution times for individual agents by calculating the time difference between when a ticket is created, and when it’s responded to and resolved. It then labels any ticket where the response time is more than 30 minutes as `sla_breaches`.

3. Measure marketing channel effectiveness

Using DATEDIFF can also help you determine which marketing channels are driving conversions the fastest.

```sql

WITH conversion_metrics AS (

    SELECT 

        marketing_channel,

        AVG(DATEDIFF(day, first_visit_date, conversion_date)) AS avg_days_to_convert,

        MIN(DATEDIFF(day, first_visit_date, conversion_date)) AS fastest_conversion,

        MAX(DATEDIFF(day, first_visit_date, conversion_date)) AS slowest_conversion,

        COUNT(*) as total_conversions,

        SUM(conversion_value) as total_revenue

    FROM user_journeys

    WHERE conversion_date IS NOT NULL

        AND conversion_date >= DATEADD(month, -3, CURRENT_DATE())

    GROUP BY marketing_channel

)

SELECT 

    marketing_channel,

    avg_days_to_convert,

    fastest_conversion,

    slowest_conversion,

    total_conversions,

    total_revenue,

    total_revenue / total_conversions AS avg_conversion_value

FROM conversion_metrics

ORDER BY avg_days_to_convert;

```

This query uses DATEDIFF to calculate the average, minimum, and maximum days to conversion for a given marketing channel, labeling them as `avg_days_to_convert`, `fastest_conversion`, and `slowest_conversion`, respectively.

It then orders each channel according to `avg_days_to_convert`, giving you a ranked list of top-performing channels to share in a dashboard with stakeholders.

4. Analyze subscription performance

For SaaS businesses, there’s so much psychology that goes into pricing, and to make the best decisions you can, you have to be able to act on your data.

```sql

SELECT 

    subscription_plan,

    COUNT(*) as total_subscribers,

    AVG(DATEDIFF(day, trial_start, conversion_date)) AS avg_days_to_convert,

    AVG(CASE 

        WHEN subscription_end IS NOT NULL 

        THEN DATEDIFF(month, subscription_start, subscription_end)

        ELSE DATEDIFF(month, subscription_start, CURRENT_DATE())

    END) AS avg_subscription_length,

    SUM(CASE 

        WHEN subscription_end IS NULL 

        THEN 1 

        ELSE 0 

    END) AS active_subscriptions

FROM subscriptions

GROUP BY subscription_plan

ORDER BY avg_subscription_length DESC;

```

This query does a handful of helpful things:

• Calculates the average time from trial conversion as `avg_days_to_convert`
• Measures average customer lifetime by plan as `avg_subscription_length`
• Identifies which plans have the highest retention
• Tracks active versus churned subscriptions

This kind of information is invaluable when making pricing and packaging decisions, and DATEDIFF helps make it happen.

Tips and tricks when using DATEDIFF in Snowflake

There are a few quirks and best practices to keep in mind when working with DATEDIFF. Nothing too difficult, but knowing about them ahead of time will save you from having to re-run some queries when you’re getting started.

1. Handle NULL values carefully: If either the start or end date is NULL, DATEDIFF will return NULL. Consider using COALESCE or IFNULL when necessary.
2. Be mindful of time zones: When working with timestamps, remember that DATEDIFF respects time zone differences. Use CONVERT_TIMEZONE if you need to standardize timestamps before comparison.
3. Choose the right date part: Pick the most appropriate unit for your analysis. Using too large a unit (like YEAR for short intervals) might miss important patterns, while too small a unit (like SECOND for long intervals) might be unnecessarily granular.
4. Remember it's inclusive: DATEDIFF includes both the start and end dates in its calculation. For example, DATEDIFF(day, '2024-01-01', '2024-01-02') returns 1, not 2.

DATEDIFF vs other time functions

Snowflake offers several functions for working with dates and times. Here's when to use each:

• DATEDIFF: Use when you need to calculate the exact difference between two dates in a specific unit
• DATEADD: Use when you need to add or subtract time from a date
• TIMEDIFF: Similar to DATEDIFF but specifically for time components
• DATE_TRUNC: Use when you need to round down dates to a specific unit

Calculate time differences in Snowflake with DATEDIFF

DATEDIFF is a powerful function that makes time-based analysis in Snowflake straightforward. Whether you're tracking user engagement, monitoring business operations, or analyzing marketing performance, DATEDIFF helps you understand the temporal aspects of your data.

Remember to consider the appropriate time unit for your analysis, handle NULL values appropriately, and be mindful of time zones when working with timestamps. With these tips in mind, you'll be well-equipped to perform time-based analysis in Snowflake.

Using Snowflake as your data warehouse? Try out Metaplane's native Snowflake app to get end-to-end dataobservability right inside your data warehouse. Plus, you can use your existing Snowflake credits to make it happen. Learn more here.