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.
Want to know your Snowflake costs? Check out our Pricing Calculator today!
How does a Snowflake date_part function work?
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:
- <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.
- <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.
Migrating your data to Snowflake doesn’t have to be complex. Relax and go for a seamless migration using Hevo’s no-code platform. With Hevo, you can:
- Effortlessly extract data from 150+ connectors.
- Tailor your data to Snowflake’s needs with features like drag-and-drop and custom Python scripts.
- Achieve lightning-fast data loading into Snowflake, making your data analysis ready.
Try to see why customers like Slice and Harmoney have upgraded to a powerful data and analytics stack by incorporating Hevo!
Get Started with Hevo for Free
Returns
A Snowflake DATE_PART
function always returns a NUMBER type value.
Examples of Snowflake DATE_PART Function
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 |
+-------------------------+-----------------------------+
Integrate Azure Blob Storage to Snowflake
Integrate Amazon DocumentDB to Snowflake
Integrate Amazon S3 to Snowflake
Use Cases of Snowflake DATE_PART
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.
Migrate Data seamlessly Within Minutes!
No credit card required
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.
Learn More About:
Utilize DATE_TRUNC Snowflake 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. Sign up for Hevo’s 14-day free trial and learn how Hevo Data, a Snowflake Partner, streamlines data transfer.
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.
2. What is the function of Date_sub in Snowflake?
This function subtracts a specified number of days from a given date. It’s equivalent to DATEADD
with a negative value for the interval.
3. What is the date datatype in Snowflake?
The DATE
datatype stores date values without a time component. It supports values from 0001-01-01
to 9999-12-31
.
-
Radhika is a Content Specialist at Hevo Data with over 5 years of experience. She excels at creating high-quality content related to data engineering, especially data integration, and data analytics. Her expertise lies in translating complex technical concepts into accessible and engaging content for diverse audiences.