Snowflake Extract Function Usage and Examples

The extract function in Snowflake extracts specified date or time part from the date, time, or timestamp fields. This function retrieves the sub part such as day, month, year,etc. The extract function is equivalent to date_part() function. In this article, we will check how to use extract and date_part function to get specified sub part.

Snowflake Extract Function

The extract function is very useful when you are working with various data sources. Sometimes, you may get requirement to extract part of timestamp field such as date and pass it to calling application.

For example, extract time part of the current datetime and append it to file name to create unique file name. Similarly, there are so many applications use extract or date_part function.

Snowflake Extract and Date_part Functions Syntax

Following is the extract function syntax.

EXTRACT( <date_or_time_part> FROM <date_or_time_expr> )

Similarly, below is the date_part function syntax.

DATE_PART( <date_or_time_part> , <date_or_time_expr> )

Snowflake Extract and Date_part Functions Usage

Below table represents the descriptions of the different datepart or timepart used in extract and date_part function. These values are sometimes referred to as a units.

Datepart or Timepart	Descriptions
HOUR	Hours from the time or timestamp field.
MINUTE	Minutes from the time or timestamp field.
SECOND	Seconds from the time or timestamp field.
YEAR	Year
YEAROFWEEK	Year that the extracted week belongs to
YEAROFWEEKISO	Same as YEAROFWEEK, except uses ISO semantics
DAY / DAYOFMONTH	Day of the month
DAYOFWEEK	Day of the week
DAYOFWEEKISO	Day of the week uses ISO semantics
DAYOFYEAR	Day of the year
WEEK / WEEKOFYEAR	Week of the year
WEEKISO	Same as WEEK, except uses ISO semantics
MONTH	Month of the year
QUARTER	Quarter of the year
epoch	epoch

Snowflake Date Extract Examples

Extract epoch from the current_timestamp in Snowflake.

select extract(epoch from current_timestamp) as epoch;

+------------+
|      EPOCH |
|------------|
| 1578328049 |
+------------+

Get quarter from the current_timestamp in Snowflake.

select extract(QUARTER from current_timestamp) as QUARTER;
+---------+
| QUARTER |
|---------|
|       1 |
+---------+

Extract hour from the current_timestamp in Snowflake.

>select extract(hour from current_timestamp) as hour;
+------+
| HOUR |
|------|
|    8 |
+------+

Get minutes from the current_timestamp in Snowflake.

select extract(MINUTE from current_timestamp) as MINUTE;
+--------+
| MINUTE |
|--------|
|     33 |
+--------+

Get day from the current_timestamp in Snowflake.

select extract(day from current_timestamp) as day;
+-----+
| DAY |
|-----|
|   6 |
+-----+

Extract month from the current_timestamp in Snowflake.

select extract(MONTH from current_timestamp) as MONTH;
+-------+
| MONTH |
|-------|
|     1 |
+-------+

Extract year from the current_timestamp in Snowflake.

select extract(YEAR from current_timestamp) as YEAR;
+------+
| YEAR |
|------|
| 2020 |
+------+

Get week of the year from the current_timestamp in Snowflake.

select extract(WEEK from current_timestamp) as WEEK;
+------+
| WEEK |
|------|
|    2 |
+------+

Get day of the week from the current_timestamp in Snowflake.

select extract(DAYOFWEEK from current_timestamp) as DAYOFWEEK;
+-----------+
| DAYOFWEEK |
|-----------|
|         1 |
+-----------+