How to Use DATEDIFF Snowflake Function: A Comprehensive Guide

Have you ever wondered how Snowflake’s date and time functions can enhance your organization’s data analysis capabilities? Consider a scenario where you must prepare a financial report by calculating the days between the order and delivery dates for all products sold within a week. While a Snowflake minus (-) operator might suffice to subtract these dates, it becomes inadequate if your report requires time values. 

This is where the DATEDIFF Snowflake function would be useful. It allows you to perform efficient data analysis and reporting tasks involving date, time, or timestamp expressions. With a single line of SQL query, the DATEDIFF function helps derive insights from your date/time-related data.

This article highlights the syntax of the Snowflake DATEDIFF function, its key considerations, use cases, practical examples, and drawbacks. Additionally, you will explore how to implement the DATEDIFF function using the Snowpark API.

What Is DATEDIFF Snowflake Function? 

The Snowflake DATEDIFF function computes the difference between two dates, times, or timestamp values. It supports various date units, such as years, months, weeks, and days, and time units, such as hours, minutes, seconds, or milliseconds. You can specify any date/time units in the DATEDIFF function, and it returns an integer value representing the number of units between two specified date/time values.

The DATEDIFF function is particularly beneficial for analyzing time intervals, tracking durations, managing deadlines, and other timestamp expressions in your data. These functionalities make DATEDIFF a valuable tool for time-based data analysis and reporting within the Snowflake data warehouse.

What Is the Syntax of DATEDIFF in Snowflake?

The DATEDIFF Snowflake function uses the following syntax:

DATEDIFF ( <Date_or_Time_Part>, <Start_Date_or_Time_Expression1>, <End_Date_or_Time_Expression2> )

Let’s understand the three arguments used in the DATEDIFF() syntax:

• <Date_or_Time_Part>: The unit of time for the difference calculation. It must be assigned with one of the Snowflake-supported date and time parts. The value for this argument can be a string literal with or without quotes.
• <Date_or_Time_Expression1>: The start date, time, or timestamp value.
• <Date_or_Time_Expression2>: The end date, time, or timestamp value.

The DATEDIFF() subtracts the <Date_or_Time_Expression1> from the <Date_or_Time_Expression2> and returns the difference in the number of specified date/time units.

Here’s an example of the DATEDIFF function:

SELECT DATEDIFF(month,'2024-01-02', '2024-05-20');

This SQL query calculates the number of months between 2024-01-02 and 2024-05-20 using DATEDIFF(). The result is four months.

What Are the Key Considerations for DATEDIFF in Snowflake?

When using the Snowflake DATEDIFF function, you must consider the following key factors to obtain accurate results. 

1) Select the Precise Date/Time Parts

The data/time part specified in the DATEDIFF function affects the accuracy of the results. Carefully choose the smaller and more precise date/time parts required for your analysis.

• To retrieve the exact number of days, use the day unit instead of the broader estimates like year or month.
• For precise time measurements, choose units like seconds or minutes instead of hours.

2) Leap Year Calculations

The presence of 29 days in February over leap years can impact calculations involving years. Results of Snowflake DATEDIFF() may differ by +/-1 day when calculating year differences spanning leap years. For better precision, it is advisable to use “day” units rather than “year”.

3) Time Zones within Timestamps

DATEDIFF considers time zones in timestamp calculations. As a result, differences may arise if the timestamps are from different time zones. To minimize inconsistencies, standardize your date on UTC timestamps before using them in the DATEDIFF function. For more information, read how to convert the timezone in Snowflake.

4) Daylight Saving Time

Daylight Saving Time (DST) transitions can impact the accuracy of the calculations. If a time range exceeds a DST transition, the elapsed time may be one hour more or less than the simple calendar calculations. Consider using smaller units like minutes or seconds to prevent the impact of the one-hour DST change.

How Can You Calculate the Difference between Date, Time, or Timestamp Values Using the DATEDIFF in Snowflake?

Let’s explore how the DATEDIFF Snowflake function can be used with various dates and timeframes.

1) Calculate the Number of Years between Two Dates in Snowflake

You must specify the date part as year and provide any start and end dates of your choice. Run the following SQL query to calculate the Snowflake years between dates using the DATEDIFF function:

SELECT DATEDIFF(year,  '2022-02-04', '2024-01-21') AS DIFFERENCE_IN_YEARS;

The result would be:

DIFFERENCE_IN_YEARS

————————————

2

You can also replace year with other date parts like quarter, month, week, or day.   

2) Calculate the Number of Hours between Two Time Values in Snowflake

You must include the time part as the hour and specify any start and end time of your choice. Execute the following SQL query to calculate the difference in hours between the given time values using the DATEDIFF function:

SELECT DATEDIFF(hour,  '02:02:54', '04:10:11') AS DIFFERENCE_IN_HOURS;

The result would be:

DIFFERENCE_IN_HOURS

————————————

2

You can use other time parts like minute, second, millisecond, microsecond, or nanosecond.   

3) Calculate the Number of Days between Two Timestamp Values in Snowflake

You must include a day unit, a starting timestamp, and an ending timestamp. You can calculate the difference in days between the given timestamp values by executing the following SQL query with the DATEDIFF function:

SELECT DATEDIFF(day,  '2019-10-05 03:12:44', '2024-05-21 06:11:12') AS DIFFERENCE_IN_DAYS;

The result would be:

DIFFERENCE_IN_DAYS

———————————

1690

You can choose from either date or time parts to determine the difference between the two timestamp values.

Why the DATEDIFF Function Truncates Date, Time, or Timestamp Values

The DATEDIFF function computes the difference between two date or time values and truncates the result to the nearest whole unit of the specified date/time part. It discards any fractional part of the unit. 

Let’s look into an example. If the difference between two dates is three months and 22 days, and you use DATEDIFF to calculate the difference in months, it will return three months, ignoring the 22 days. This avoids rounding up to the nearest unit, which is four months.

Here’s an SQL query that demonstrates how the DATEDIFF function truncates the date and timestamp values instead of rounding up to the nearest value:

SELECT 
    COLUMN1 AS START_DATE,
    COLUMN2 AS END_DATE,
    DATEDIFF(YEAR, COLUMN1, COLUMN2) AS YEAR_DIFFERENCE,
    DATEDIFF(MONTH, COLUMN1, COLUMN2) AS MONTH_DIFFERENCE,
    DATEDIFF(DAY, COLUMN1, COLUMN2) AS DAY_DIFFERENCE
FROM (
    SELECT '2022-11-01' AS COLUMN1, '2023-11-01' AS COLUMN2
    UNION ALL
    SELECT '2022-01-01', '2022-12-31'
    UNION ALL
    SELECT '2022-11-11 01:00:00',  '2022-12-11 10:00:00'
    UNION ALL
    SELECT '2022-11-01 00:00:00', '2022-11-30 23:59:59'
) AS DateValues;

Execute the query to see the following output:

START_DATE END_DATE                       YEAR_DIFF MONTH_DIFF   DAY_DIFF

——————- ——————- ————— ——————-   ————–

2022-11-01 2023-11-01                   1              12        365

2022-01-01 2022-12-31                   0         11   364

2022-11-11 01:00:00  2022-12-11 10:00:00           0         1 30

2022-11-01 00:00:00 2022-11-30 23:59:59           0         0 29

The above example shows that the year difference between 2022-01-01 and 2023-12-31 is 0 years instead of rounding to one full year. Similar logic is applied to calculate the difference in units between the time values.

What Are the Use Cases of DATEDIFF Snowflake Function?

Here are a few practical use cases of the Snowflake DATEDIFF function:

1) Monitoring Product Expiry 

The DATEDIFF function helps you monitor product expiration dates by calculating the difference in days between the current date and each product’s expiry date. This automated tracking of expiry dates helps avoid the risk of selling expired products.

Here is a simple example of how DATEDIFF functions identify product expiry dates:

CREATE TABLE product_expiry_date (
    product_id INT,
    expiry_date DATE
);
INSERT INTO product_expiry_date VALUES
(1, '2024-05-25'),
(2, '2024-09-05'),
(3, '2023-11-22'),
(4, '2024-08-25'),
(5, '2023-07-28')
SELECT 
    product_id AS "Product ID", 
    CASE 
        WHEN DATEDIFF(DAY, GETDATE(), expiry_date) < 0 THEN 'EXPIRED'
        ELSE CAST(DATEDIFF(DAY, GETDATE(), expiry_date) AS VARCHAR)
    END AS "Remaining Days Until Expiry"
FROM 
    product_expiry_date;

Run the query to get a similar output as shown below:

Product ID Remaining Days Until Expiry

———–     ————————————-

   1 3

   2 106

   3 EXPIRED

   4 95

   5 EXPIRED

2) Time-Based Reporting and Analytics

With the DATEDIFF function, you can measure intervals between two dates to assess your business growth or changes over time.

Run the following example of how to measure user registration growth between May and April:  

CREATE TABLE user_registrations (
    user_id INT,
    register_date DATE
);

INSERT INTO user_registrations VALUES
(101, '2024-04-05'),
(102, '2024-04-15'),
(103, '2024-05-25'),
(104, '2024-05-05'),
(105, '2024-05-10');

SELECT 
    DATEDIFF(DAY, MIN(register_date), MAX(register_date)) AS "Difference in Days"
FROM 
    user_registrations

The result would be:

Difference in Days

————————

50 

3) Calculation of User Subscriptions Duration

The DATEDIFF function helps calculate the duration of a user subscription. It determines the difference in days between the start and end dates of the subscription period. This calculation helps services bill users accurately based on the subscription duration.

Execute the following example to understand how the DATEDIFF function calculates the duration of the subscription in days:

CREATE TABLE UserSubscriptions (
    userID INT,
    startDate DATE,
    endDate DATE
);

INSERT INTO UserSubscriptions VALUES
(101, '2023-01-01', '2024-05-31'),
(102, '2023-01-15', '2024-05-14'),
(103, '2024-02-05', '2024-03-05'),
(104, '2023-02-20', '2024-03-20'),
(105, '2024-03-01', '2024-03-31');

SELECT 
    userID, 
    DATEDIFF(DAY, startDate, endDate) AS "Subscription Duration"
FROM 
    UserSubscriptions;

The result would be:

userID Subscription Duration

———– —————————-

101 516

102 485

103 29

104 394

105 30

Practical Example: Analyzing Order Shipping Intervals with Snowflake DATEDIFF()

This example shows you how to use the Snowflake DATEDIFF function in a sales scenario. Let’s look into the following SQL query to understand how to calculate the time intervals between order and shipping timestamps:

DATEDIFF Snowflake: Practical Example

What Are the Best Practices for Effectively Utilizing the DATEDIFF Snowflake Function?

Here are a few practical tips while using the DATEDIFF function in Snowflake:

• Select the appropriate date or time part depending on your specific reporting or analysis needs. If you need the exact number of months, specify the date part as “month”. 

• To achieve precise results, utilize smaller date or time parts like week, day, minute, or second instead of broader estimates like year or hour.

• Convert your timestamps to UTC before using the DATEDIFF function to avoid producing inconsistent results from different time zones.

• Since DATEDIFF only calculates the difference between date/times values of a single unit, utilize subqueries to calculate the differences in multiple units. 

• When calculating differences between date or timestamp values, be cautious in interpreting the negative results. It indicates the start date is later than the end date.

• Only use the DATEDIFF Snowflake function where needed instead of directly on the large Snowflake tables. This avoids unnecessary calculations on large datasets. 

What Are the Drawbacks of DATEDIFF Snowflake Function?

When using DATADIFF() in Snowflake, you may find the following issues:

Impact of DATEDIFF Function on Snowflake Performance 

If you are working with large datasets, extensive use of the DATEDIFF function can negatively impact Snowflake’s performance. The function requires scanning large amounts of data, which delays the query response times. Additionally, queries involving DATEDIFF that have complex join or filtering conditions might not be optimized efficiently by Snowflake, potentially increasing processing costs.

No Automatic Time Zone Conversion

The DATEDIFF Snowflake function does not automatically adjust differences in time zones when comparing time stamps. If the timestamps being compared are in different time zones, it can cause inconsistent results. To avoid such issues, you must explicitly convert the timestamps to UTC before applying DATEDIFF.

Only a Single Date/Time Part is Allowed

You cannot combine multiple date/time parts, such as year or hour, in the same SQL query using the DATEDIFF function. Since the DATEDIFF() allows you to specify only one date or time part per function call, you must write additional queries for each unit.

Impact of Negative Values

A negative result from the DATEDIFF Snowflake function signifies that the first date or time is later than the second instead of indicating a reversed time flow. For example, if the difference in days between two dates is -3, it shows that the first date is three days after the second. 

How Do You Use the DATEDIFF Function with the Snowpark API in Snowflake?

The Snowpark API in Snowflake offers a built-in datediff function similar to the DATEDIFF function in SQL.

The Snowpark datediff function also determines the difference between two timestamps, date, or time values based on the specified date or time part. You can use this function with Java, Python, or Scala. Let’s look at an example of Snowpark datediff function in Python:

import snowflake.snowpark as snowpark
from snowflake.snowpark.functions.datediff
import datetime
DateDataFrame = session.create_dataframe([[datetime.date(2022, 2, 4), datetime.date(2024, 2, 4)]], schema=["date_column1", "date_column2"])
DateDataFrame.select(datediff("year", col("date_column1"), col("date_column2")).alias("year_difference")).show()

The result would be:

YEAR_DIFFERENCE

—————

2

How Hevo Helps in Utilizing Snowflake DATEDIFF Function for Better Data Analysis?

Efficient time-based analysis becomes challenging when your organization’s data is spread across multiple databases. Consolidating all the data into Snowflake provides support for date and time-related analysis with the DATEDIFF function. Consider using a real-time ELT, no-code data pipeline platform like Hevo Data to cost-effectively automate the data migration process to Snowflake. For more information on how to configure Snowflake as your destination, read the Hevo Documentation for Snowflake. An added benefit of Hevo Data is that it also helps you integrate with Snowpark API.

Here are the key features of Hevo Data for utilizing the DATEDIFF function:

• Data Transformation: Hevo offers analyst-friendly drag-and-drop transformations like Date and Control functions. This option can help remove any inconsistencies in date or time formats before loading the data to Snowflake. Then, you can apply DATEDIFF functions in your date/time-related calculations without any interruptions.

• Incremental Data Load: Hevo Data facilitates real-time data transfer, allowing you to perform DATEDIFF() on data as soon as it arrives in Snowflake. This ensures that your date-based analytics and reports are always up-to-date.

Conclusion

You have explored how Snowflake facilitates time-based analysis, enabling you to identify trends in user engagement, customer behavior, and sales variations over time.

With the help of the DATEDIFF function, Snowflake can measure temporal changes in your business by calculating the differences between specific dates or timestamps. You can monitor business progress, evaluate performance, and make smart decisions based on date or time-related calculations. Using timely insights from the DATEDIFF Snowflake function, your organization can accommodate changing business requirements and achieve success.

Frequently Asked Questions (FAQs)

Q1. Given BEFORE_DATETIME and AFTER_DATETIME, some string values in the BEFORE_DATETIME cause an error when using DATEDIFF(). How can I calculate the difference in days between the two dates, ignoring the bad string values? 

A. You can apply the TRY_TO_TIMESTAMP function to date values. It parses your BEFORE_DATETIME as a timestamp value with error-handling support; it returns NULL rather than raising an error if the conversion fails.

Q2. How do I calculate a user’s age based on their birthday year and the current year using the DATEDIFF function in Snowflake? 

A. Initially, calculate the difference in years between the current date and the user’s birthday date using the DATEDIFF function. Then, add the difference in years to the birthday date using the DATEADD function. If the resulting date is later than the current date, subtract 1 from the resulting date. If it falls on or before the current date, the result is already accurate.