Mastering DATE_PART Snowflake for Powerful Date Operations

Working with dates and times is a common task in data analysis and reporting scenarios. Whether you’re analyzing time-series data, generating reports based on specific date ranges, or performing complex date calculations, having the right tools and functions at your disposal is crucial.

The Snowflake DATE_PART function is an incredibly powerful tool for working with dates and times. In this blog, we will delve into the intricacies of the DATE_PART function, exploring what it is and how it works. We will also discuss the use cases and examples to help you understand its functionality. 

What is DATE_PART Snowflake?

The DATE_PART function in Snowflake is a versatile tool that allows you to extract a specific date or time part from a date, time, or timestamp expression. It’s particularly useful in data analytics and reporting, where precise date manipulations and extractions are often required.

How does a Snowflake date_part function work? (to include the syntax, arguments and returns)

Syntax

The syntax for the function is as follows:

DATE_PART( <date_or_time_part> , <date_or_timetamp_expr> )

DATE_PART( <date_or_time_part> FROM <date_or_time_expr> )

Parameters

The DATE_PART function in Snowflake takes two arguments:

1. <date_or_time_part> 

• This argument specifies the unit or component of the date or timestamp that you want to extract.
• It must be one of the supported date and time part values such as ‘year’, ‘month’, ‘day’, ‘hour’, ‘minute’, ‘second’, etc.
• The value can be provided as a string literal enclosed in single quotes (e.g., ‘month’) or as an unquoted identifier (e.g., month).
• For certain part specifications like ‘week’, ‘dayofweek’, or ‘yearofweek’, the output is influenced by the WEEK_START session parameter, which determines the start day of the week.
• Additionally, when using ‘dayofweek’ or ‘yearofweek’, the output is also controlled by the WEEK_OF_YEAR_POLICY session parameter, which defines how weeks are numbered within a year.

2. <date_or_timetamp_expr>

• This argument is the actual date, time, timestamp, or an expression that can be evaluated to a date, time, or timestamp value.

Returns

A Snowflake DATE_PART function always returns a NUMBER type value.

Examples

Let us look at some examples to understand the functionality of the Snowflake date part.

Example 1: Extracting Part of a Date

SELECT DATE_PART(quarter, '2024-05-08'::DATE);

Output:

+----------------------------------------+

| DATE_PART(QUARTER, '2024-05-08'::DATE) |

|----------------------------------------|

|                                      2 |

+----------------------------------------+

Example 2: Extracting Part of a Timestamp

SELECT TO_TIMESTAMP(

  '2024-05-08T22:49:20.123-07:00') AS "TIME_STAMP1",

  DATE_PART(year, "TIME_STAMP1") AS "EXTRACTED YEAR";

Output:

+-------------------------+----------------+

| TIME_STAMP1             | EXTRACTED YEAR |

|-------------------------+----------------|

| 2024-05-08 22:49:20.123 |           2024 |

+-------------------------+----------------+

Example 3: Converting a Timestamp to the Number of Seconds

SELECT TO_TIMESTAMP(

 '2024-05-08T22:49:20.123-07:00') AS "TIME_STAMP1",

  DATE_PART(epoch_second, "TIME_STAMP1") AS "EXTRACTED EPOCH SECOND";

Output:

+-------------------------+------------------------+

| TIME_STAMP1             | EXTRACTED EPOCH SECOND |

|-------------------------+------------------------|

| 2024-05-08 22:49:20.123 |             1712619560 |

+-------------------------+------------------------+

Example 4: Converting a Timestamp to the Number of Milliseconds

SELECT TO_TIMESTAMP(

  '2024-04-08T23:39:20.123-07:00') AS "TIME_STAMP1",

  DATE_PART(epoch_millisecond, "TIME_STAMP1") AS "EXTRACTED EPOCH MILLISECOND";

Output:

+-------------------------+-----------------------------+

| TIME_STAMP1             | EXTRACTED EPOCH MILLISECOND |

|-------------------------+-----------------------------|

| 2024-04-08 23:39:20.123 |               1712619560123 |

+-------------------------+-----------------------------+

Use Cases of date_part Snowflake

Use Case 1: How to Filter Data based on specific Date components?

Suppose you have table orders with a column order_date containing timestamps. You can filter the data based on a specific year or month using DATE_PART:

-- Get orders from the year 2022

SELECT *

FROM orders

WHERE DATE_PART(year, order_date) = 2022;

-- Get orders from March 2022

SELECT *

FROM orders

WHERE DATE_PART(year, order_date) = 2022

  AND DATE_PART(month, order_date) = 3;

Use Case 2: How to group and aggregate Data by Date Components?

You can group and aggregate data by year, month, or any other date component to analyze trends or patterns:

-- Get total sales by month and year

SELECT DATE_PART(year, order_date) AS order_year,

       DATE_PART(month, order_date) AS order_month,

       SUM(total_amount) AS total_sales

FROM orders

GROUP BY DATE_PART(year, order_date), DATE_PART(month, order_date)

ORDER BY order_year, order_month;

Use Case 3: How to perform data calculations and manipulations with DATE_PART?

DATE_PART can be used in conjunction with other date functions to perform calculations and manipulations:

-- Calculate the number of days between two dates

SELECT DATEDIFF(day, '2023-01-01', '2023-05-15') AS days_diff;

-- Calculate the age in years from a birth date

SELECT DATE_PART(year, CURRENT_DATE()) - DATE_PART(year, birth_date) AS age

FROM customers;

Use Case 4: How to generate date ranges & Intervals using DATE_PART?

You can use DATE_PART to extract all the dates in a particular month or particular days in a month:

-- Get all dates in the month of March 2023

SELECT DATE_FROM_PARTS(2023, 3, day_number) AS march_dates

FROM TABLE(FLATTEN(SPLIT_RANGE(1, 32)));

-- Get all Mondays in 2023

SELECT DATE_FROM_PARTS(2023, month_num, day_num) AS mondays_2023

FROM TABLE(FLATTEN(SPLIT_RANGE(1, 13), year_num => 2023, month_num => seq8(), day_num => seq8()))

WHERE DATE_PART(dayofweek, DATE_FROM_PARTS(2023, month_num, day_num)) = 2;

Supported Date and Time Parts in the DATE_PART Snowflake function

The Snowflake date part function can accept the following date or time part(case sensitive) in their arguments:

Date Parts	Abbreviations/ Variations	Date Parts	Abbreviations/ Variations
year	y , yy , yyy , yyyy , yr , years , yrs	hour	h , hh , hr , hours , hrs
month	mm , mon , mons , months	minute	m , mi , min , minutes , mins
day	d , dd , days, dayofmonth	second	s , sec , seconds , secs
dayofweek	weekday , dow , dw	nanosecond	ns , nsec , nanosec , nsecond , nanoseconds , nanosecs , nseconds
dayofweekiso	weekday_iso , dow_iso , dw_iso	epoch_second	epoch , epoch_seconds
dayofyear	yearday , doy , dy	epoch_millisecond	epoch_milliseconds
week	w , wk , weekofyear , woy , wy	epoch_micrpsecond	epoch_microseconds
weekiso	week_iso , weekofyeariso , weekofyear_iso	epoch_nanosecond	epoch_nanoseconds
quarter	q , qtr , qtrs , quarters	timezone_hour	tzh
yearofweek		timezone_minute	tzm
yearofweekiso

Benefits of DATE_PART Snowflake function 

• Versatility: The Snowflake date_part function is versatile and supports a wide range of date and time parts(as seen in the table above), simplifying data analysis and time bases calculations.
• ISO Week functionality: date_part Snowflake function follows the ISO week semantics, ensuring consistent behaviour across different date and time parts.
• Integration with Other Functions: The DATE_PART function can be combined with other Snowflake functions, such as DATEDIFF, DATE_TRUNC, or DATE_FORMAT, to perform more complex date and time calculations or transformations.

Limitations of DATE_PART Snowflake functions 

• Limited support for Date/Time parts: The date_part function in Snowflake only supports pre-defined data and time parts. You cannot create a custom unit or create your own calculation within the function.
• Limited Functionality: The date_part Snowflake function cannot perform complex functions such as adding or subtracting dates, calculating differences between dates, or formatting time/date. 
• Missing Timezone Awareness: date_part doesn’t inherently handle time zones. If your data includes timestamps with time zones, you might need to perform additional processing to ensure consistent results based on the desired time zone.

Simplify Time-based analysis and ELT in Snowflake using Hevo

Efficient time-based analysis becomes streamlined when your organization’s data is consolidated into Snowflake. Snowflake’s DATE_PART function provides robust support for date and time-related calculations and analysis. To cost-effectively automate the data migration process to Snowflake, consider leveraging Hevo Data, a real-time, no-code ELT data pipeline platform with 150+ connectors. Hevo Data offers seamless integration with Snowflake and the Snowpark API, facilitating an efficient data consolidation process. Additionally, Hevo Data’s key features enhance the utilization of Snowflake’s DATE_PART function:

• Data Transformation: Hevo Data offers user-friendly, drag-and-drop transformations, including Date and Control functions. These transformations enable the removal of inconsistencies in date or time formats before loading data into Snowflake. This ensures the smooth execution of DATE_PART functions in your date/time-related calculations.
• Incremental Data Load: Hevo Data facilitates incremental data transfer, allowing you to apply the DATE_PART function on data as soon as it arrives in Snowflake. This capability ensures that your date-based analytics and reports are always up-to-date, leveraging the latest available data for accurate time-based calculations using the DATE_PART function.

Conclusion 

This is all about the DATE_PART Snowflake function. With this Snowflake function, you can extract particular dates, times and timestamps, calculate the last day of a month, add days to a specific date, extract the Mondays of a year, calculate the gap between two dates and multiple other things. 

These calculations are an integral part of the data analysis workflow and are important for time-series analysis, data aggregation, and any scenarios where you need to work with particular times and dates. 

While date and time manipulation in Snowflake becomes easy with DATE_PART, scattered data can become a hindrance here. A no-code ELT platform like Hevo Data can consolidate all your data from your sources into Snowflake in a few easy steps and allow you to perform easy date and time analysis.

FAQs 

1. What is the alternative to DATE_PART date function in a COPY INTO TABLE command?

In Snowflake, the COPY INTO TABLE command does not natively support functions like DATE_PART directly within the command syntax. However, you can achieve similar results by using a staging table or a transformation step before performing the COPY INTO TABLE command.