HOW TO: Use Snowflake DATEADD to Manipulate Dates (2024)

Snowflake offers a variety of date & time functions to work with date and time data. Date and time functions are essential for any data analysis, as they allow you to manipulate, compare, and extract information from date and time values. Whether you want to calculate the difference between two dates, convert between time zones, or add or subtract dates, Snowflake has a function for that. One of the most useful and versatile date and time functions is Snowflake DATEADD(). As its name indicates, Snowflake DATEADD() allows users to dynamically add or subtract intervals of time to and from dates, timestamps, and times.

In this article, we will cover everything you need to know about the Snowflake DATEADD function and how to use it. We will cover its syntax and parameters for invocation, its in-depth examples of adding and subtracting intervals from dates, common use cases, and considerations of using Snowflake DATEADD().

What is Snowflake DATEADD() Function?

Snowflake DATEADD function is a function that adds a specified number of date or time units to a date or time expression. It returns a new date or time value that is the result of the addition. Snowflake DATEADD function can be used for various purposes, such as:

• Calculating the date or time after or before a certain period of time. For example, you can use Snowflake DATEADD to find the date that is 10 days after today, or the time that is 2 hours before now.
• Comparing the date or time values between different rows or columns.
• Creating date or time intervals or ranges. For example, you can use Snowflake DATEADD to generate a series of dates or times with a fixed interval or to filter the data based on a date or time range.
• Snowflake DATEADD function can also subtract date or time units from a date or time expression, by using a negative number as the argument. For example, you can use Snowflake DATEADD to find the date that is 10 days before today, or the time that is 2 hours after now, by using -10 and -2 as the arguments respectively.

How does Snowflake DATEADD function Work?

Snowflake DATEADD function is a simple yet powerful function that allows you to add or subtract a certain number of date or time units to a date, time, or timestamp value. Now take a closer look at the syntax and the parameters of the Snowflake DATEADD function and how they affect the output.

The syntax of the Snowflake DATEADD function is as follows:

DATEADD(date_or_time_part, number, date_or_time_expr)

Snowflake DATEADD function takes three arguments: date_or_time_part, number, and date_or_time_expr. Let’s see what each of them means and how they work.

• <date_or_time_part>: This argument specifies the unit of the addition or subtraction, such as year, month, day, hour, minute, second, etc. You can use different abbreviations or variations of the date and time parts, such as y, yr, year, years, etc. The date and time parts are case-insensitive, so you can use upper or lower case letters. You can also use singular or plural forms, such as day or days, hour or hours, etc. The date and time parts must be one of the supported values listed in Supported Date and Time Parts.

Note: If you use an invalid or unsupported date or time part, the function will return an error.

• <number>: This argument specifies the number of days or time units to be added or subtracted. It can be a positive or negative integer. If it is a positive number, the function will add the number of units to the date or time expression. If it is a negative number, the function will subtract the number of units from the date or time expression.
• <date_or_time_expr>: This argument specifies the date, time, or timestamp value that is the base of the addition or subtraction. It can be a literal value, a column name, or an expression that returns a date, time, or timestamp value. The data type of the date or time expression determines the data type of the return value.

Some examples of how these Snowflake DATEADD functions parameters come together:

Adding 7 days

SELECT DATEADD(DAY, 7, '2023-01-01');

Subtracting 1 month from the current date

SELECT DATEADD(MONTH, -1, CURRENT_DATE)

Now that you have a basic understanding of the Snowflake DATEADD function’s syntax and how it works, let’s now see some in-depth, real-world practical examples.

Practical Examples and Use Cases of Snowflake DATEADD function

Example 1 — How to Find the Date (or Time) After or Before a Given period of time

One of the most common use cases of the DATEADD function is to find the date or time after or before a given period of time. For example, you might want to find the date that is 10 days after today, or the time that is 2 hours before now. You can use the DATEADD function to easily achieve this by using the appropriate date or time part and number as the arguments.

For example, suppose you want to find a date that is 10 days after today. You can use the following query:

SELECT CURRENT_DATE AS today, DATEADD(day, 10, CURRENT_DATE) AS ten_days_later;

The output will be something like this:

As you can see, in this query, we use the CURRENT_DATE function to get the current date, and then use Snowflake DATEADD function to add 10 days to it. We use the day as the date or time part, and 10 as the value. The result is a new date value that is 10 days later than today.

Example 2 — How do you subtract 7 days from a date in Snowflake?

Another use case of Snowflake DATEADD function is to subtract dates using a negative number as the argument. For example, you might want to find the date that is 7 days before today, or the date that is one month before a given date. You can use Snowflake DATEADD function to easily achieve this by using the appropriate date or time part and a negative number as the arguments.

For example, suppose you want to find the date 7 days before today. You can use the following query:

SELECT CURRENT_DATE AS today, DATEADD(day, -7, CURRENT_DATE) AS seven_days_ago;

The output will be something like this:

Example 3 — How to Create Date or Time Intervals or Ranges

Another common use case of Snowflake DATEADD function is to create date or time intervals or ranges. For example, you might want to generate a series of dates or times with a fixed interval, or to filter the data based on a date or time range. You can make use of the Snowflake DATEADD function to easily achieve this by using the appropriate date or time part and number as the arguments, and then using the GENERATOR function or the BETWEEN operator to create the intervals or ranges.

For example, suppose you want to generate a series of dates with a weekly interval, starting from 2023-11-01 and ending at 2023-12-31. You can use the following query:

SELECT DATEADD(week, seq4(), '2023-11-01'::date) AS week_date
  FROM TABLE(GENERATOR(ROWCOUNT => 10))
  WHERE week_date <= '2023-12-31';

As you can see, in this query, we use the Snowflake CURRENT_DATE function to get the current date, and then use Snowflake DATEADD function to subtract 10 days from it. We then use the day as the date or time part, and -10 as the number. The result is a new date value that is 10 days earlier than today.

Example 4 — How to Subtract Hours from a Timestamp down to the millisecond precision

Now let’s try subtracting 12 hours from a timestamp containing a specific date + time down to the millisecond precision:

SELECT DATEADD(HOUR, -12, '2023-03-15 08:45:32.123'::TIMESTAMP);

As you can see, Snowflake DATEADD enabled shifting the timestamp back 12 hours fairly easily.

When to Use Snowflake DATEADD () Function ?

Beyond direct date arithmetic, there are also a number of various different practical use cases leveraging Snowflake DATEADD capabilities. Snowflake DATEADD () truly shines when it comes to enabling all sorts of helpful time-based calculations. Whether you need to analyze trends across sliding windows, build in future expiration logic, or Partition data across quarters — Snowflake DATEADD has got you covered! Let’s explore some common use cases it allows wielding the mighty power of date manipulation:

1) Calculating Historical Trends

Understanding patterns from what happened 6 months or a year ago can be super-helpful for planning ahead. With the help of Snowflake DATEADD(), getting those historical snapshots is a cakewalk by simply subtracting whatever time period you need from your timestamps. No complex coding required!!

2) Modeling the Future with Expiration Dates

Got a subscription service, free trials, or access credentials you need to expire? Adding expiration logic based on sign-up dates is simple as possible by using Snowflake DATEADD () to advance sign-up timestamps by the required validity period. No remembering to manually update statuses needed !!

3) Slicing and Dicing with Time Partitions

Segmentation is a analyst’s best friend for better data insight. With the help of Snowflake DATEADD(), dividing date ranges into quarters, weeks—days to get time-based partitions ready for evaluation is breeze. No extra math needed to figure ranges !!

There are also tons more possibilities like calculating ages, analyzing daily patterns thanks to time windows, or even predicting future sequences with prospective timestamps. Let your imagination run wild with Snowflake DATEADD ()!!

Considerations and Limitations of Snowflake DATEADD() Function

While DATEADD() provides versatile functionality to manipulate dates, there are some semantic considerations to factor when using it:

1) Timezones

Date adjustments are calculated based on the executing user's timezone session settings rather than any explicit timezone set for referenced dates. This can potentially cause inconsistencies if sessions span different zones.

2) Daylight Savings

Similar to timezone quirks, intervals that cross Daylight Saving Time transitions are subject to inconsistencies unless handled carefully.

3) Date Overflow

Adding extremely large intervals can theoretically cause integer overflow leading to unexpected behaviors if bounds checks aren’t applied by users.

4) Historical Accuracy

For historical analytics and trend data, shifting dates can possibly degrade accuracy of seasonality and temporal models.

5) Reversibility Difficulties

Unlike updates to regular attributes, Snowflake DATEADD adjustments on existing records cannot be easily reverted since prior dates are lost.

Limitations of Snowflake DATEADD()

In addition to the general considerations above, there are some limitations to note with Snowflake DATEADD(), such as:

1) Timezone Inconsistencies Across User Sessions

One of the limitations of Snowflake DATEADD function is that it can cause timezone inconsistencies across different user sessions. This is because Snowflake DATEADD function preserves the time zone information of the date or time expression, if any. For example, if the date or time expression is a timestamp with time zone value, Snowflake DATEADD function will return a timestamp with time zone value. BUT, the time zone of the returned value may not match the time zone of the user session, depending on the session parameters and the source of the data.

For example, suppose you have a table called events that contains the event name and the event time in UTC, as shown below:

You want to convert the event time from UTC to PST, which is 8 hours behind UTC. You can use the following query:

SELECT event_name, event_time_utc, 
  DATEADD(hour, -8, event_time_utc) AT TIME ZONE 'America/Los_Angeles' AS event_time_pst
  FROM events;

As you can see, we use the Snowflake DATEADD function to subtract 8 hours from the event time in UTC. We use the hour as the date or time part, and -8 as the number. We then use the AT TIME ZONE clause to specify the target time zone name, which is America/Los_Angeles. The result is a new timestamp value that is 8 hours earlier than the event time in UTC and has the PST time zone information.

However, if you run this query in a different user session that has a different time zone setting, such as UTC, you will get a different output. So to avoid this problem, you should always use the CONVERT_TIMEZONE function instead of the DATEADD function for time zone conversion.

2) Daylight Saving Time Misalignments Can Cause Errors

Another limitation of Snowflake DATEADD function is that it can cause errors when dealing with daylight saving time changes. Daylight saving time is a practice of adjusting the clocks forward or backward by one hour during certain periods of the year, to make better use of the natural daylight. But, different regions may have different rules and dates for applying the daylight saving time changes, which can cause misalignments and inconsistencies in the date and time data.

3) No Custom Calendar Support

Out of the box, Snowflake DATEADD function does not support custom or complex calendars, such as lunar calendars, fiscal calendars, or academic calendars. The Snowflake DATEADD function assumes that the date or time expression follows the Gregorian calendar, which is the most widely used civil calendar in the world. However, some regions or users may use different calendars that have different rules and dates for the year, month, day, and other date or time parts. The Snowflake DATEADD function may not work correctly or consistently with these calendars, and may produce incorrect or unexpected results.

3) Impacts on Snowflake Query Performance

Snowflake DATEADD function can also have some impacts on the query performance, depending on how it is used and what kind of data it is applied to. Some of the factors that can affect the query performance are:

• The size and complexity of the date or time expression. The larger and more complex the date or time expression is, the more time and resources it may take for Snowflake DATEADD function to process it.
• The number and frequency of the DATEADD function calls. The more times Snowflake DATEADD function is called in a query, the more time and resources it may take for the query to run.
• The data distribution and partitioning of the input tables. The data distribution and partitioning of the input tables can affect how the query is executed and how Snowflake DATEADD function is applied.

To optimize the query performance when using Snowflake DATEADD function, you should consider the following best practices:

• Use Snowflake DATEADD function only when necessary, and avoid using it for simple date or time calculations that can be done with arithmetic operators or other functions.
• Avoid using Snowflake DATEADD function on the columns that are used for filtering, joining, grouping, or ordering, unless the function is applied to both sides of the condition.
• Use the Snowflake CONVERT_TIMEZONE function instead of Snowflake DATEADD function for time zone conversion, as it handles the time zone information and the daylight saving time changes automatically, and does not depend on the user session time zone.
• Cluster the input tables by the date or time column, or use the search optimization service, to improve the data distribution and partitioning, and to enable the query to leverage the clustering information and the pruning techniques.

Conclusion

And that's a wrap! Manipulating dates can feel like time-travel magic! Snowflake's DATEADD() helps you achieve those powers. With it, you gain powerful temporal abilities to manipulate and take control of dates in Snowflake.

In this article, we covered:

• What Snowflake DATEADD() is and its core capabilities
• How Snowflake DATEADD() syntax works to add and subtract date/time units
• Practical Examples and Use Cases of Snowflake DATEADD function
• Common use case possibilities enabled by Snowflake DATEADD()
• Considerations and Limitations of Snowflake DATEADD() Function

Imagine Snowflake DATEADD() as a control panel for a time machine. You can pick where you want to go in time by adjusting the settings. Want to check what happened last month? Just turn the dial to go back 30 days. Planning for the future? Set it to move ahead by specific periods. There are lots of choices, so you can get really creative with how you play with dates!!

FAQs

How to insert a date in Snowflake?

Dates can be inserted into Snowflake tables using the DATE data type and ISO formatting like 'YYYY-MM-DD', e.g:

INSERT INTO table (date_col) VALUES ('2023-01-07');

What is the syntax of Snowflake DATEADD()?

Syntax of Snowflake DATEADD function is: DATEADD(date_part, value, date_expression)

What date parts can be used in Snowflake DATEADD()?

Supported date parts include YEAR, MONTH, DAY, HOUR, MINUTE, SECOND etc.

Can Snowflake DATEADD() be used with TIMESTAMP values?

Yes, Snowflake DATEADD works with DATE, TIME, and TIMESTAMP data types.

Does Snowflake DATEADD() modify dates in-place?

No, Snowflake DATEADD returns a new date rather than updating existing dates.

How are negative numbers handled in Snowflake DATEADD()?

Negative numbers cause date/time units to be subtracted instead of added.

What if invalid or overflow dates are produced?

Errors are returned if Snowflake DATEADD results violate date validity boundaries.

Can intervals be added/subtracted across year boundaries?

Yes, Snowflake DATEADD handles year transitions properly based on interval units.

Does Snowflake DATEADD consider Daylight Savings Time impacts?

No, daylight savings impacts need explicit handling.

Are leap years handled correctly by Snowflake DATEADD()?

Yes, February 29th is adjusted correctly in leap years.

What is the difference between Snowflake DATEADD and DATEDIFF in Snowflake?

DATEADD adds/subtracts intervals from dates, DATEDIFF calculates date differences.

How to subtract dates using Snowflake DATEADD?

To subtract from dates, provide a negative number as the interval amount in DATEADD.

For example, to subtract 5 days:

SELECT DATEADD(DAY, -5, '2023-03-07');

This returns:

2023-03-02 00:00:00.000

Can Snowflake DATEADD be used to produce a sliding calculation window?

Yes, by applying the same interval offsets across multiple records.  

How does Snowflake DATEADD impact query performance?

Can increase processing and affect optimizations if used extensively.

What are alternatives to using Snowflake DATEADD() for date math?

Options like direct +/- interval arithmetic often work too.

Can fiscal calendar adjustments be made using Snowflake DATEADD()?

No, custom calendars require more complex logic.  

Do timezones affect calculations made by Snowflake DATEADD()?

Timezones can lead to inconsistent interval offsets.

Is historical trend accuracy impacted by shifting dates with Snowflake DATEADD()?

Yes, statistical precision may degrade for predictive models.

Can Snowflake DATEADD be used for data warehousing date dimension builds?

Yes, very applicable for incrementing standard date rows.